How to calculate running total by adding last 7 days of month using SQL Server

Hi vsaklani,

Refer the below sample query.

SQL

DECLARE @Test AS TABLE([Date] DATE,TotalRestOfDay INT)
INSERT INTO @Test VALUES('12-1-2016',10)
INSERT INTO @Test VALUES('12-2-2016',12)
INSERT INTO @Test VALUES('12-3-2016',10)
INSERT INTO @Test VALUES('12-4-2016',11)
INSERT INTO @Test VALUES('12-5-2016',12)
INSERT INTO @Test VALUES('12-6-2016',10)
INSERT INTO @Test VALUES('12-7-2016',11)
INSERT INTO @Test VALUES('12-8-2016',10)
INSERT INTO @Test VALUES('12-9-2016',9)
INSERT INTO @Test VALUES('12-10-2016',10)
INSERT INTO @Test VALUES('12-11-2016',13)

;WITH CTE AS
(SELECT ROW_NUMBER() OVER(ORDER BY ([Date])) ID,[Date],TotalRestOfDay FROM @Test)
SELECT [Date],
       TotalRestOfDay,
       CASE WHEN ID > 6 THEN (SELECT ISNULL(CONVERT(VARCHAR(20),SUM(TotalRestOfDay)),'')
                              FROM CTE T2
                              WHERE T2.ID BETWEEN T1.ID - 6 and T1.ID
                             ) 
       ELSE '' 
       END 'Total7DayAmount'
FROM CTE T1

Output