Search code examples
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