Reputation: 189
I have a table
DATE Val
01-01-2020 1
01-02-2020 3
01-05-2020 2
01-07-2020 8
01-13-2020 3
...
I want to summarize these values by the following Sunday. For example, in the above example:
1-05-2020, 1-12-2020, and 1-19-2020 are Sundays, so I want to summarize these by those dates.
The final result should be something like
DATE SUM
1-05-2020 6 //(01-01-2020 + 01-02-2020 + 01-05-2020)
1-12-2020 8
1-19-2020 3
I wasn't certain if the best place to start would be to create a temp calendar table, and then try to join backwards based on that? Or if there was an easier way involving DATEDIFF. Any help would be appreciated! Thanks!
Upvotes: 0
Views: 74
Reputation: 29677
Here's a solution that uses DATEADD & DATEPART to calculate the closest Sunday.
With a correction for a different setting of @@datefirst
.
(Since the datepart weekday values are different depending on the DATEFIRST setting)
Sample data:
create table #TestTable ( Id int identity(1,1) primary key, [Date] date, Val int ); insert into #TestTable ([Date], Val) VALUES ('2020-01-01', 1) , ('2020-01-02', 3) , ('2020-01-05', 2) , ('2020-01-07', 8) , ('2020-01-13', 3) ;
Query:
WITH CTE_DATA AS ( SELECT [Date], Val , DATEADD(day, ((7-(@@datefirst+datepart(weekday, [Date])-1)%7)%7), [Date]) AS Sunday FROM #TestTable ) SELECT Sunday AS [Date], SUM(Val) AS [Sum] FROM CTE_DATA GROUP BY Sunday ORDER BY Sunday;
Date | Sum :--------- | --: 2020-01-05 | 6 2020-01-12 | 8 2020-01-19 | 3
db<>fiddle here
Extra:
Apparently the trick of adding the difference of weeks from day 0 to day 6 also works independently from the DATEFIRST setting.
So this query will return the same result for the sample data.
WITH CTE_DATA AS
(
SELECT [Date], Val
, CAST(DATEADD(week, DATEDIFF(week, 0, DATEADD(day, -1, [Date])), 6) AS DATE) AS Sunday
FROM #TestTable
)
SELECT
Sunday AS [Date],
SUM(Val) AS [Sum]
FROM CTE_DATA
GROUP BY Sunday
ORDER BY Sunday;
The subtraction of 1 day makes sure that if the date is already a Sunday that it isn't calculated to the next Sunday.
Upvotes: 2
Reputation: 1076
Here is a simple solution. Putting your values into a temporary table and viewing the results on that table:
DECLARE @dates TABLE
(
mDATE DATE,
Val INT,
Sunday DATE
)
INSERT INTO @dates (mDATE,Val) VALUES
('01-01-2020',1),('01-02-2020',3),('01-05-2020',2),('01-07-2020',8),('01-13-2020',3)
UPDATE @dates
SET Sunday = dateadd(week, datediff(week, 0, mDATE), 6)
SELECT Sunday,SUM(Val) AS Val FROM @dates
GROUP BY Sunday
OUTPUT:
Sunday Val
2020-01-05 4
2020-01-12 10
2020-01-19 3
Upvotes: 0
Reputation: 4061
Here is a way to do it: nb:1-13-2020 wont show cuz its not a sunday
with cte as
(
select cast('01-01-2020'as Date) as Date, 1 as Val
union select '01-02-2020' , 3
union select '01-05-2020' , 2
union select '01-07-2020' , 8
)
select Date, max(dateadd(dd,number,Date)), sum(distinct Val) as SUM
from master..spt_values a inner join cte on Date <= dateadd(dd,number,Date)
where type = 'p'
and year(dateadd(dd,number,Date))=year(Date)
and DATEPART(dw,dateadd(dd,number,Date)) = 7
group by Date
Output:
Date (No column name) SUM
2020-01-01 2020-12-26 1
2020-01-02 2020-12-26 3
2020-01-05 2020-12-26 2
2020-01-07 2020-12-26 8
Upvotes: 0