CodeDump
AbleTech AbleTech - 10 months ago 37
SQL Question

How to show data from this month same month last year same period SQL Server 2008

I've been trying to get this work. I want to show the data up to date as the days passes I want to display the same day in the same month last year

Jul 7 2016     

Jul 6 2016    

Jul 5 2016    

Jul 4 2016    

Jul 3 2016     

Jul 2 2016    

Jul 1 2016  



Jul 7 2015     

Jul 6 2015    

Jul 5 2015    

Jul 4 2015    

Jul 3 2015     

Jul 2 2015    

Jul 1 2015


This is my code: right now shows all 2015 records and i want it to show up to date like above.

SELECT        

    OrderStatus, Sum_SellPrice, Sum_SellerMargin, Sum_BuyPrice, 

    OrderPeriodMonthName, OrderDate

FROM

    Sum_OrderCharges

WHERE        

    (OrderStatus IN ('Completed', 'Invoiced', 'Open')) 

    AND (OrderPeriodYear IN ('2016','2015')) 

    AND (MONTH(OrderDate) = MONTH(GETDATE()))

ORDER BY 

    OrderDate

Matt Matt
Answer 
SELECT        OrderStatus, Sum_SellPrice, Sum_SellerMargin, Sum_BuyPrice, OrderPeriodMonthName, OrderDate
FROM            Sum_OrderCharges
WHERE        (OrderStatus IN ('Completed', 'Invoiced', 'Open')) AND (OrderPeriodYear IN ('2016','2015')) AND (MONTH(OrderDate) = MONTH(GETDATE()))

       AND DAY(OrderDate) <= DAY(GETDATE())

ORDER BY OrderDate

note DAY() will return an integer between 1 & 31 not an actual date so you can just use that to compare and further restrict your results to days that have actually occurred.

Share