Comparing a date with the same day last year

[bajanpoet]

The code snippet below is the beginning of a script I've been working on. What I need to figure out is make sure that I can generate the corresponding day last year. The business logic is such that I am supposed to compare sales data from the last business day this year with the comparative day last year. (Look at the comments in the snippet.

--Declaration of variables
declare @LastBusinessDay varchar(8),   -- Last Business Day
            @PrevBusinessDay varchar(8),   -- Same Business Day last year


--Initalization of variables

set @LastBusinessDay = convert(varchar(8),dateadd(day, case when datepart(weekday,GETDATE()) = 1 then -2 when datepart(weekday, GETDATE()) = 2 then -3 else -1 end, GETDATE()),112)
set @PrevBusinessDay = CONVERT(varchar(8),dateadd(year, -1, @LastBusinessDay),112)

-- To compare the same day with the same business day in the year 
-- Monday  Jan. 11 2010  ---> Monday Jan 12, 2009 (Rather than Sun Jan 11, 2009)

declare @LBDName varchar(8), @PBDName varchar(8)
set @LBDName = DATENAME(DW, @LastBusinessDay)
set @PBDName = DATENAME(DW, @PrevBusinessDay)

select @LBDName as [This Year], @PBDName as [Last Year]

If this is run you will see that the day represented by the date in @LastBusinessDay was Friday, but the day represented by the same date last year was a Thursday. I need to be able to compare Friday to Friday automatically.

[sknake]

When does your fiscal year begin? Usually that is "week 1". I would get the week number from the fiscal year and calculate forward from the previous years base date. Some companies use "the first full week in January" for example. Let me know if this is how your company operates and what your year-begin is.

[bajanpoet]

When does your fiscal year begin? Usually that is "week 1". I would get the week number from the fiscal year and calculate forward from the previous years base date. Some companies use "the first full week in January" for example. Let me know if this is how your company operates and what your year-begin is.

Thanks for replying! The fiscal year starts in October.

[sknake]

So you want to do fiscal week comparisons with a calendar that beings the week October 1st regardless of the day of week? That doesn't sound right...

[bajanpoet]

I'm only saying what my managers want :) The whole script (not given) pulls sales data for that particular day (which is calculated as the last business day - If today is Tuesday Jan 13, the last business day is Monday Jan 12). That date is stored in a variable @LastBusinessDay. I use that script to pull out the invoice totals for the date in the variable. My bosses want to compare that date with the comparative date last year. However, if that date (the previous year) lands on a Sunday, I need to be able to automatically compare it with the next day. Basically, if this year is Monday, I need to get the invoice totals for the comparative Monday the year before.

I don't know if I can make it clearer. That's what my bosses asked for.

[padtes]

Would you rather subtract 52 weeks from your @LastBusinessDay? Because, even if today was Wed Jan 13 2010, Last year Jan 13 is Tue. select dateadd(wk, -52, getDate()) See if this is better? If it helps lets close this thread.

[bajanpoet]

First I check to see if there are more than one record in this batch

select @BatchCount =  COUNT(datebtch) from ARIBC where DATEBTCH = @LastBusinessDay

Runs this code to decrement the year

set @LastBusinessDay = CONVERT(varchar(8),dateadd(DAY, -1, @LastBusinessDay),112) -- Decrement @LastBusinessDay
set @PrevBusinessDay = CONVERT(varchar(8),dateadd(year, -1, @LastBusinessDay),112) --Same day last year

When I run the code the previous year may not be a working day (Sat or Sun etc) My boss wants to compare like with like he said, so that we are comparing the day this year with the same business day previously Monday Jan. 11 2010 ---> Monday Jan 12, 2009 (Rather than Sun Jan 11, 2009)

This test code below shows the DAY of the week represented by @LastBusinessDay and @PrevBusinessDay

declare @LBDName varchar(8), @PBDName varchar(8)
set @LBDName = DATENAME(DW, @LastBusinessDay)
set @PBDName = DATENAME(DW, @PrevBusinessDay)

select @LBDName as [This Year], @PBDName as [Last Year]

I hope this explains what my boss wants more explicitly.

I need this to work, so I can't just close the request!

[bajanpoet]

Would you rather subtract 52 weeks from your @LastBusinessDay? Because, even if today was Wed Jan 13 2010, Last year Jan 13 is Tue. select dateadd(wk, -52, getDate()) See if this is better? If it helps lets close this thread.

HEY! Thanks! It worked.... :)