All Forums Database
Glass 225 posts Joined 04/10
20 Apr 2012
Sql help.

Hi,

I need to have amt stay the same until ind repeats itself as 1, then stay the same until the next ind of 1

 

Input: 

   date              ind       amt
    7/13/2011   1         149754.18
    7/20/2011   0         153102.84
    7/27/2011   0         154831.15
    10/5/2011   1         137612.69
  10/12/2011   0         139036.60
  10/19/2011   0         139561.28
  10/26/2011   0         134208.37

needed output:

    date              ind       amt
    7/13/2011   1         149754.18
    7/20/2011   0         149754.18
    7/27/2011   0         149754.18
    10/5/2011   1         137612.69
  10/12/2011   0         137612.69
  10/19/2011   0         137612.69
  10/26/2011   0         137612.69
 

 

 

 

Angellore 19 posts Joined 06/10
22 Apr 2012
select mT.*, max(case when calendar_date = dt then week_of_year end) over(partition by Dt)
from (
select calendar_date, week_of_year, max(case when day_of_week = 2 then calendar_date end) 
over(order by calendar_date rows unbounded preceding) Dt
from sys_calendar.calendar) mT
order by calendar_date

Use idea from this script.

dnoeth 4628 posts Joined 11/04
22 Apr 2012

You need a kind of dynamic partitioning, which is available in TD13 using RESET WHEN:

SELECT datecol, ind, amt,
 MIN(CASE WHEN ind=1 THEN amt END) 
 OVER (ORDER BY datecol RESET WHEN ind=1 ROWS UNBOUNDED PRECEDING) 
FROM tab

Before TD13 you can do the same using a nested OLAP function:

SELECT datecol, ind,
  MIN(amt) OVER (PARTITION BY dynamic_partition)
FROM 
 (
  SELECT datecol, ind, amt,
    SUM(ind) 
    OVER (ORDER BY datecol ROWS UNBOUNDED PRECEDING) AS dynamic_partition
  FROM tab
 ) AS dt

Both will result in exactly the same Explain.

Dieter

Dieter

ulrich 816 posts Joined 09/09
22 Apr 2012

Dieter,

just a minor comment - in the second SQL is the 

case when ind = 1 then amt end

missing...

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Glass 225 posts Joined 04/10
23 Apr 2012

Perfect, thank you Dieter!

You must sign in to leave a comment.