Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tjwent69
Starting Member
30 Posts |
 Posted - 2008-09-02 : 14:54:27
|
| Can I use a case to set datetime variables based off of the value of another variable?Variables@Year1Start as DateTime --2006@Year1End as DateTime --2006@Year2Start as DateTime --2007@Year2End as DateTime --2007@Year3Start as DateTime --2008@Year3End as DateTime --2008@x as int -- zero or oneI prompt the user for the current start and End datesThese are @Year3Start @Year3End. The other dates are calculated from these. So my history would sum(TotalSales) Where Dates >= StartDates and < EndDates For the TY vs LY for the Second Quarter here is how I do the dates.Set @Year3StartDate = '04/01/2008'Set @Year3EndDate = '06/30/2008' -- I do a dateadd in the querySet @Year2StartDate = DateAdd(yyyy, -1, @Year3StartDate) -- 04/01/2007 Set @Year1StartDate = DateAdd(yyyy, -2, @Year3StartDate) -- 04/01/2006Set @Year2EndDate = DateAdd(dd,1,(DateAdd(yyyy, -1, @Year3EndDate))) -- 07/01/2007Set @Year1EndDate = DateAdd(dd,1,(DateAdd(yyyy, -2, @Year3EndDate))) -- 07/01/2006For Year totals here is how I do the dates.Set @Year3StartDate = '01/01/2008'Set @Year3EndDate = '08/31/2008' -- I do a dateadd in the querySet @Year2StartDate = DateAdd(yyyy, -1, @Year3StartDate) -- 01/01/2007 Set @Year1StartDate = DateAdd(yyyy, -2, @Year3StartDate) -- 01/01/2006Set @Year2EndDate = @Year3StartDate -- 01/01/2008Set @Year1EndDate = @Year2StartDate -- 01/01/2007The start dates just subtract the appropriate number of years.The End dates need to be different. I was hoping to do a case statement and if @x was a 1 then do the dates like TY vs LY and a zero would to the totals. |
|
|
|
|
|
|
|
Setting DateTime variable with a Case