Hi guys, first time asking for help here! I have a doubt about how to properly case flag some items in my query. I have a table with data starting from 08/2019, like the following:
Store Group |Store Owner| Store | Date
GA | AA | A |06/2019
GA | AA | A |07/2019
GA | AA | B |06/2019
GA | AA | B |07/2019
I then needed to flag when a new store was added, disregarding month 8. So I managed to create the following code, which can flag when a store was added:
SELECT "STORE GROUP", "STORE OWNER", "STORE", TO_CHAR(TO_DATE("DATE"), 'MM/YYYY') "DATE","FLAG" FROM
(
SELECT
a."STORE GROUP", a. "STORE OWNER", a."STORE" , CONCAT (min(a."datemonth" ), '01') "DATE",
CASE
WHEN a."datemonth" = '201908'
THEN 'INITIAL'
ELSE 'STORE ADDED'
END
AS "FLAG"
FROM
"TABLE" as a
INNER JOIN
(SELECT
"STORE GROUP", "STORE OWNER", "STORE", "datemonth"
FROM "TABLE") as b
on b."STORE" = a."STORE" and b."STORE OWNER" = a."STORE OWNER" and a."datemonth"!= b."datemonth"
group by a."STORE GROUP",a."STORE OWNER", a."STORE", a."datemonth"
)
Now the problem is, as the months goes by, a new STORE OWNER, with many different STORES, can be added to this table. I then needed to flag these new entries from this STORE OWNER as “INITIAL” on the first month in which they appeared. And after that, if a new store is added to this new STORE GROUP, flag then as “STORE ADDED”.
How could I do that?