Get sum of previous 6 values including the group

Question

I need to sum up the values for the last 7 days,so it should be the current plus the previous 6. This should happen for each row i.e. in each row the column value would be current + previous 6. The case :-

(Note:- I will calculate the hours,by suming up the seconds).

I tried using the below query :-

select SUM([drivingTime]) OVER(PARTITION BY driverid ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
from [f.DriverHseCan]

The problem I face is I have to do grouping on driver,asset for a date

In the above case,the driving time should be sumed up and then,its previous 6 rows should be taken, I cant do this using rank() because I need these rows as well as I have to show it in the report.

I tried doing this in SSRS and SQL both.

In short it is adding total driving time for current+ 6 previous days

Answer 1

Try the following query

SELECT 
        s.date
        , s.driverid
        , s.assetid
        , s.drivingtime
        , SUM(s2.drivingtime) AS total_drivingtime
    FROM f.DriverHseCan s
    JOIN (
        SELECT date,driverid, SUM(drivingtime) drivingtime
        FROM f.DriverHseCan
        GROUP BY date,driverid
        ) AS s2
        ON s.driverid = s2.driverid AND s2.date BETWEEN DATEADD(d,-6,s.date) AND s.date
    GROUP BY 
        s.date
        , s.driverid
        , s.assetid
        , s.drivingtime

If you have week start/end dates, there could be better performing alternatives to solve your problem, e.g. use the week number in SSRS expressions rather than do the self join on SQL server