All Forums Database
sanmisc 3 posts Joined 12/12
05 Apr 2016
OLAP function: CSUM / SUM with Reset value

Hi All,
Can I have the teradata query for the below requirement:
Input:

day dt	SRC_CREATE_DT	CURR_VAL
3/1/2016	?	0
3/2/2016	?	0
3/3/2016	?	0
3/4/2016	?	0
3/5/2016	?	0
3/6/2016	?	0
3/7/2016	?	0
3/8/2016	?	0
3/9/2016	?	0
3/10/2016	?	0
3/11/2016	?	0
3/12/2016	?	0
3/13/2016	03/13/2016 22:48:23	366
3/14/2016	?	0
3/15/2016	?	0
3/16/2016	?	0
3/17/2016	?	0
3/18/2016	?	0
3/19/2016	?	0
3/20/2016	03/20/2016 22:47:33	700
3/21/2016	?	0
3/22/2016	?	0
3/23/2016	?	0
3/24/2016	?	0
3/25/2016	03/25/2016 22:47:33	920
3/26/2016	?	0
3/27/2016	?	0
3/28/2016	?	0
3/29/2016	?	0
3/30/2016	03/30/2016 21:01:00	1,021.00
3/31/2016	?	0

 
Output/Expected Result:

day dt	SRC_CREATE_DT	CURR_VAL
3/1/2016	?	0
3/2/2016	?	0
3/3/2016	?	0
3/4/2016	?	0
3/5/2016	?	0
3/6/2016	?	0
3/7/2016	?	0
3/8/2016	?	0
3/9/2016	?	0
3/10/2016	?	0
3/11/2016	?	0
3/12/2016	?	0
3/13/2016	03/13/2016 22:48:23	366
3/14/2016	?	366
3/15/2016	?	366
3/16/2016	?	366
3/17/2016	?	366
3/18/2016	?	366
3/19/2016	?	366
3/20/2016	03/20/2016 22:47:33	700
3/21/2016	?	700
3/22/2016	?	700
3/23/2016	?	700
3/24/2016	?	700
3/25/2016	03/25/2016 22:47:33	920
3/26/2016	?	920
3/27/2016	?	920
3/28/2016	?	920
3/29/2016	?	920
3/30/2016	03/30/2016 21:01:00	1,021.00
3/31/2016	?	1,021.00

Thanks

dnoeth 4628 posts Joined 11/04
05 Apr 2016

If CURR_VALUE is actually always increasing you only need a MAX, which results a single STAT-step.

MAX(NULLIF(CURR_VAL, 0))
OVER (--partition by ...
      ORDER BY day_dt
      ROWS UNBOUNDED PRECEDING)

 
Otherwise it depends on your Teradata release.

Since TD14.10 there's FIRST/LAST_VALUE, single STAT-step, too:

COALESCE(LAST_VALUE(NULLIF(CURR_VAL, 0) IGNORE NULLS)
         OVER (--partition by ...
               ORDER BY day_dt
               ROWS UNBOUNDED PRECEDING)
        ,0)

 
Before you can use RESET WHEN, but this will require two STAT-steps:

MAX(CURR_VAL)
OVER (--partition by ...
      ORDER BY day_dt RESET WHEN CURR_VAL <> 0
      ROWS UNBOUNDED PRECEDING)

 

Dieter

sanmisc 3 posts Joined 12/12
05 Apr 2016

You are awesome Dieter,  3rd one is working for me.

sanmisc 3 posts Joined 12/12
20 Apr 2016

Hi Dieter,
I am able to gererate rows for one ID but not more than one ID. Is it possible to generate the rows as below:
I am using points table and date lookup table in the below query:


SELECT	
DAY_DT,	
MAX(ID)	
OVER (--partition by ...	
      ORDER BY day_dt RESET WHEN POINTS <> 0
      ROWS UNBOUNDED PRECEDING) ID,	
MAX(POINTS)	
OVER (--partition by ...	
      ORDER BY day_dt RESET WHEN POINTS <> 0
      ROWS UNBOUNDED PRECEDING) POINTS
FROM TIME_DAY_LKP 	

LEFT OUTER JOIN	

(SELECT ID,SOURCE_DT,  POINTS FROM POINT_TBLE
 ) DT	
ON CAST(SOURCE_DT AS DATE)=DAY_DT	
WHERE MTH_IDNT=201602	

 

Source table
ID	source_dt	POINTS
12,953	3/24/2016	366
12,953	3/29/2016	541
12,953	3/30/2016	1,021.00
12,953	3/31/2016	1,620.00
13,046	3/2/2016	700
13,046	3/17/2016	720
13,046	3/22/2016	760
13,046	3/24/2016	770

Result I am getting
DAY_DT	ID	POINTS
2/28/2016	?	?
2/29/2016	?	?
3/1/2016	?	?
3/2/2016	13,046	700
3/3/2016	13,046	700
3/4/2016	13,046	700
3/5/2016	13,046	700
3/6/2016	13,046	700
3/7/2016	13,046	700
3/8/2016	13,046	700
3/9/2016	13,046	700
3/10/2016	13,046	700
3/11/2016	13,046	700
3/12/2016	13,046	700
3/13/2016	13,046	700
3/14/2016	13,046	700
3/15/2016	13,046	700
3/16/2016	13,046	700
3/17/2016	13,046	720
3/18/2016	13,046	720
3/19/2016	13,046	720
3/20/2016	13,046	720
3/21/2016	13,046	720
3/22/2016	13,046	760
3/23/2016	13,046	760
3/24/2016	13,046	770
3/24/2016	12,953	366
3/25/2016	12,953	366
3/26/2016	12,953	366
3/27/2016	12,953	366
3/28/2016	12,953	366
3/29/2016	12,953	541
3/30/2016	12,953	1,021.00
3/31/2016	12,953	1,620.00
4/1/2016	12,953	1,620.00
4/2/2016	12,953	1,620.00

Expected results
DAY_DT	ID	POINTS
2/28/2016	?	?
2/29/2016	?	?
3/1/2016	?	?
3/2/2016	13,046	700
3/3/2016	13,046	700
3/4/2016	13,046	700
3/5/2016	13,046	700
3/6/2016	13,046	700
3/7/2016	13,046	700
3/8/2016	13,046	700
3/9/2016	13,046	700
3/10/2016	13,046	700
3/11/2016	13,046	700
3/12/2016	13,046	700
3/13/2016	13,046	700
3/14/2016	13,046	700
3/15/2016	13,046	700
3/16/2016	13,046	700
3/17/2016	13,046	720
3/18/2016	13,046	720
3/19/2016	13,046	720
3/20/2016	13,046	720
3/21/2016	13,046	720
3/22/2016	13,046	760
3/23/2016	13,046	760
3/24/2016	13,046	770
3/24/2016	13,046	770
3/25/2016	13,046	770
3/26/2016	13,046	770
3/27/2016	13,046	770
3/28/2016	13,046	770
3/29/2016	13,046	770
3/30/2016	13,046	770
3/31/2016	13,046	770
4/1/2016	13,046	770
4/2/2016	13,046	770
2/28/2016	?	?
2/29/2016	?	?
3/1/2016	?	?
3/2/2016	?	?
3/3/2016	?	?
3/4/2016	?	?
3/5/2016	?	?
3/6/2016	?	?
3/7/2016	?	?
3/8/2016	?	?
3/9/2016	?	?
3/10/2016	?	?
3/11/2016	?	?
3/12/2016	?	?
3/13/2016	?	?
3/14/2016	?	?
3/15/2016	?	?
3/16/2016	?	?
3/17/2016	?	?
3/18/2016	?	?
3/19/2016	?	?
3/20/2016	?	?
3/21/2016	?	?
3/22/2016	?	?
3/23/2016	?	?
3/24/2016	?	?
3/25/2016	12,953	366
3/26/2016	12,953	366
3/27/2016	12,953	366
3/28/2016	12,953	366
3/29/2016	12,953	541
3/30/2016	12,953	1,021.00
3/31/2016	12,953	1,620.00
4/1/2016	12,953	1,620.00
4/2/2016	12,953	1,620.00

 

dnoeth 4628 posts Joined 11/04
21 Apr 2016

You can't use this approach when you need multiple IDs, the only workaround is
TIME_DAY_LKP CROSS JOIN (SELECT DISTINCT ID FROM POINT_TBLE)
to create all possible combinations of date/id and then LEFT JOIN POINT_TBLE using both date&id, which is not very efficient.
 
A better approach utilizes EXPAND ON.
This is an example:

create table inventory(product varchar(10), inv_date date, quantity int);

insert into inventory('bottle', date '2011-04-01', 10);
insert into inventory('bottle', date '2011-04-06', 50);
insert into inventory('bottle', date '2011-04-07', 40);
insert into inventory('bottle', date '2011-04-13', 30);

insert into inventory('bottle2', date '2011-04-03', 15);
insert into inventory('bottle2', date '2011-04-06', 55);
insert into inventory('bottle2', date '2011-04-12', 35);

-- only for a single product:
SELECT 
  calendar_date,
  MIN(quantity) OVER
     (ORDER BY calendar_date
      RESET WHEN quantity IS NOT NULL
      ROWS UNBOUNDED PRECEDING) AS new_quantity
FROM sys_calendar.calendar AS c LEFT JOIN inventory AS i
ON c.calendar_date = i.inv_date
AND product = 'bottle'
WHERE calendar_date BETWEEN DATE '2011-03-31' AND DATE '2011-04-13'
ORDER BY calendar_date;


-- TD13.10+: easier, more efficient, multiple products possible
SELECT product, BEGIN(expd), quantity
FROM
 (  
    SELECT product, quantity, inv_date,
      MIN(inv_date) 
      OVER (PARTITION BY product ORDER BY inv_date
            ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next_date
    FROM inventory
    WHERE inv_date < DATE '2011-04-14'
    -- AND product = 'bottle'
 ) AS i
EXPAND ON PERIOD(inv_date,COALESCE(next_date,DATE '2011-04-14')) AS expd BY INTERVAL '1' DAY
FOR PERIOD(DATE '2011-03-31', DATE '2011-04-14')
ORDER BY 1,2;

 

Dieter

You must sign in to leave a comment.