CODEWITHSUNDEEP
sqlsql-server-2008data-manipulation
Sabi Tech
Sabi Tech

Reputation: 81

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

Upvotes: 1

Views: 1182

Answers (2)

HappyCoding
HappyCoding

Reputation: 661

I believe you would append the following to the where clause: AND (OrderDate <= GETDATE())

Fully it would be:

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

Not required: I would format it something like the following so you could better see what all you have going on in your where clause. (easier to see all your opening and closing parentheses)

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 (OrderDate <= GETDATE()) 
ORDER BY      OrderDate

Upvotes: 0

Matt
Matt

Reputation: 14341

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.

Upvotes: 1

Related Questions