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.