SQL statement wanted

Database
Enthusiast

SQL statement wanted

Hi,

 

I'm trying to write an SQL statement based on this example data (just the first 3 columns)

 

OUTLET_ID DATE_ID      QUALITY_FLAG Count

799              01.06.2014   1            

799              02.06.2014   1

799              03.06.2014   1  

799              04.06.2014   1

799              05.06.2014   1

799              06.06.2014   1

799              07.06.2014   1

799              08.06.2014   1

799              09.06.2014   1

799              10.06.2014   1                            10

799              11.06.2014   0

799              12.06.2014   0

799              13.06.2014   0                              3

799              14.06.2014   1

799              15.06.2014   1

799              16.06.2014   1

799              17.06.2014   1

799              18.06.2014   1

799              19.06.2014   1                              6

My target is to get the values from the Count column.

From June 1st until June 10th the QUALTITY_FLAG is 1 -> this is 10 days in row,

from 11th until 13th the flag is 0 -> this is 3 days in row.

from 14th until 19th the flag is 1 -> this is 6 days in row.

 

Result should be something like

 

OUTLET_ID QUALITY_FLAG Count

799               1                          10

799               0                            3

799               1                            6

 

I tried the stuff with OLAP functions like COUNT(...) OVER (PARTITON BY …. ROWS BETWEEN…) but this was not the answer.

 

Answers, advices and other help is very appreciated :-)

 

Thanks in advance

Helmut

3 REPLIES
Senior Supporter

Re: SQL statement wanted

Try the following:

create table t1
(OUTLET_ID integer, DATE_ID date, QUALITY_FLAG integer
) no primary index ;

insert into t1 values (1,date-5,1);
insert into t1 values (1,date-4,1);
insert into t1 values (1,date-3,0);
insert into t1 values (1,date-2,0);
insert into t1 values (1,date-1,0);
insert into t1 values (1,date,0);
insert into t1 values (1,date+1,1);

WITH cte(
OUTLET_ID
,QUALITY_FLAG
,valid_pd
) AS
(
SELECT
OUTLET_ID
,QUALITY_FLAG
,PERIOD(DATE_ID , DATE_ID +1) as valid_pd
FROM T1
)
SELECT *
FROM TABLE
( TD_SYSFNLIB.TD_NORMALIZE_OVERLAP_MEET(
NEW VARIANT_TYPE(
cte.OUTLET_ID
,cte.QUALITY_FLAG
)
, cte.valid_pd
)
RETURNS (
OUTLET_ID integer,
QUALITY_FLAG integer,
valid_pd PERIOD(DATE),
cnt INT
)
HASH BY OUTLET_ID
,QUALITY_FLAG

LOCAL ORDER BY
OUTLET_ID
,QUALITY_FLAG
,valid_pd
) AS dt
ORDER BY OUTLET_ID
,valid_pd
;

If you don't want the period in the result set just detail you select * 

Ulrich

Junior Contributor

Re: SQL statement wanted

Hi Helmut,

is there a row for every date?

What if a date is missing?

You need to nest OLAP functions:

SELECT
OUTLET_ID
,QUALITY_FLAG
,COUNT(*)
,MIN(DATE_ID)
,MAX(DATE_ID)
FROM
(
SELECT
OUTLET_ID
,DATE_ID
,QUALITY_FLAG
,SUM(flag) -- create a group number
OVER (PARTITION BY OUTLET_ID
ORDER BY DATE_ID
ROWS UNBOUNDED PRECEDING) AS dummy
FROM
(
SELECT
OUTLET_ID
,DATE_ID
,QUALITY_FLAG
-- create a flag whenever QUALITY_FLAG changes
,CASE WHEN MIN(QUALITY_FLAG)
OVER (PARTITION BY OUTLET_ID
ORDER BY DATE_ID
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
) <> QUALITY_FLAG
THEN 1
ELSE 0
END AS flag
FROM vt
) AS dt
) AS dt
GROUP BY OUTLET_ID, QUALITY_FLAG, dummy

If there are no missing dates, you could also utilize TD_NORMALIZE_MEET:

WITH cte (OUTLET_ID, QUALITY_FLAG, pd) AS
(
SELECT
OUTLET_ID
,QUALITY_FLAG
,period(DATE_ID ,DATE_ID + 1) AS pd
FROM vt
)
SELECT
OUTLET_ID
,QUALITY_FLAG
,cnt
,BEGIN(pd)
,LAST(pd)
FROM
TABLE (TD_SYSFNLIB.TD_NORMALIZE_MEET
(NEW VARIANT_TYPE(cte.OUTLET_ID, cte.QUALITY_FLAG)
,cte.pd)
RETURNS (OUTLET_ID INT
,QUALITY_FLAG INT
,pd PERIOD(DATE)
,cnt INTEGER)
HASH BY OUTLET_ID, QUALITY_FLAG
LOCAL ORDER BY OUTLET_ID, QUALITY_FLAG, pd
) dt
ORDER BY 1,2
;
Enthusiast

Re: SQL statement wanted

Hi Dieter/Ulrich,

there's no missing dates.

Thanks a lot for the quick and great responses!!! This is a great start in a sunny weekend :-)

Best regards

Helmut