CODEWITHSUNDEEP
sqlsql-server-2008date
Sabi Tech
Sabi Tech

Reputation: 81

How to show YTD last year vs Same period this year

i have this query that is showing me the same period of days from this month 2016 and the same month 2015,

But i need to show YTD

Ene, feb, mar, apr, may, jun, up to Jul 6 2015 and same period 2016 (because today is the 6th and tomorrow show 7th then 8th july and so on) Meaning that is something similar to the code i have but instead of showing me just the current month i need YTD. 

SELECT        OrderStatus, Sum_SellPrice, Sum_SellerMargin, Sum_BuyPrice, OrderDate
FROM            Sum_OrderCharges
WHERE        (OrderStatus IN ('Completed', 'Invoiced', 'Open')) 
AND (OrderPeriodYear IN ('2015','2016')) 
AND (MONTH(OrderDate) = MONTH(GETDATE())) 
and (day(OrderDate) <= DAY(GETDATE())) 
ORDER BY OrderDate

Upvotes: 1

Views: 2658

Answers (2)

Alex Kudryashev
Alex Kudryashev

Reputation: 9460

It is more elegant to prepare and use date windows using CTE. This is an example.

declare @start as date='2016-01-01', @end date=getdate(),@depth int = 2

;with dates as (--prepare date ranges
--anchor query
select @start startDate, @end endDate, 1 cnt
union all
--recursive query 
select dateadd(year,-1,startDate),dateadd(year,-1,endDate), cnt+1
from dates where cnt < @depth
)
select d.startDate,d.endDate, 
OrderStatus, Sum_SellPrice, Sum_SellerMargin, Sum_BuyPrice --aggregate if necessary
from Sum_OrderCharges o
inner join dates d on o.OrderDate between d.startDate and d.endDate
WHERE (OrderStatus IN ('Completed', 'Invoiced', 'Open'))

This way you can even compare date ranges, for example 2015-11-01 to 2016-07-05 and 2014-11-01 to 2015-07-05.

Upvotes: 0

Aaron Dietz
Aaron Dietz

Reputation: 10277

Add an OR statement to cover for this:

OR MONTH(OrderDate) <= MONTH(GETDATE())

SELECT OrderStatus, Sum_SellPrice, Sum_SellerMargin, Sum_BuyPrice, OrderDate
FROM   Sum_OrderCharges
WHERE  OrderStatus IN ('Completed', 'Invoiced', 'Open') 
AND OrderPeriodYear IN ('2015','2016')
AND ( (MONTH(OrderDate) = MONTH(GETDATE()) AND day(OrderDate) <= DAY(GETDATE()))
    OR MONTH(OrderDate) <= MONTH(GETDATE())  )--Be sure to wrap the entire OR segment with ()
ORDER BY OrderDate

Upvotes: 2

Related Questions