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
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments