get week numbers from dates, saturday being start of the week,

Question

get week numbers from dates, saturday being start of the week,

Nomvula

Hall of Fame

Points: 3778
More actions
September 17, 2015 at 2:44 am

#321259

i have the following table I need to select dates grouping them by weeks, my week start is Saturday to Friday
CREATE TABLE weekdays
(
datevalue datetime NOT NULL
, numericvalue INT NOT NULL
);
INSERT INTO weekdays (datevalue, numericvalue) VALUES
('2015-09-01', 1000),
('2015-09-02', 1001),
('2015-09-07', 1003),
('2015-09-08', 1004),
('2015-09-12', 1005),
('2015-09-13', 1006),
('2015-09-14', 1007),
('2015-09-19', 1008);
the output should look like this
weeknototalvalue
362015-09-01 00:00:00.000
362015-09-02 00:00:00.000
372015-09-07 00:00:00.000
372015-09-08 00:00:00.000
382015-09-12 00:00:00.000
382015-09-13 00:00:00.000
382015-09-14 00:00:00.000
392015-09-19 00:00:00.000

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Nomvula (9/17/2015)
i have the following table I need to select dates grouping them by weeks, my week start is Saturday to Friday
CREATE TABLE weekdays
(
datevalue datetime NOT NULL
, numericvalue INT NOT NULL
);
INSERT INTO weekdays (datevalue, numericvalue) VALUES
('2015-09-01', 1000),
('2015-09-02', 1001),
('2015-09-07', 1003),
('2015-09-08', 1004),
('2015-09-12', 1005),
('2015-09-13', 1006),
('2015-09-14', 1007),
('2015-09-19', 1008);
the output should look like this
weeknototalvalue
362015-09-01 00:00:00.000
362015-09-02 00:00:00.000
372015-09-07 00:00:00.000
372015-09-08 00:00:00.000
382015-09-12 00:00:00.000
382015-09-13 00:00:00.000
382015-09-14 00:00:00.000
392015-09-19 00:00:00.000

Quick suggestion, use the DATEPART function

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID('dbo.weekdays') IS NOT NULL DROP TABLE dbo.weekdays;

CREATE TABLE dbo.weekdays

(

datevalue datetime NOT NULL

, numericvalue INT NOT NULL

);

INSERT INTO dbo.weekdays (datevalue, numericvalue) VALUES

('2015-09-01', 1000),

('2015-09-02', 1001),

('2015-09-07', 1003),

('2015-09-08', 1004),

('2015-09-12', 1005),

('2015-09-13', 1006),

('2015-09-14', 1007),

('2015-09-19', 1008);

SELECT

DATEPART(WEEK, WD.datevalue) weekno

,WD.datevalue

FROM dbo.weekdays WD;

Results

weekno datevalue

----------- -----------------------

36 2015-09-01 00:00:00.000

36 2015-09-02 00:00:00.000

37 2015-09-07 00:00:00.000

37 2015-09-08 00:00:00.000

37 2015-09-12 00:00:00.000

38 2015-09-13 00:00:00.000

38 2015-09-14 00:00:00.000

38 2015-09-19 00:00:00.000

Neil Burton SSC-Insane Points: 23000 More actions · Answer 2

CREATE TABLE #weekdays

(

datevalue datetime NOT NULL

, numericvalue INT NOT NULL

);

INSERT INTO #weekdays (datevalue, numericvalue) VALUES

('2015-09-01', 1000),

('2015-09-02', 1001),

('2015-09-07', 1003),

('2015-09-08', 1004),

('2015-09-12', 1005),

('2015-09-13', 1006),

('2015-09-14', 1007),

('2015-09-19', 1008)

select

case DATEPART(dw,datevalue)

when 7 then DATEPART(wk,datevalue) +1

else DATEPART(wk,datevalue)

end

,datevalue

from #weekdays w

drop table #weekdays;

You may need to use a CASE statement to account for the non-standard weeks.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Nomvula Hall of Fame Points: 3778 More actions · Answer 3

Nomvula

Hall of Fame

Points: 3778

September 17, 2015 at 6:33 am

#1827379

thank you so much, it worked!!!

ScottPletcher SSC Guru Points: 101099 More actions · Answer 4

That code is dependent on the DATEFIRST setting, and I don't believe it's accurate anyway. Maybe this?:

--additional dates for testing/verification

INSERT INTO #weekdays (datevalue, numericvalue) VALUES

('20150101', 1000),

('20150102', 1001),

('20150103', 1001),

('20150104', 1001),

('20150105', 1001),

('20150106', 1001),

('20150107', 1001),

('20150108', 1001),

('20150109', 1001),

('20150110', 1001)

SELECT

'Proposed',

DATEPART(DAYOFYEAR, datevalue) / 7 + 1 +

CASE WHEN DATEPART(DAYOFYEAR, datevalue) % 7 > DATEDIFF(DAY, 5, datevalue) % 7 THEN 1 ELSE 0 END AS Week#,

datevalue

FROM #weekdays

ORDER BY datevalue

select

'Original',

case DATEPART(dw,datevalue)

when 7 then DATEPART(wk,datevalue) +1

else DATEPART(wk,datevalue)

end as week#

,datevalue

from #weekdays w

order by datevalue

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".