CODEWITHSUNDEEP
sqlsql-servert-sql
remh
remh

Reputation: 189

Grouping sql rows by weeks

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

Answers (3)

LukStorms
LukStorms

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

Icculus018
Icculus018

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

zip
zip

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

Related Questions