There are a lot questions on CASE WHEN topic, but the closest my question is related to this How to use CASE WHEN condition with MAX() function query which has not been resolved.

Here is some of my sample data:

So, I want to all records for the last two dates and three additional columns: sum(sales) for the previous day, sum for the current day and the difference between them:

SELECT
    [debet],
    [date] , 
    SUM( CASE WHEN [date] = MAX(date)     THEN [debet] ELSE 0 END ) AS sum_act, 
    SUM( CASE WHEN [date] = MAX(date) - 1 THEN [debet] ELSE 0 END ) AS sum_prev , 
    (
        SUM( CASE WHEN [date] = MAX(date)     THEN [debet] ELSE 0 END ) 
        -
        SUM( CASE WHEN [date] = MAX(date) - 1 THEN [debet] ELSE 0 END )  
    ) AS diff 
FROM
    Table 
WHERE
    [date] = ( SELECT MAX(date) FROM Table WHERE date < ( SELECT MAX(date) FROM Table) )
    OR
    [date] = ( SELECT MAX(date) FROM Table WHERE date = ( SELECT MAX(date) FROM Table ) )
GROUP BY
    [date],
    [debet]

Further, of course, it informs that I can't use the aggregate function inside CASE WHEN. Now I use this combination: sum(CASE WHEN [date] = dateadd(dd,-3,cast(getdate() as date)) THEN [debet] ELSE 0 END). But here every time I need to make an adjustment for weekends and holidays. The question is, is there any other way than using 'getdate' in 'case when' Statement to get max date?

Expected result:

CodePudding user response：

You can use dense_rank() to filter the last 2 dates in your table. After that you can use either conditional case expression with sum() to calculate the required value

select [date],
       sum_act  = sum(case when rn = 1 then [debet] else 0 end),
       sum_prev = sum(case when rn = 2 then [debet] else 0 end),
       diff     = sum(case when rn = 1 then [debet] else 0 end)
                - sum(case when rn = 2 then [debet] else 0 end)
from
(
    select *, rn = dense_rank() over (order by [date] desc)
    from   tbl 
) t
where rn <= 2
group by [date]

db<>fiddle demo