All Forums Database
HelmutR 6 posts Joined 01/13
17 Jul 2014
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

ulrich 816 posts Joined 09/09
18 Jul 2014

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

dnoeth 4628 posts Joined 11/04
18 Jul 2014

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
; 

 

Dieter

HelmutR 6 posts Joined 01/13
18 Jul 2014

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
 

You must sign in to leave a comment.