Help Grabbing Correct Date For Fiscal Periods

[Jdbenike]

Got a question about grabbing a date in a fiscal period.
Below is just a sample code that grabs data from this table back to 2008-10-31. As you can see each Year Period has a number for the month 01 through 12, then a 01, 02, 03, 04 ect for the week of the period.

select [YPWEnd], [YPWPd], [YPWWk] from [RODB].[dbo].[tYPW]
WHERE [YPWEnd] <= getdate()
AND [YPWEnd] > '2008-10-31 00:00:00.000'

Date ----YearPeriod---YearPeriodWeek
2008-11-01 00:00:00.000 11 01
2008-11-08 00:00:00.000 11 02
2008-11-15 00:00:00.000 11 03
2008-11-22 00:00:00.000 11 04
2008-11-29 00:00:00.000 11 05
2008-12-06 00:00:00.000 12 01
2008-12-13 00:00:00.000 12 02
2008-12-20 00:00:00.000 12 03
2008-12-27 00:00:00.000 12 04
2009-01-03 00:00:00.000 12 05
2009-01-10 00:00:00.000 01 01
2009-01-17 00:00:00.000 01 02
2009-01-24 00:00:00.000 01 03
2009-01-31 00:00:00.000 01 04
2009-02-07 00:00:00.000 02 01
2009-02-14 00:00:00.000 02 02
2009-02-21 00:00:00.000 02 03
2009-02-28 00:00:00.000 02 04
2009-03-07 00:00:00.000 03 01
2009-03-14 00:00:00.000 03 02
2009-03-21 00:00:00.000 03 03
2009-03-28 00:00:00.000 03 04


My question is. If Iam grabbing the most recent date which is the 3-28, how would I go about grabbing the previous Periods last Day. So, if I grabbed 3-28, I would be grabbing period 3, week 4. How do I get Period 02, and it's last date.


This code

SELECT [YPWEnd], CONVERT(INT,[YPWPd]), [YPWWk]
FROM [RODB].[dbo].[tYPW]
WHERE [YPWEnd] <= getdate()
AND [YPWEnd] = (SELECT MAX([YPWEnd]) FROM [RODB].[dbo].[tYPW] WHERE [YPWEnd] <= getdate())

Grabs just
2009-03-28 00:00:00.000 3 04

so from that how would I grab ??????
2009-02-28 00:00:00.000 02 04

Keep in mind now.. 04 is not the max weeks, sometimes i can be 05. Also keep in mind that this last week of each period could be in the following month.

The purpose is that if a person ran a report today, it would grab the last date range of the last fiscal period.

 

[RyanEK]

One of many ways to do this...

select top 1 *
from (
  select t1.*
  from [Table] t1
  inner join (
	select top 1 t2.yearperiod, t2.yearperiodweek
	from [Table] t2
	order by t2.date desc
  ) as t3 on convert(int,t1.yearperiod) < convert(int,t3.yearperiod)
) t
order by date desc