Sql help.

Database
Enthusiast

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

4 REPLIES
Enthusiast

Re: Sql help.

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.

Junior Contributor

Re: Sql help.

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

Supporter

Re: Sql help.

Dieter,

just a minor comment - in the second SQL is the 

case when ind = 1 then amt end

missing...

Enthusiast

Re: Sql help.

Perfect, thank you Dieter!