CODEWITHSUNDEEP
sql-server
SQLnoob
SQLnoob

Reputation: 77

CustomerID and Order Dates List From Current Month and Previous Month

I'm having trouble working on this query. I have a table that store customer id and Purchase as follow :

CustomerID  PurchaseDate
------------------------
1              09/10/2018
1              08/15/2018
2              09/05/2018
2              09/07/2018
3              09/02/2018
4              08/20/2018

I'm trying to create a monthly report where that shows customerID and purchasedate for the current month and also show the purchasedate from the previous if they purchase something.So something like this: 

select CustomerId, PurchaseDate 
From Orders
Where PurchaseDate between '09/01/18' and '09/30/18' --- can't be changed
or PurchaseDate between '08/01/18' and '08/31/18 --------- wouldn't work

because this will include list of customer that bought purchased and item for the previous month even if they didn't purchase anything for the current month. I could use temp tables to store customerID and purchasedate for each month and then join them together but I'm trying to do it without using temps. This is the result I'm trying to get.

CustomerID  PurchaseDate
------------------------
1              09/10/2018
1              08/15/2018 ------ purchased previous month 
2              09/05/2018
2              09/07/2018
3              09/02/2018

Any suggestion? I will gladly appreciated it . Thanks!

Upvotes: 1

Views: 47

Answers (2)

error_unknown
error_unknown

Reputation: 30

your code should look like this:

select Orders.CustomerId, Orders.PurchaseDate, c.PurchaseDate as previous_month
From Orders, (select CustomerId, PurchaseDate from Orders where PurchaseDate 
    between '08/01/18' and '08/31/18' ) as c
Where Orders.PurchaseDate between '09/01/18' and '09/30/18'
and Orders.CustomerId = c.CustomerId

This is called a subquery and can be slow if you let it get out of hand.

Upvotes: 0

sgeddes
sgeddes

Reputation: 62851

Here's one option using exists:

select * 
from orders o
where exists (
    select 1
    from orders o2 
    where o.customerid = o2.customerid
        and o2.PurchaseDate between '09/01/18' and '09/30/18'
    ) and o.PurchaseDate between '08/01/18' and '09/30/18'

Upvotes: 1

Related Questions