CODEWITHSUNDEEP
sql
Eric Wu
Eric Wu

Reputation: 5

Get the product_id sold on date1 but not date2 in the same table

How can I get the product_id sold on date1 but not date2 in the same table? I found this question online, but couldn't figure out the right solution. The minus function also doesn't work in my SQL Server 2012.

Sample data:

ORDER_DAY         ORDER_ID            PRODUCT_ID   QUANTITY     PRICE
------------------ -------------------- ---------- ---------- ----------
01-JUL-11         O1                  P1                 5          5  
01-JUL-11         O2                  P2                 2         10  
01-JUL-11         O3                  P3                10         25  
02-JUL-11         O5                  P3                 5         25  
02-JUL-11         O6                  P4                 6         20  
02-JUL-11         O7                  P1                 2          5

Return product_id that was ordered on 02-Jul-11 but not on 01-Jul-11

the desired output should be:
P4

Upvotes: 0

Views: 77

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

I would use aggregation and having:

select t.product_id
from t
group by t.product_id
having sum(case when t.order_date = '2011-07-01' then 1 else 0 end) = 0 and
       sum(case when t.order_date = '2011-07-02' then 1 else 0 end) > 0;

For just two dates, you can simplify this to:

select t.product_id
from t
where t.order_date in ('2011-07-01', '2011-07-02')
group by t.product_id
having min(t.order_date) = '2011-07-02';

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

Get unique sales of product from 1st of July and remove those which also have a sale on 2nd of July using not exists:

select distinct t1.product_id
from yourtable t1
where t1.order_day = '2011-07-02'
and not exists (
  select 1
  from yourtable t2
  where t1.product_id = t2.product_id
    and t2.order_day = '2011-07-01'
  )

If one product can't be sold more than once within a particular date (one day) you can omit distinct part because you'd have unique results anyway and this would avoid sorting and eliminating duplicates.

Upvotes: 1

Related Questions