Finding Last Week in Financial Period 1

[AngelB]

Hi All

In our SQL Server 2000 database, we have a table for our internal financial calendar.

This table contains the fields:

'Type' (= W or P for 'Week' or 'Period')
'Financial Year End' (Currently 2007)
'Week No'
'Period No'
'Start Date' and 'End Date' (for weeks and periods)
and lastly (for week records only) a field which tells us which period that week falls into.

My question is, given a week number and the financial year end, how to tell whether that week is the last week in the period that it falls into? Please let me know if this information needs clarifying.

Thanks in advance


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[gmmastros]

Can you show some sample data? I think it will go a long way in helping us to understand what you are trying to accomplish.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AngelB]

Hi there

Type Year_End Week_No Period_No Start_Date End_Date Period_Week_In

W 2007 27 NULL 2006-10-01 2006-10-07 7
W 2007 28 NULL 2006-10-08 2006-10-14 7
W 2007 29 NULL 2006-10-15 2006-10-21 7
W 2007 30 NULL 2006-10-22 2006-10-28 7
P 2007 NULL 7 2006-10-01 2006-10-28 NULL

The record beginning 'P' gives information for the whole period. The records beginning 'W' give information for each of the weeks within that period. I need to find out, given a week number and year_end number, whether the week in question is the last one in its period. Hope this helps clear things up.


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[gmmastros]

You should be able to get the last week in the period by using a self join, like this...

[tt][blue]
Select A.Year_End,
B.Week_No
From TableName A
Inner Join TableName B
On A.End_Date = B.End_Date
Where A.Type = 'P'
And B.Type = 'W'
[/blue][/tt]

You can use this to determine if a week/year combination is the last week in the period by doing this...

If Exists(
         Select A.Year_End,
                B.Week_No
         From   TableName A
                Inner Join TableName B
                  On A.End_Date = B.End_Date
         Where  A.Type = 'P' 
                And B.Type = 'W'
                And A.Year_End = @YearValueToTest
                And B.Week_No = @WeekValueToTest
         )
  Begin
    Select 'Last Week of Period'
  End
Else
  Begin
    Select 'Not last week of period.'
  End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AngelB]

Thanks George, just what I needed. I could kick myself for missing that though.


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy