All Forums Database
markus 2 posts Joined 03/06
14 Jan 2015
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 

Rohan_Sawant 55 posts Joined 07/14
14 Jan 2015

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

dnoeth 4628 posts Joined 11/04
15 Jan 2015

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

 

Dieter

markus 2 posts Joined 03/06
15 Jan 2015

Thanks Rohan and Dieter.
 
Dieter you are right, I am flagging the gaps in SCD.
 
Thanks,
Markus

dnoeth 4628 posts Joined 11/04
15 Jan 2015

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

 

Dieter

You must sign in to leave a comment.