August 25, 2022 at 2:09 pm
I need to create a query where the first of March will be Week 1 starting from Monday to Sunday from the date. If the first of the month starts on a Wednesday then I expect 1st to the 5th to be Week 1 and 6th to the 12th to be Week 2.
The calendar season for this is 01 March to 28 or 29 Feb
DECLARE @StartDate DATETIME = '20220306',
@EndDate DATETIME = '20220312'
SELECT
aa.Id AS AssetId
,FORMAT(DATEADD (HOUR, sites.TimeZoneOffSet, pls.StartTime), 'dd/MM/yyyy') AS [Date]
,1 + (datediff( day,
datefromparts(year(dateadd(month, -2, DATEADD (HOUR, sites.TimeZoneOffSet, pls.StartTime))), 3, 1),
DATEADD (HOUR, sites.TimeZoneOffSet, pls.StartTime) ) / 7) AS WeekNumbers
FROM dbo.prdLogSheets AS pls
INNER JOIN dbo.astAssets AS aa ON aa.Id = pls.AssetId
INNER JOIN dbo.stdSites AS sites ON sites.Id = aa.SiteId
WHERE DATEADD(HOUR,sites.TimeZoneOffSet,pls.StartTime) >= @StartDate
AND DATEADD(HOUR,sites.TimeZoneOffSet,pls.StartTime) <= @EndDate
AND aa.Id = 20467
The query above returns the 6th and 7th as Week1 and 08 to the 12th as Week 2, Whereas it should be Week2
August 25, 2022 at 3:24 pm
What if the first of the month is a Sunday? Is the first week just one day and the second week the 2nd to 8th? like May 2022.
Is this right?
May 1-1: Week 1
May 2-8: Week 2
May 9-15: Week 3
May 16-22: Week 4
May 23-29: Week 5
May 30-31: Week 6
June 1-5 Week 1 ...
August 25, 2022 at 3:45 pm
That's correct!!
August 25, 2022 at 5:05 pm
I think both methods below get the right result. I think the second one is better, but it relies on SET DATEFIRST 1 to make the weeks end on Sunday, not Saturday. This may not be the standard way to make a dynamic tally table, but that's not the subject of the question. The Tally CTE is used to create a list of dates which are assigned a week of month. I don't know how to include this with your query, I would create a table and use that.
Version 1
DECLARE @StartDate DATE = '2022-03-01';
WITH t1 AS
(SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS a (n) ),
Tally AS
( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number
FROM t1 AS a CROSS JOIN t1 AS b CROSS JOIN t1 AS c CROSS JOIN t1 AS d)
SELECT b.SQLDate, Calendaryear, CalendarMonth, Sunday,
ISNULL(SUM(Sunday) OVER (PARTITION BY c.CalendarYear, c.CalendarMonth
ORDER BY c.CalendarYear, c.CalendarMonth
ROWS BETWEEN 31 PRECEDING AND 1 PRECEDING)
,0) +1 AS WeekNumber
FROM Tally AS a
CROSS APPLY (SELECT DATEADD(DAY, a.Number-1, @StartDate) AS SQLDate) AS b
CROSS APPLY (SELECT DATEPART(YEAR, b.SQLDate) AS CalendarYear,
DATEPART(MONTH, b.SQLDate) AS CalendarMonth,
CASE WHEN DATEPART(DW, b.SQLDate) = 6 THEN 1 ELSE 0 END AS Sunday) AS c
WHERE a.Number < 1000
Version 2
SET DATEFIRST 1
DECLARE @StartDate DATE = '2022-03-01';
WITH t1 AS
(SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS a (n) ),
Tally AS
( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number
FROM t1 AS a CROSS JOIN t1 AS b CROSS JOIN t1 AS c CROSS JOIN t1 AS d)
SELECT b.SQLDate, DATEPART(WEEK, b.SQLDate) AS GregorianWeekOfYear,
DENSE_RANK() OVER ( PARTITION BY DATEPART(YEAR, b.SQLDate), DATEPART(MONTH, b.SQLDate)
ORDER BY DATEPART(YEAR, b.SQLDate), DATEPART(MONTH, b.SQLDate), DATEPART(WEEK, b.SQLDate)
) AS WeekNumber
FROM Tally AS a
CROSS APPLY (SELECT DATEADD(DAY, a.Number-1, @StartDate) AS SQLDate) AS b
WHERE a.Number < 1000
August 25, 2022 at 6:14 pm
No need to overly complicate this. Just calc the starting Monday date, then determine week# displaced off that "base" date.
I moved the time adjustment into a CROSS APPLY only so that it is only done once. Needless to say, that's not required and you could put the calc back in-line if you prefer.
For performance, I also adjusted the WHERE clause to make it sargable. I'm a DBA, and using the function typically prevents using the matching (hopefully clustered) index I build on StartTime for you!
DECLARE @StartDate DATETIME = '20220306', @EndDate DATETIME = '20220312'
DECLARE @FirstMondayInMarch DATE
SET @FirstMondayInMarch = DATEADD(DAY, -DATEDIFF(DAY, 0, DATEFROMPARTS(YEAR(@StartDate), 3, 7)) % 7, DATEFROMPARTS(YEAR(@StartDate), 3, 7))
SELECT
...,
CASE WHEN StartDay < @FirstMondayInMarch THEN 0
ELSE DATEDIFF(DAY, @FirstMondayInMarch, calc1.StartDay) / 7 + 1 END AS WeekNumber,
...
FROM dbo.YourTable
CROSS APPLY (
/* do the time adjustment ONE time and use the resulting day throughout the query */
SELECT CAST(DATEADD(HOUR, sites.TimeZoneOffSet, pls.StartTime) AS date) AS StartDay
) AS calc1
...
WHERE pls.StartTime >= DATEADD(HOUR,-sites.TimeZoneOffSet,@StartDate) --<<-- sargable!
AND pls.StartTime <= DATEADD(HOUR,-sites.TimeZoneOffSet,@EndDate) --<<-- sargable!
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".
August 25, 2022 at 6:54 pm
I need to create a query where the first of March will be Week 1 starting from Monday to Sunday from the date. If the first of the month starts on a Wednesday then I expect 1st to the 5th to be Week 1 and 6th to the 12th to be Week 2.
The calendar season for this is 01 March to 28 or 29 Feb
DECLARE @StartDate DATETIME = '20220306',
@EndDate DATETIME = '20220312'
SELECT
aa.Id AS AssetId
,FORMAT(DATEADD (HOUR, sites.TimeZoneOffSet, pls.StartTime), 'dd/MM/yyyy') AS [Date]
,1 + (datediff( day,
datefromparts(year(dateadd(month, -2, DATEADD (HOUR, sites.TimeZoneOffSet, pls.StartTime))), 3, 1),
DATEADD (HOUR, sites.TimeZoneOffSet, pls.StartTime) ) / 7) AS WeekNumbers
FROM dbo.prdLogSheets AS pls
INNER JOIN dbo.astAssets AS aa ON aa.Id = pls.AssetId
INNER JOIN dbo.stdSites AS sites ON sites.Id = aa.SiteId
WHERE DATEADD(HOUR,sites.TimeZoneOffSet,pls.StartTime) >= @StartDate
AND DATEADD(HOUR,sites.TimeZoneOffSet,pls.StartTime) <= @EndDate
AND aa.Id = 20467
The query above returns the 6th and 7th as Week1 and 08 to the 12th as Week 2, Whereas it should be Week2
It's just a little unclear as to what you want for week numbers. Do you want them to start over on the first week of the month or continue to increase for each week in your "season"?
I know you said "that's correct" to Ed's question... I just want to be absolutely sure.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2022 at 7:01 pm
Does that take care of the whole "season" of dates the OP identified
I need to create a query where the first of March will be Week 1 starting from Monday to Sunday from the date. If the first of the month starts on a Wednesday then I expect 1st to the 5th to be Week 1 and 6th to the 12th to be Week 2.
I think it works for March, but not any other months. The week number keeps increasing and the weeks are always 7 days. At least that's what I saw when I ran it against a list of dates.
I modified Scott's code to use a FirstMondayInMonth, not FirstMondayInMarch, and I think it works. I don't know if that's the best way to get the first Monday, but I just copied the existing code and replaced the hard coded 3 with a datepart month to see if it worked. It looks right for May 2022, which has a single day for week 1.
DECLARE @StartDate DATETIME = '20220306', @EndDate DATETIME = '20220312'
SELECT
...,
CASE WHEN StartDay < FirstMondayInMonth THEN 1
ELSE DATEDIFF(DAY, FirstMondayInMonth, calc1.StartDay) / 7 + 1 END AS WeekNumber,
...
FROM dbo.YourTable
CROSS APPLY (
/* do the time adjustment ONE time and use the resulting day throughout the query */ SELECT CAST(DATEADD(HOUR, sites.TimeZoneOffSet, pls.StartTime) AS date) AS StartDay
) AS calc1
CROSS APPLY( SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, DATEFROMPARTS(YEAR(@StartDate), (DATEPART(MONTH,calc1.StartDay)), 7)) % 7, DATEFROMPARTS(YEAR(@StartDate),
(DATEPART(MONTH,calc1.StartDay)), 7)) as FirstMondayinMonth) AS calc2
...
WHERE pls.StartTime >= DATEADD(HOUR,-sites.TimeZoneOffSet,@StartDate) --<<-- sargable!
AND pls.StartTime <= DATEADD(HOUR,-sites.TimeZoneOffSet,@EndDate) --<<-- sargable!
August 26, 2022 at 9:10 am
Thank you all, this seems to work
August 26, 2022 at 9:12 am
Thank you Scott
August 26, 2022 at 3:27 pm
Does that take care of the whole "season" of dates the OP identified
Nomvula wrote:I need to create a query where the first of March will be Week 1 starting from Monday to Sunday from the date. If the first of the month starts on a Wednesday then I expect 1st to the 5th to be Week 1 and 6th to the 12th to be Week 2.
I think it works for March, but not any other months. The week number keeps increasing and the weeks are always 7 days. At least that's what I saw when I ran it against a list of dates.
That's what I understood the requirement to be. If you go month by month, you'd never get a Week 6, as shown in OP's example.
Then again, maybe I misunderstood the requirement.
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".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply