sqlsql-servergroup-byrollup

SQL Incorporate rows while rollup


I have a View in SQL Server Express that shows total values rolled up per Date

Below is how the view is currently built

SELECT        
    COALESCE(MACH, 'Total') AS MACH,
    COALESCE(DATE, '')  AS 'DATE',
    SUM(VAL) AS VAL
FROM
    myTable
GROUP BY 
    ROLLUP (MACH, DATE)
HAVING
    (MACH IS NULL) OR (DATE <> '')

Inside myTable I have these events:

| MACH | DATE       | TIME     | VAL |
| 1    | 02/05/2024 | 17:35:17 | 100 |
| 1    | 02/05/2024 | 19:35:17 | 100 |
| 2    | 02/05/2024 | 21:35:17 | 100 |
| 1    | 03/05/2024 | 04:35:17 | 100 |
| 2    | 03/05/2024 | 05:35:17 | 100 |
| 1    | 03/05/2024 | 07:35:17 | 100 |

and my View is like this:

| MACH  | DATE       | VAL |
| 1     | 02/05/2024 | 200 |
| 1     | 03/05/2024 | 200 |
| 2     | 02/05/2024 | 100 |
| 2     | 03/05/2024 | 100 |
| Total |            | 600 |

What I want to do is incorporate two dates into one when myTable.TIME is before 06:00 AM. The view should then looks like below:

| MACH  | DATE       | VAL |
| 1     | 02/05/2024 | 300 |
| 1     | 03/05/2024 | 100 |
| 2     | 02/05/2024 | 200 |
| Total |            | 600 |

Is it possible? I tried using CASE WHEN inside ROLLUP but had no luck and I can't figure out how to do it


Solution

  • Here I moved the relevant calculation into a subquery, so you don’t have to repeat it three times. As you can see, it’s just “if the time is before 06:00, use the day before”:

    select
        coalesce(MACH, 'Total') AS MACH,
        RollupDate AS 'DATE',
        sum(VAL) AS VAL
    from
        (select *, iif(TIME < '06:00', dateadd(day, -1, DATE), DATE) as RollupDate
        from ##MyTable) X
    group by
        rollup (MACH, RollupDate)
    having
        RollupDate is not null