All Forums Database
Youri 2 posts Joined 04/16
06 Apr 2016
from table with daily log to table with from-to dates

Hello
I'm trying to figure out how I can from a table with daily logs to a table where I have a from and to date
ex (data set in bold is just to make the groupings more readable):

cust   | product | logdate

-----------------------------------

1       | X          | 15/01/2016

1       | X          | 16/01/2016

1       | X          | 17/01/2016

1       | X          | 18/01/2016

1       | Y           | 18/01/2016

1       | Y           | 19/01/2016

1       | Y           | 20/01/2016

1       | X          | 25/01/2016

1       | X          | 26/01/2016

1       | X          | 27/01/2016

2       | X           | 22/01/2016

2       | X           | 23/01/2016

2       | X           | 24/01/2016

2       | X           | 25/01/2016

 

what I would like to get is the following:

 

cust   | product | start_date    | end_date

----------------------------------------------------

1       | X          | 15/01/2016 | 18/01/2016

1       | Y          | 18/01/2016 | 20/01/2016

1       | X          | 25/01/2016 | 27/01/2016

2       | X          | 22/01/2016 | 25/01/2016

 

There is probably an easy solution for this, but I can't seem to find it.

 

thanks in advance

 

Youri

dnoeth 4628 posts Joined 11/04
06 Apr 2016

Hi Youri,
what if there are gaps, e.g. no row for 17/01/2016, should this result in one or two rows?

Dieter

Youri 2 posts Joined 04/16
06 Apr 2016

Hello Dieter
A gap should indeed result into 2 records
The goal is to have, for each combo cust / product the min and max date - from and upto the next gap
 
In case 17/01/2016 should not exists for the fist bold part that would would have to result in:
1 | X | 15/01/2016 | 16/01/2016
1 | X | 18/01/2016 | 18/01/2016
 

dnoeth 4628 posts Joined 11/04
06 Apr 2016

If you don't have to return additional columns besides cust/prod/date:
TD14.10

SELECT cust, product, BEGIN(pd), END(pd)
FROM 
 ( 
   SELECT NORMALIZE
      cust, product,
      PERIOD(logdate, logdate + 1) AS pd
   FROM tab
 ) AS dt

 
pre-TD14.10

WITH cte AS
 (
   SELECT
      cust, product,
      PERIOD(logdate, logdate + 1) AS pd
   FROM tab
 ) 
SELECT cust, product, BEGIN(pd), END(pd)
FROM
   TABLE(TD_NORMALIZE_OVERLAP_MEET(NEW VARIANT_TYPE(cte.cust,cte.product), cte.pd)
        RETURNS (cust INT, product CHAR, pd PERIOD(DATE))
   HASH BY cust, product
   LOCAL ORDER BY cust, product, pd) AS dt

 
If you need other columns you might put those queries in a Derived Table and join back to the base table or use some nested OLAP-functions (which is more efficient depends on the actual data and if there are additonal OLAP-functions needed for calculating those other functions)

Dieter

07 Apr 2016

Better use a qualifier here

You must sign in to leave a comment.