AbleTech AbleTech - 5 months ago 7
SQL Question

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

Hi guys this has been a headache.

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

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.