SQL CASE WHEN- can I do a function within a function? New to SQL

Question

SELECT
        SP.SITE,
        SYS.COMPANY,
        SYS.ADDRESS,
        SP.CUSTOMER,
        SP.STATUS,
        DATEDIFF(MONTH,SP.MEMBERSINCE, SP.EXPIRES) AS MONTH_COUNT
CASE WHEN(MONTH_COUNT = 0 THEN MONTH_COUNT = DATEDIFF(DAY,SP.MEMBERSINCE, SP.EXPIRES) AS DAY_COUNT)
ELSE NULL
END
    FROM SALEPASSES AS SP
    INNER JOIN SYSTEM AS SYS ON SYS.SITE = SP.SITE
    WHERE STATUS IN (7,27,29);

I am still trying to understand SQL. Is this the right order to have everything? I'm assuming my datediff() is unable to work because it's inside case when. What I am trying to do, is get the day count if month_count is less than 1 (meaning it's less than one month and we need to count the days between the dates instead). I need month_count to run first to see if doing the day_count would even be necessary. Please give me feedback, I'm new and trying to learn!

Answer 1

Case is an expression, it returns a value, it looks like you should be doing this:

DAY_COUNT =
 CASE WHEN DATEDIFF(MONTH,SP.MEMBERSINCE, SP.EXPIRES) = 0 
 THEN DATEDIFF(DAY,SP.MEMBERSINCE, SP.EXPIRES)) 
 ELSE NULL END

You shouldn't actually need else null as NULL is the default.

Note also you [usually] cannot refer to a derived column in the same select

Answer 2

It appears that what you are trying to do is define the MonthCount column's value, and then reuse that value in another column's definition. (The Don't Repeat Yourself principle.)

In most dialects of SQL, you can't do that. Including MS SQL Server.

That's because SQL is a "declarative" language. This means that SQL Server is free to calculate the column values in any order that it likes. In turn, that means you're not allowed to do anything that would rely on one column being calculated before another.

There are two basic ways around that...

First, use CTEs or sub-queries to create two different "scopes", allowing you to define MonthCount before DayCount, and so reuse the value without retyping the definition.

SELECT
  *,
  CASE WHEN MonthCount = 0 THEN foo ELSE NULL END AS DayCount
FROM
(
  SELECT
    *,
    bar   AS MonthCount
  FROM
    x
)
  AS derive_month

The second main way is to somehow derive the value Before the SELECT block is evaluated. In this case, using APPLY to 'join' a single value on to each input row...

SELECT
  x.*,
  MonthCount,
  CASE WHEN MonthCount = 0 THEN foo ELSE NULL END AS DayCount
FROM
  x
CROSS APPLY
(
  SELECT
    bar   AS MonthCount
)
  AS derive_month