group data by weeks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

group data by weeks

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-04-22 : 06:11:02

hi, I am trying to group my data into weeks in a month.
I am using :
GROUP BY DateAdd(day, -1 * datepart(dw, convert(char(10),date1,23)),
convert(char(10),date1,23))

Which works ok, but my first day is the monday of each week.
Do you know how I can group by weeks,
where dates run from monday to sunday ?
So IN march I would have

week begin 3/3
week begin 10/3
week begin 17/3
week begin 24/3
week begin 31/3

But for the last day I only want to count the 31st, not the entire week.
Same goes for the beginning of the month,
eg for april, I want it to show as week beginning 31st march but I only count aprils data.

I hope that makes sense.

can you help ?

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-04-22 : 06:22:00

hi,
I think I have worked out a bit of it :

convert(char(11), DATEADD(wk, DATEDIFF(wk, 1, convert(char(10),Date1,23)), 0),106) Will get me the first monday of each week.

do you know how I could pivot the data from :
week begin 3/3
week begin 10/3
week begin 17/3
week begin 24/3
week begin 31/3

to 3 10 17 24

?

bfoster
Starting Member

30 Posts

Posted - 2008-04-22 : 17:19:35

Here's a start. Look into the "Using PIVOT and UNPIVOT" in the books online as a starting place on how to pivot your data. When I ran the example you had in the previous post, I get March 2nd mapped to the week of March 3rd and so on for all Sundays.

DECLARE @T TABLE(Id INT, Qty INT, Date1 DATETIME)

INSERT INTO @T SELECT 1, 1000, '2008-02-29'
INSERT INTO @T SELECT 2, 1, '2008-03-01'
INSERT INTO @T SELECT 3, 2, '2008-03-02'
INSERT INTO @T SELECT 4, 10, '2008-03-03'
INSERT INTO @T SELECT 5, 11, '2008-03-07'
INSERT INTO @T SELECT 6, 50, '2008-03-11'
INSERT INTO @T SELECT 7, 51, '2008-03-16'
INSERT INTO @T SELECT 8, 100, '2008-03-19'
INSERT INTO @T SELECT 9, 101, '2008-03-22'
INSERT INTO @T SELECT 10, 102, '2008-03-23'
INSERT INTO @T SELECT 11, 500, '2008-03-24'
INSERT INTO @T SELECT 12, 501, '2008-03-28'
INSERT INTO @T SELECT 13, 1500, '2008-03-31'
INSERT INTO @T SELECT 14, 10000, '2008-04-01'

SELECT
DATEPART(month, Date1) AS Month1,
CONVERT(CHAR(11), DATEADD(day, (DATEPART(dw, Date1) + 5) % 7 * -1, Date1), 106) AS Date1,
Qty
FROM @T

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 17:36:30

GROUP BY DATEDIFF(WEEK, '19000101', Date1), DATEPART(MONTH, Date1)

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources