Archives of the TeradataForum

Message Posted: Wed, 06 Sep 2006 @ 09:44:24 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I would try this approach (pseudocode :-)) - if you have enough spoolspace :-)...

Ulrich

SQL Pseudocode:

     /* set open end - might need adoption in case last period can also be
     closed... */
     select thekey,
            thevalue,
            start_date,
            case when rowperiod = maxrpwperiod then null else end_date end as
     end_date
     from
     (
     /* get the new values for the start and end date */
     select thekey,
            thevalue,
            rowperiod,
            max(rowperiod) over (partition by thekey) as maxrowperiod,
            min(thedate) over (partition by thekey, thevalue, rowperiod) as
     start_date,
            max(thedate) over (partition by thekey, thevalue, rowperiod) as
     end_date
     from
     (
     /* counts in key and key/value combination are generating in diffs
     periods */
     select thekey,
            thevalue,
            thedate,
            sum(1) over (partition by thekey
                         order by thedate
                         rows between unbounded preceding and current row
                        ) as rowinkey,
            sum(1) over (partition by thekey,thevalue
                         order by thedate
                         rows between unbounded preceding and current row
                        ) as rowinkeyvalue,
            rowinkey - rowinkeyvalue as rowperiod
     from
     (
     /* one row per date */
     select f.thekey,
            f.thevalue,
            c.calendar_date as thedate
     from fact_tab f
          join
          sys_calendar.calendar c
             on c.calendar_date between f.start_date and f.end_date
                or (f.end_date is null and c.calendar_date = f.start_date)
     ) as t1
     ) as t2
     qualify start_date = thedate
     ) as t3
     order by 1,2,3

	<Prev	Next>		<<First	<Prev	Next>	Last>>