Oak_3260548 Oak_3260548 - 6 months ago 16
SQL Question

SQL Group By condition

I have following SQL query for start:

SELECT CASE
WHEN CAST(tDate as DATE) < CAST(GETDATE() as date) -- if older then Today
THEN DATEADD(dd,-1,CAST(GETDATE() as date)) -- assgin yesterday's date
ELSE CAST(tDate as date) -- use the date
END as tDate,
SUM(tDuration-ISNULL(tDone,0)) as tToDo FROM tTable

WHERE .....conditions.....

GROUP BY tDate
ORDER BY tDate


I have two problems with that:

I tried to
SUM()
all of the past task's duration to yesterdays date, which I succeed, but
GROUP BY
wouldn't sum them into one date, it leaves them at separate lines. I don't know why, when I cast datetime input to date and they should have equal value.

I tried to use
CONVERT
instead of
CAST
and to separate the
CASE
processing into sub-query with no improvements.

Answer

Replace your query with this:

SELECT CASE 
           WHEN CAST(tDate as DATE) < CAST(GETDATE() as date) -- if older then Today
           THEN DATEADD(dd,-1,CAST(GETDATE() as date))        -- assgin yesterday's date
           ELSE CAST(tDate as date)                           -- use the date
       END as tDate, 
       SUM(tDuration-ISNULL(tDone,0)) as tToDo FROM tTable 

    WHERE .....conditions.....

    GROUP BY CASE 
           WHEN CAST(tDate as DATE) < CAST(GETDATE() as date) -- if older then Today
           THEN DATEADD(dd,-1,CAST(GETDATE() as date))        -- assgin yesterday's date
           ELSE CAST(tDate as date)                           -- use the date
       END   
    ORDER BY tDate