SQL - Compare data from one table

Question

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

@ADyson It's clear enough to me ;-) – Strawberry Aug 21 '17 at 10:05 — Strawberry, Aug 21 '17 at 10:05

Strawberry · Accepted Answer · 2017-08-21T10:36:53.823

1

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.

edited Aug 21 '17 at 10:36

answered Aug 21 '17 at 10:22

Strawberry

33,750
13
40
57

Thank you a lot! I will try to write it on my own and understand it. I have added also a line in which I can choose exact ID that I would like to check. – michal2805 Aug 21 '17 at 12:01
@michal2805 Note that there's actually a mechanism for expressing thanks on SO. Just sayin'. – Strawberry Aug 24 '17 at 14:11

score 1 · Answer 2 · answered Aug 21 '17 at 10:45

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)
                 );

score 0 · Answer 3 · answered Aug 21 '17 at 10:07

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
)

Thank you for reply, I will try to review this on my own. 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). — michal2805, Aug 21 '17 at 11:20

SQL - Compare data from one table

3 Answers3