SQL - Check if an item was available in the previous month and then flag it

Question

I'm trying to solve a problem in SQL but without much success so far. I have a table like this:

OWNER|STORE|DATE
  A  | MIX |01/01/2019
  A  | BIX |01/01/2019
  A  | BIX |02/01/2019
  B  | CIX |01/01/2019
  B  | CIX |02/01/2019

It's a table showing information about owners and their stores. An owner can have a store during a month, but this store could be gone in the next month. Or, their store could be present in january, but gone in february.

I wanted to find a way to flag this store movement, so if a store is present in january, and gone in february, I would flag a colum as "gone". And if a store was not present in january, but appeared in february, I would flag it as "new".

Can anyone help me with that? Thanks!

just create a `subquery` to match previous month data – RoMEoMusTDiE Dec 20 '19 at 00:33 — RoMEoMusTDiE, Dec 20 '19 at 00:33

score 1 · Answer 1 · answered Dec 20 '19 at 01:34

Use lag() and lead():

select t.*,
       (case when prev_date < add_months(date, -1) or
                  prev_date is null
             then 'new'
             when next_date > add_months(date, 1) or
                  next_date is null
             then 'gone'
        end) as flag
from (select t.*,
             lag(date) over (partition by owner, store order by date) as prev_date,
             lead(date) over (partition by owner, store order by date) as lead_date
      from t
     ) t;

score 0 · Answer 2 · answered Dec 20 '19 at 00:49

0

SELECT 
    ID, 
    OWNER, 
    STORE, 
    DATE, 
    CASE WHEN DATE IN (SELECT DATE FROM TableName
WHERE DATE IN (<InsertDatesHere)) THEN 'NEW' ELSE 'Gone' END AS Flag

answered Dec 20 '19 at 00:49

Christian Townsend

171
1
16

Note that I just added the ID column as a habit. To me, all tables should possess a unique ID column. – Christian Townsend Dec 20 '19 at 00:50

Mohammad · Accepted Answer · 2019-12-20T17:57:43.733

0

select d.store ,d.owner , d.timedate , 'new' flag from (
  SELECT
 a.store, count(a.store) as flag
FROM
  store as a
  left join  store as b
  on a.store=b.store
group by a.store
having(count(a.store)<2)) as c

  inner join store as d
  on c.store=d.store

union all
(SELECT
 a.store , a.owner, max(a.timedate ), 'gone' as [flag]
FROM
  store as a
  inner  join
  (SELECT
 owner,store,timedate

FROM store) as b
on b.store = a.store and a.timedate!=b.timedate
group by a.store , a.owner)

sqlfiddle here

edited Dec 20 '19 at 17:57

answered Dec 20 '19 at 00:51

Mohammad

1,549
1
15
27

Hey Mohammad, thanks, your solution really works, but I only get a flag for the stores that are gone. How could I get a flag for new stores? That were not present in previous months. – Renato Dec 20 '19 at 13:44
@Renato do you want me update my answer or you already got your answer? :) – Mohammad Dec 20 '19 at 14:26
@Renato i updated my answer , check the fiddle , i will grateful upvote or accept my answer if it satisfy your needs – Mohammad Dec 20 '19 at 17:58

SQL - Check if an item was available in the previous month and then flag it

3 Answers3