September 16, 2009 at 4:49 am
Hi there,
I wonder if anyone can help me with this.
I have a date table that has the following columns
Weekno (1 - 52)
PeriodNo (1 - 12)
WeekStartDate (Every Sunday)
WeekEndDate (Every Saturday)
Year (Financial year i.e. 2009)
I want to be able to populate the table but I'm not sure how to do this so that I'm populateing the WeekStartDate with Saturday dates and WeekEndDate with Sunday dates. Specifically, the fields I'm not sure about is the WeekStartDate and WeekEndDate, I'm sure the other fields I can work out
Does anyone have existing code or know where I can get it that may help.
Many thanks
September 16, 2009 at 7:26 am
This is a pretty simple query if you're familiar with Tally/number tables. Take a look at the article in my signature for a full explanation of what a Tally table is and how it works. If you have any questions, let us know and we'll be happy to help.
September 16, 2009 at 7:56 am
Following up on what Seth said, you could try something like the below. However, you should note that the first week will start January 1, so if your first financial week needs to start on a Sunday, you will have to adjust.
DECLARE @year CHAR(4)
SELECT @year = 2009
SELECT
WeekNo,
PeriodNo,
WeekStartDate = MIN(CASE WHEN Dy = 'Sunday' THEN Dt ELSE NULL END),
WeekEndDate = DATEADD(day,6,MIN(CASE WHEN Dy = 'Sunday' THEN Dt ELSE NULL END))
FROM
(
SELECT
Dt,
Dy,
WeekNo = DATEPART(week,dt),
PeriodNo = DATEPART(month,dt)
FROM
(
SELECT
Dt = DATEADD(day,n-1,'1/1/' + @year),
Dy = DATENAME(dw,DATEADD(day,n-1,CONVERT(DATETIME,('1/1/' + @year))))
FROM tally t
WHERE t.n < = 365
) t1
) t2
WHERE CASE WHEN Dy = 'Sunday' THEN Dt ELSE NULL END IS NOT NULL
GROUP BY WeekNo,PeriodNo
Edited: I just realized I had '1/1/2009' hard coded in one spot. I changed it to '1/1/' + @year
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 16, 2009 at 9:37 am
Similar to what Greg posted, here is another method. Both have advantages and disadvantages, but together they should give you a good idea of how to use a tally table for this (once you know what a tally table is).
DECLARE @StartDatedatetime,
@EndDatedatetime
SET @StartDate = '01/01/2006' -- Set to the First Sunday you want to use in your table.
SET @EndDate = '12/31/2025' -- End of your Calendar Table
SELECT
DATEPART(wk, DATEADD(d,((N-1)*7),@StartDate)) WeekNo,
DATEPART(mm, DATEADD(d,((N-1)*7),@StartDate)) PeriodNo,
DATEPART(yy, DATEADD(d,((N-1)*7),@StartDate)) YearNo,
DATEADD(d,((N-1)*7),@StartDate) WeekStartDate,
DATEADD(d,-1,(DATEADD(d,(N*7),@StartDate))) WeekEndDate
FROM Tally
WHERE DATEADD(d,((N-1)*7),@StartDate) < @EndDate
There are a million unanswered questions about this, such as :
Should each new year start at 1/1, or should they be continuous.
What does period Tie to, start date, end date, number of days in the week in each period, something else?
Same thing for Year
How do you handle Leap Years or years where sunday is January 1st (Like 2006), which result in 53 week start dates (12/31/2006 is a Sunday which would start week 53).
Etc. Etc.
These decisions will affect how you write your population script.
September 16, 2009 at 9:54 am
Garadin Both have advantages and disadvantages
Well I can see one, which is that mine is limited to only one year at a time. Other than that, yours is simply at a higher level of thinking than what I can do with SQL 🙂
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 16, 2009 at 10:11 am
Greg Snidow (9/16/2009)
Garadin Both have advantages and disadvantages
Well I can see one, which is that mine is limited to only one year at a time. Other than that, yours is simply at a higher level of thinking than what I can do with SQL 🙂
But because it's limited to one year at a time, you start the counts over on January 1st of each year, where as mine starts at whatever day the first sunday is. Not sure which is going to work better for his purposes. Trying to adapt mine to mimic new years the way yours does would get quite a bit more complex.
FWIW, the reasons I list in my above post are some of the reasons why I don't really like calendar tables unless they're by day and meant for specific things like holiday/business schedules. I wouldn't personally make a weeks table like this. For any given date, unless you're starting on a different fiscal schedule than January 1st (Which I believe you actually are in this case because you have another post on it), you can just use DATEFIRST/DATEPART with the appropriate designator to pull this information out without the need for this table.
September 16, 2009 at 11:55 am
pwatson-904240 (9/16/2009)
Hi there,I wonder if anyone can help me with this.
I have a date table that has the following columns
Weekno (1 - 52)
PeriodNo (1 - 12)
WeekStartDate (Every Sunday)
WeekEndDate (Every Saturday)
Year (Financial year i.e. 2009)
I want to be able to populate the table but I'm not sure how to do this so that I'm populateing the WeekStartDate with Saturday dates and WeekEndDate with Sunday dates. Specifically, the fields I'm not sure about is the WeekStartDate and WeekEndDate, I'm sure the other fields I can work out
Does anyone have existing code or know where I can get it that may help.
Many thanks
What is your definition of the first week of the year? Also, what is your definition of "PeriodNo".
--Jeff Moden
Change is inevitable... Change for the better is not.
September 16, 2009 at 12:17 pm
In order to accommodate all the different reporting requirements based on a single date...I tend to build a calendar table in Excel...using the date functions/calc cols and fill down abilities.
very easy to have 13 fiscal periods/yr alter the year/wk no start date . no of weeks in a year etc
The I fine tune the spreadsheet to reflect the oddities....like Easter Hols and any other speciific dates for the business .
then I import into SQL.
but of course that takes away the fun of coding 😀
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 17, 2009 at 4:53 am
First of all, I'd like to thank everyone for the input to my thread, without this sort of help, i'd be lost, so one day I'd like to help as well.
I'll have a look at all suggestions today and test them to see what I can come up with
Garidin: but together they should give you a good idea of how to use a tally table for this (once you know what a tally table is).
I was sort of familiar with the idea of the table as it was mentioned in Itzik Ben-Gan's T-SQL querying. I'll work on becoming more familiar with this during the weekend. At the moment, there's already a calendar table in the workplace which has the afore-mentioned fields (i.e. weekno, period). The forsight questions, like leap years etc is something I'll have to think about, thanks for bringing this topic up.
Jeff Moden:
What is your definition of the first week of the year? Also, what is your definition of "PeriodNo".
The first week of the year will always be the last Sunday of March of that year. The period is something I'll have to discuss with whoever's feeding the financial year dates ( I think)
Gah:
...I tend to build a calendar table in Excel
I'll certainly look into this after the present hurdle 🙂
Be Back soon...
September 21, 2009 at 9:08 am
Hi folks,
Similar to what Greg posted, here is another method. Both have advantages and disadvantages, but together they should give you a good idea of how to use a tally table for this (once you know what a tally table is).
I tried this and changed the code slightly so the week no. would start on the last Sunday of March:
DATEPART(wk, DATEADD(d,((N-14)*7 )+7,@StartDate)) WeekNo
So that's a great help. I've just one more question, I've been trying to work out n-1 (or in my case n-14)... is this referring to the week no position in the year? I'd be grateful if you could clarify this.
Now I'll use the code to put into the exising calendar table that's been set up.
Many thanks to all for your help.
September 21, 2009 at 9:14 am
The reason for N-1 all over my code is that my Tally Table starts at 1, where for the purposes of this, you need the first N to be 0. Something like this:
DATEPART(wk, DATEADD(d,((N-1)*7),@StartDate)) WeekNo,
is really
Get the Week of: Add (N[1]-1)[0]*7 [0] Days to the startdate
then, the next time
Get the week of Add (N[2]-1)[1]*7 [7] Days to the startdate
then the next time
Get teh week of ADD((N[3]-1)[2]*7 [14] Days to the startdate
etc. etc.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply