How to flag products based on start ts and end ts

Database
Fan

How to flag products based on start ts and end ts

Hi All,

Here is my scenario

product_ID  start_ts              end_ts                flag

123             01/01/2013       01/02/2013

123             01/03/2013       01/05/2013        Y

123             01/06/2013       01/10/2014        Y

123             01/11/2014       01/20/2055        Y

124             01/01/2013       01/05/2013       

124             01/06/2013       01/10/2013        Y

124             01/13/2013       01/10/2055        N     (Start_ts ia not after previous record end_ts then flag "N")

Could some help me to write the SQL for above scenario.

Thanks,

Markus 

4 REPLIES
Enthusiast

Re: How to flag products based on start ts and end ts

Hi Markus,

The records set you provided doesnt have the scenario of  ---> Start_ts ia not after previous record end_ts then flag "N" according to my understanding. So I added the one extra insert to create the scenario. Please refer the below sql:

/*Started creating test data*/
CREATE MULTISET VOLATILE TABLE VT_TEST_DATA ,NO FALLBACK ,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
NO LOG
(
PRODUCT_ID INTEGER,
START_TS DATE,
END_TS DATE
)
PRIMARY INDEX (PRODUCT_ID)
ON COMMIT PRESERVE ROWS;

INSERT INTO VT_TEST_DATA VALUES (123,'2013-01-01','2013-01-02');
INSERT INTO VT_TEST_DATA VALUES (123,'2013-01-03','2013-01-05');
INSERT INTO VT_TEST_DATA VALUES (123,'2013-01-06','2013-01-10');
INSERT INTO VT_TEST_DATA VALUES (123,'2013-01-11','2013-01-20');
INSERT INTO VT_TEST_DATA VALUES (124,'2013-01-01','2013-01-05');
INSERT INTO VT_TEST_DATA VALUES (124,'2013-01-06','2013-01-10');
INSERT INTO VT_TEST_DATA VALUES (124,'2013-01-13','2055-01-10');
/*Added to check scenario of "Start_ts ia not after previous record end_ts then flag 'N' " */
INSERT INTO VT_TEST_DATA VALUES (124,'2013-01-16','2055-01-11');
/*Completed creation of test data*/

/* Your output */
SELECT
PRODUCT_ID
, START_TS
, END_TS
, CASE
WHEN START_TS > MIN(END_TS) OVER (PARTITION BY PRODUCT_ID ORDER BY END_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
THEN 'Y'
WHEN MIN(END_TS) OVER (PARTITION BY PRODUCT_ID ORDER BY END_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) IS NULL
THEN ''
ELSE 'N'
END AS STATUS
FROM
VT_TEST_DATA;

I am not quite clear with your requirement.

Please let me know if the above sql satisfies your scenario.

If not then please help me understanding the same.

Thanks,

Rohan Sawant

Junior Contributor

Re: How to flag products based on start ts and end ts

Hi Rohan,

Markus has probably a kind of slowly changing dimension and is trying to flag gaps.

So when the start_ts is exactly one day after the previous end_ts it's 'Y' else 'N', and then the provided data matches:

, CASE 
WHEN MIN(END_TS) OVER (PARTITION BY PRODUCT_ID ORDER BY END_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) = START_TS - 1
THEN 'Y'
WHEN MIN(END_TS) OVER (PARTITION BY PRODUCT_ID ORDER BY END_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) IS NULL
THEN ''
ELSE 'N'
END AS STATUS
Fan

Re: How to flag products based on start ts and end ts

Thanks Rohan and Dieter.

Dieter you are right, I am flagging the gaps in SCD.

Thanks,

Markus

Junior Contributor

Re: How to flag products based on start ts and end ts

Hi Markus,

if it's just for flagging the gaps you can simplify it by treating the first row as correct:

, CASE 
WHEN MIN(END_TS) OVER (PARTITION BY PRODUCT_ID ORDER BY END_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) < START_TS - 1
THEN 'N'
ELSE 'Y'
END AS STATUS