sql statement problem

Question

In my query I am trying to get yesterdays date and the exact same date on the selected year. So today is 12/08/2010

I want 12/07/2010 and 12/07/2009

I forgot to mention the last person who created the database stored all the booked dates as intgers so basically

     Date  Year month booked  time
     28Sep09 2009 09 40084 1159
    07Oct09 2009 10 40093 1221
    08Oct09 2009 10 40094 1025
    16Oct09 2009 10 40102 1058
    02Nov09 2009 11 40119 1708
    05Nov09 2009 11 40122 1213
    13Nov09 2009 11 40130 1502
    24Nov09 2009 11 40141 1004
    24Nov09 2009 11 40141 1030
    16Feb10 2010 02 40225 1150
    16Feb10 2010 02 40225 1158
    08Mar10 2010 03 40245 1249
    05Apr10 2010 04 40273 0937
    24May10 2010 05 40322 1559
    25May10 2010 05 40323 1539
    04Jun10 2010 06 40333 1428
    26Jul10 2010 07 40385 1708
    19Aug10 2010 08 40409 1637
    02Sep10 2010 09 40423 0927
    03Sep10 2010 09 40424 1253
    13Sep10 2010 09 40434 1534
    04Oct10 2010 10 40455 1341
    19Oct10 2010 10 40470 0939
    02Nov10 2010 11 40484 0923
    03Nov10 2010 11 40485 0854
    03Nov10 2010 11 40485 1259
    08Nov10 2010 11 40490 1210
    12Nov10 2010 11 40494 1121
    18Nov10 2010 11 40500 1222
    05Aug10 2010 08 40395 1649

my dates look like that : (

so this would replace my last query

Declare @YearToget Integer 
Set @yearToGet = 2008

SELECT TOP (100) PERCENT DIVISION, SDESCR, 
      DYYYY, SUM(APRICE) AS Sales, SUM(PARTY) AS PAX, 
      SUM(NetAmount) AS NetSales, 
      SUM(InsAmount) AS InsSales, 
      SUM(CancelRevenue) AS CXSales, 
      SUM(OtherAmount) AS OtherSales, 
      SUM(CXVALUE) AS CXValue 
FROM  dbo.B101BookingsDetails AS B101BookingsDetails 
WHERE BOOKED <= DateAdd(year, @YearToGet - Year(getdate()), 
                 DateAdd(day, DateDiff(day, 1, getdate()), 0) )
  AND @YearToGet
GROUP BY SDESCR, DYYYY, DIVISION 
HAVING      (DYYYY = @YearToGet) 
ORDER BY DIVISION, SDESCR, DYYYY 

Answer 1

Try:

Declare @YearToget Integer 
Set @yearToGet = 2008

  Select DateAdd(year, @YearToGet - Year(getdate()), 
         DateAdd(day, DateDiff(day, 1, getdate()), 0) )

In your query:

   Where Booked = DateAdd(year, 2008- Year(getdate()), 
             DateAdd(day, DateDiff(day, 1, getdate()), 0) )
      Or Booked = DateAdd(year, 2009- Year(getdate()), 
             DateAdd(day, DateDiff(day, 1, getdate()), 0) )
      Or Booked = DateAdd(year, 2010- Year(getdate()), 
             DateAdd(day, DateDiff(day, 1, getdate()), 0) )
      Or Booked = DateAdd(year, 2011- Year(getdate()), 
             DateAdd(day, DateDiff(day, 1, getdate()), 0) )

Or

   Where Booked In
      (DateAdd(year, 2008- Year(getdate()), 
             DateAdd(day, DateDiff(day, 1, getdate()), 0) ),
       DateAdd(year, 2009- Year(getdate()), 
             DateAdd(day, DateDiff(day, 1, getdate()), 0) ),
       DateAdd(year, 2010- Year(getdate()), 
             DateAdd(day, DateDiff(day, 1, getdate()), 0) ),
       DateAdd(year, 2011- Year(getdate()), 
             DateAdd(day, DateDiff(day, 1, getdate()), 0) ) )

Your query above, rewritten:

Declare @YearToget Integer  
Set @yearToGet = 2008  
Select Division, SDESCR,        
    DYYYY, Sum(APRICE) Sales, 
    Sum(PARTY) AS PAX,        
    Sum(NetAmount) NetSales,        
    Sum(InsAmount) InsSales,        
    Sum(CancelRevenue) CXSales,        
    Sum(OtherAmount) OtherSales,        
    Sum(CXVALUE) CXValue  
From dbo.B101BookingsDetails 
Where Booked <= DateAdd(year, @YearToGet - Year(getdate()),                   
                DateAdd(day, DateDiff(day, 1, getdate()), 0) )      
-- AND @YearToGet -- This line appears to be incomplete
Group By SDESCR, DYYYY, Division
Having (DYYYY = @YearToGet)  
Order By Division, SDESCR, DYYYY 

Answer 2

A couple of notes. First, you should not use "TOP 100 PERCENT". It provides no benefit. If you are trying to use this in a View, you should know that using TOP 100 PERCENT with an ORDER BY will not guarantee the order when querying against the View. Second, it would appear that you have a problem with your integer values in that they are off by two days compared to DateDiff(d,0, <date>). I have compensated for that in the Where clause with BD.Booked = N.DateInt - 2 but you should look into the reasons why this is the case.

Declare @YearToGet int
Set @YearToGet = 2008

;With RawData As
    (
    Select 39503 As Booked
    Union All Select 39509
    Union All Select 39535
    Union All Select 39620
    Union All Select 39791
    Union All Select 39838
    Union All Select 39899
    Union All Select 39134
    Union All Select 39139
    Union All Select 39139
    Union All Select 39140
    Union All Select 39146
    Union All Select 39146
    Union All Select 39146
    )
    , Numbers As 
    (
    Select 0 As Value, Year(GetDate()) As [Year]
        , Cast( DateDiff(d,0,GetDate()) as datetime ) As [Date]
        , DateDiff(d,0,GetDate()) As [DateInt]
    Union All 
    Select Value + 1, [Year] - 1
        , DateAdd(yyyy, -1, [Date])
        , DateDiff(d, 0, DateAdd(yyyy, -1, [Date]))
    From Numbers
    Where Value <= ( Year(GetDate()) - @YearToGet )
    )
Select DIVISION, SDESCR, DYYYY
    , SUM(APRICE) AS Sales
    , SUM(PARTY) AS PAX
    , SUM(NetAmount) AS NetSales
    , SUM(InsAmount) AS InsSales
    , SUM(CancelRevenue) AS CXSales
    , SUM(OtherAmount) AS OtherSales
    , SUM(CXVALUE) AS CXValue
From dbo.B101BookingsDetails As BD
    Join Numbers As N
        On Cast(N.[Year] As char(4)) = BD.DYYYY
Where BD.Booked = N.DateInt - 2
Group By DIVISION, SDESCR, DYYYY

Answer 3

select getdate() /* Today */
select dateadd(day, -1, getdate()) /* Yesterday */
select dateadd(year, -1, dateadd(day, -1, getdate())) /* 1 year ago yesterday */

Answer 4

yesterday

convert(varchar, getDate()-1, 101)

a year ago from yesterday

convert(varchar, dateadd(year, -1, getDate()-1), 101)