I want to create a table that has a StartDate, EndDate, WeekNum for the year 2021 starting with the First Wednesday of January to the end of December ( I know the last week may land in 2022, that's fine). So in 2021, the first wednesday is January 6. So the table would start with the first record with startdate = 2019-01-06 and EndDate = 2019-01-12 and WeekNum = 1.
How can i fill in the rest of the weeks in this table?
Something like this using a tally table or function:
DECLARE @startDate date = '2021-01-06'
DECLARE @nextYear date = DATEADD(year,1,DATEADD(year,DATEDIFF(year,0,@startDate),0));
SELECT DATEADD(WEEK,Number,@startDate ) AS StartDate, DATEADD(DAY,6,DATEADD(WEEK,Number,@startDate )) AS EndDate, Number + 1 AS WeekNumber
FROM dbo.fnTally(0,53)
WHERE DATEADD(WEEK,Number,@startDate) < @nextYear;
See Jeff Moden's posts on SqlServerCentral for tally tables & functions if needed.
October 27, 2022 at 5:09 pm
Something like this using a tally table or function:
DECLARE @startDate date = '2021-01-06'
DECLARE @nextYear date = DATEADD(year,1,DATEADD(year,DATEDIFF(year,0,@startDate),0));
SELECT DATEADD(WEEK,Number,@startDate ) AS StartDate, DATEADD(DAY,6,DATEADD(WEEK,Number,@startDate )) AS EndDate, Number AS WeekNumber
FROM dbo.fnTally(0,53)
WHERE DATEADD(WEEK,Number,@startDate) < @nextYear;See Jeff Moden's posts on SqlServerCentral for tally tables & functions if needed.
This looks close to what I need, but "Number" in your code has to change 52 or 53 times....to get every week for the year.
October 27, 2022 at 5:15 pm
ratbak wrote:Something like this using a tally table or function:
DECLARE @startDate date = '2021-01-06'
DECLARE @nextYear date = DATEADD(year,1,DATEADD(year,DATEDIFF(year,0,@startDate),0));
SELECT DATEADD(WEEK,Number,@startDate ) AS StartDate, DATEADD(DAY,6,DATEADD(WEEK,Number,@startDate )) AS EndDate, Number AS WeekNumber
FROM dbo.fnTally(0,53)
WHERE DATEADD(WEEK,Number,@startDate) < @nextYear;See Jeff Moden's posts on SqlServerCentral for tally tables & functions if needed.
This looks close to what I need, but "Number" in your code has to change 52 or 53 times....to get every week for the year.
Will you always start a year on the first Wednesday of January? If not, what are the rules for calculating the start of all years?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 27, 2022 at 5:22 pm
Do you have Jeff's fnTally function and did you test it? It is a TVF that in this case returns numbers 1-52...
after I fixed the script to return Number + 1 (returned 0-51 before) 🙂
October 27, 2022 at 5:31 pm
Jeff, yes it will always start on Wednesday. So the week is from Wednesday thru Tuesday.
October 27, 2022 at 5:37 pm
Do you have Jeff's fnTally function and did you test it? It is a TVF that in this case returns numbers 1-52...
after I fixed the script to return Number + 1 (returned 0-51 before) 🙂
Yes, I have the fnTally function. When I run your code the way it is, it errors "Invalid column name 'Number'
October 27, 2022 at 6:12 pm
Generate your dates and then play the following function against those dates using "3" as an @DateFirst parameter. Post back if needed.
The dates don't need to be contiguous. You could multiply the N of fnTally by 7 to get only week start dates and play the function against those.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 27, 2022 at 6:30 pm
Yes, I have the fnTally function. When I run your code the way it is, it errors "Invalid column name 'Number'
Sorry. His version uses "N". I modified my version to use "Number" to match a pre-existing tally table.
October 27, 2022 at 7:05 pm
Yes, I have the fnTally function. When I run your code the way it is, it errors "Invalid column name 'Number'
Sorry. His version uses "N". I modified my version to use "Number" to match a pre-existing tally table.
Thanks, I made that change and it worked fine now. Just had to add N + 1 As Weeknumber and that fixed the weeknumber as well.
October 27, 2022 at 7:48 pm
My question now is... how do you intend to use this new table? There are a few ways and a lot of people get into trouble because there's only a couple of ways that are actually good for performance. What kind of data are you going to play against it and how much. For example, does your data have multiple entries per day? If so, make sure you pre-aggregate the data by day for a pretty hefty performance improvement compared to playing every row against your new week-table.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 27, 2022 at 8:09 pm
Jeff, We have an existing table that houses this information for other years. I was tasked with adding the dates for year 2021 to this existing table, but I didn't know how it was built to begin with. It is used as a cross reference table in a query to build a dataset.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply