Hi Youri,
what if there are gaps, e.g. no row for 17/01/2016, should this result in one or two rows?
Dieter
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
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
Better use a qualifier here
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