CODEWITHSUNDEEP
mysqlsql
michal2805
michal2805

Reputation: 21

SQL - Compare data from one table

I am having trouble finding a way to compare data from one table (Table1).

Part of the Table1

Date        ID        Item        
----        -------   -----
2017-06-30  90        2200
2017-06-30  150       1200
2017-06-30  150       1201
2017-06-30  150       1202 
2017-06-30  150       1203 
2017-06-30  150       1204
2017-07-31  150       1201
2017-07-31  150       1202
2017-07-31  150       1203
2017-07-31  150       1204
2017-07-31  150       1205
2017-07-31  90        2200

The result I would like to get is 1205 as this is a new item in following month. It would be also nice if I could get item that would not be anymore in the following month, ie 1200

** EDITED: The one thing I should mention is that Table1 has also different IDs in ID Column. So the main goal is to compare exact ID=150 (not 160 or 180). **

I will be grateful for any advice.

Thank you

Upvotes: 1

Views: 105

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you want both "new" items in one month and "deleted" items:

select 'new', t.*
from t
where not exists (select 1
                  from t t2
                  where t2.item = t.item and
                        year(t2.date) = year(t.date - interval 1 month) and
                        month(t2.date) = month(t.date - interval 1 month)
                 )
union all
select 'deleted', t.*
from t
where not exists (select 1
                  from t t2
                  where t2.item = t.item and
                        year(t2.date) = year(t.date + interval 1 month) and
                        month(t2.date) = month(t.date + interval 1 month)
                 );

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

E.g.:

SELECT x.* 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.id = x.id 
   AND y.date = '2017-06-30' 
   AND y.item = x.item 
 WHERE x.date = '2017-07-31' 
   AND y.id IS NULL;

or

SELECT x.* 
  FROM my_table x 
  LEFT 
  JOIN my_table y  
    ON y.id = x.id AND y.date = x.date - INTERVAL 1 MONTH 
   AND y.item = x.item 
 WHERE x.date = '2017-07-31' 
   AND y.id IS NULL;

I would have left the remaining part as an exercise for the reader, but I see my plan has been scuppered.

Upvotes: 1

JohnHC
JohnHC

Reputation: 11195

To select items that were not included in previous months or were retired in previous months...

select 'new item' as result_type, item
from MyTable a1
where not exists
(
select 1
from MyTable a2
where a1.item = a2.item
and a2.Date < a1.date -- change this to a date function to compare to previous month only
)
union all
select 'retired item' as result_type, item
from MyTable a1
where not exists
(
select 1
from MyTable a2
where a1.item = a2.item
and a2.Date > a1.date -- change this to a date function to compare to previous month only
)

Upvotes: 0

Related Questions