Link to home
Start Free TrialLog in
Avatar of notasgoodasyou
notasgoodasyouFlag for Afghanistan

asked on

SQL get year to date for last year

I am currently using this to get a year to date sales number.  I have this snip working but I am trying to get the same info for last year.

  SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) THEN INV1.Quantity*INV1.Price END)AS YTD$

I would like to generate a LYTD$ field calculating the same info but year to date for the same time period last year.  In this case it would be 1/1/14-6/2/14.  I want this to be dynamic as is above.
ASKER CERTIFIED SOLUTION
Avatar of dsacker
dsacker
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dsacker
dsacker
Flag of United States of America image
Also, you may wish to have an ELSE in both CASE statements saying: ELSE 0.

That way you won't be summing NULLs in your aggregate and get that SQL nastygram. :) :)

SUM(CASE
        WHEN YEAR(OINV.DocDate) = YEAR(getdate()) THEN INV1.Quantity*INV1.Price
        ELSE 0
    END) AS YTD$,
SUM(CASE
        WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 AND OINV.DocDate <= DATEADD(year, -1, GETDATE()) THEN INV1.Quantity*INV1.Price
        ELSE 0
    END) AS LYTD$

Open in new window

Avatar of notasgoodasyou
notasgoodasyou
Flag of Afghanistan image

ASKER

Thanks worked great