from table with daily log to table with from-to dates

Database
Highlighted
Fan

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

4 REPLIES
Junior Contributor

Re: from table with daily log to table with from-to dates

Hi Youri,

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

Fan

Re: from table with daily log to table with from-to dates

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

Junior Contributor

Re: from table with daily log to table with from-to dates

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)

Enthusiast

Re: from table with daily log to table with from-to dates

Better use a qualifier here