September 17, 2015 at 2:44 am
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
September 17, 2015 at 2:55 am
Nomvula (9/17/2015)
i have the following table I need to select dates grouping them by weeks, my week start is Saturday to FridayCREATE 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
September 17, 2015 at 2:57 am
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.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
September 17, 2015 at 6:33 am
thank you so much, it worked!!!
September 17, 2015 at 1:11 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply