Code to acomodate year starting in April

Question

Code to acomodate year starting in April

cidr

Ten Centuries

Points: 1353
More actions
September 14, 2009 at 9:25 am

#100673

Hi there, hope you're all well!
This question I have is more conceptual and emcompasses more than T-SQL so I hope someone can help.
I think it's best if I explain the schema first. I have a "date" table which has the following columns:
Year (Stores year)
PeriodNumber (Stores month period)
WeekNumber (stores week number 1 - 52)
WeekStartDate (Starts on a Sunday)
WeekEndDate (Ends on a Saturday)
The database I'm working with records calls that have been logged for repairs. The query will display all calls made for that the current year and it will also have two columns called CurrentWeekCall and WeekNo. WeekNo displays the number for which the call was logged (1-52) and each row that has last weeks number against will have a "1" in the CurrentWeekColumn. This can then be summed in a report that will show how many calls where logged "last week"
The code for WeekNo:
select
YearDate
PeriodNo,
WeekNo,
WeekStartDate+1,
WeekEndDate+1
from tblWeekNo
Sample results:
Year Period WeekNo WeekStartDate WeekEndDate
2009 1 1 2009-01-05 00:00:00.000 2009-01-11 00:00:00.000
2009 1 2 2009-01-12 00:00:00.000 2009-01-18 00:00:00.000
2009 1 3 2009-01-19 00:00:00.000 2009-01-25 00:00:00.000
2009 1 4 2009-01-26 00:00:00.000 2009-02-01 00:00:00.000
And for the currentWeekNo I use the following code to capture what the last week no was:
select max(WeekNo) 'LastWeekNo'
from tblWeekNo
and WeekEndDate+1 < getdate() and YearDate = DATEPART(YY,GETDATE()) [/code] Which just gives one scalar value:
36 (which was last weeks WeekNo)
The problem I have which I may need help with is that, the system I have is based on the year ending on the first Saturday of January and starting on the first Sunday of January. The year actually ends on the last Saturday of March and starts on the first Sunday of March. This obviously means I'm crossing into new years when actually for the trading year it's the same.
I would like to ask for guidance on the best way to manage this. For example, I don't want to see the previous trading year lets say March 30 2008 - March 28 2009. However, in Janury 2010 I do want to see from March 29 2009 to present.
What would be the best way to implement this?
I'm always grateful for any help.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

I think part of your problem is that you're basing the weekno off of getdate, when you aren't using calendar years to represent what the week numbers should be. Customer Fiscal years are a very common challenge for developers.

If you rely on the *standard year* week number for other things and only want to use the customers fiscal week number for certain queries, you could add an additional field to the table and store the customers fiscal week number in that. This could even be a calculated field so that you didn't need to change the queries that populate the table. You can also skip the additional field and just calculate it during your query. Your environment will determine the best way to implement the concept. In my enviroment, I simply store the fiscal start date and I dynamically assign items to the customers fiscal structure based on their fiscal start at run time.

If you cannot modify the base table or the insert scripts, a good compromise might be a table valued function that accepts a fiscal year start date in and generates a table with all this information that you can link to.

Let me know which way you'd like to go and what else we can do to help get you there.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

This part is confusing:

The problem I have which I may need help with is that, the system I have is based on the year ending on the first Saturday of January and starting on the first Sunday of January. The year actually ends on the last Saturday of March and starts on the first Sunday of March. This obviously means I'm crossing into new years when actually for the trading year it's the same.

The year ends before it starts? Can you show me using dates what is going on?

cidr Ten Centuries Points: 1353 More actions · Answer 3

Thanks both for responding.

Lynn, yeah, I noticed the mistake I made, I actually meant that the year ends on the last Saturday and starts on the last Sunday of March. Apologies for the confusion

Garadin, I was thinking of creating a new "Date" table with the new values (same columns as the previous table), week no 1 starting on the last Sunday of March etc. I just wasn't sure of the best way to change the query (which I think would be the way I'd do it) so that it's pulling the correct data (i.e. data from march 2009 to march 2010)

There's areas of T-SQL programming where I'm not too strong in. any help here actually would create a ripple effect in numerous reports I've been asked to work with.

Thanks:)

Garadin One Orange Chip Points: 29613 More actions · Answer 4

pwatson (9/15/2009)
Garadin, I was thinking of creating a new "Date" table with the new values (same columns as the previous table), week no 1 starting on the last Sunday of March etc. I just wasn't sure of the best way to change the query (which I think would be the way I'd do it) so that it's pulling the correct data (i.e. data from march 2009 to march 2010)

That's an OK way to do it provided:

1. The number of different fiscal year setups is limited (You don't want to create a new whole set of week numbers with for every start date of the year, that'd be ridiculous).

2. The fiscal year information does not change often.

Assuming these are true, a lookup table works fine. I mentioned as much in my original reply but I think I deleted it because of the restrictions.

Modifying the query for this setup is very easy. You're just pulling information out of a table.

select MIN(WeekNo) 'LastWeekNo'

from tblWeekNo

WHERE WeekEndDate > CallDate and YearDate = DATEPART(YY,WeekEndDate)

That would modify that piece of code that you displayed.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Remove from list SSC Veteran Points: 276 More actions · Answer 5

Regarding a date table, it can handle many complexities. For example, fields in the table could include calendar date, date in Julian format, week of calendar year, week of Federal Fiscal Year, week of State Fiscal Year, week of X Fiscal year, day of week, holiday flag (Y/N), flu season flag, college semester, etc. Each programmer just uses the fields that apply to a given situation and ignores the rest.

Regarding the main data table, it may be helpful to add fields that identify the fiscal year, week in fiscal year, or any other value that is critical for regular processing or reporting.

In general, we want the data acquisition to be in tables with at least 3rd level normalization. So, it might be wise to consider creating a separate version of the table for reporting purposes. The reporting tables can be de-normalized to aid in report creation.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

pwatson-904240 (9/15/2009)
Thanks both for responding.
Lynn, yeah, I noticed the mistake I made, I actually meant that the year ends on the last Saturday and starts on the last Sunday of March. Apologies for the confusion...

Even that definition doesn't really work.

For example, if March 31 is a Saturday, the last Saturday would be March 31 and the last Sunday would be March 25, so the new fiscal year would start 6 days before the prior one ended.