Archives of the TeradataForum

Message Posted: Wed, 02 Dec 2009 @ 12:14:40 GMT

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

Thanks to ulrich arndt and Dieter for their replies to my question.

One thing I forgot to mention, Dieter, is that our Financial Calendar doesn't always have 7 days in a week (probably some sort of bean counting consideration), so the more simplistic solution probably won't work so, sorry, I should have mentioned that part. We've spent a lot of mental energy playing with this sort of construct but to no avail. But, as usual Dieter, thanks.

I think, with the varying nature of our data, the more sophisticated/complex solution, Ulrich, is the one we're going for !

     SELECT DTE_DT, DTE_YEAR_NR, DTE_WEEK_NR_FIN
      ,ROW_NUMBER() OVER (ORDER BY DTE_DT ASC) AS DAY_NR , SUM(WEEK_CHANGE_ID) OVER (ORDER BY
     DTE_DT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) +1 AS RUNNING_WEEK_nr

     FROM
     (
     SELECT
     SUM(TRIM(DTE_YEAR_NR)||TRIM(DTE_WEEK_NR_FIN)) OVER (ORDER BY DTE_DT ROWS BETWEEN 1
     PRECEDING AND 1 PRECEDING) as sumTo1Preceding
     ,SUM(TRIM(DTE_YEAR_NR)||TRIM(DTE_WEEK_NR_FIN)) OVER (ORDER BY DTE_DT ROWS BETWEEN CURRENT
     ROW AND CURRENT ROW) as sumToCurrent , CASE WHEN
       SUM(TRIM(DTE_YEAR_NR)||TRIM(DTE_WEEK_NR_FIN)) OVER (ORDER BY DTE_DT ROWS BETWEEN 1
     PRECEDING AND 1 PRECEDING)  <>
       SUM(TRIM(DTE_YEAR_NR)||TRIM(DTE_WEEK_NR_FIN)) OVER (ORDER BY DTE_DT ROWS BETWEEN
     CURRENT ROW AND CURRENT ROW)
       THEN 1 ELSE 0 END AS WEEK_CHANGE_ID
     ,  A.*
     FROM DEVCE_V.KPNDATE_V01 A
     -- ORDER BY DTE_DT
     )
      AS T
      ORDER BY DTE_DT;

Actually, I'm a bit disappointed with myself for not getting even close, but I have to hand it to you, brilliant ! As you'll see, I had to break out the individual components of your solution, just to be able to understand it, which I think I do now.

Please help yourself to 3 virtual beers on me. If you wonder past TNT in Atherstone anytime, I'll materialize them for you!

Thanks again.

Regards

David Clough
Database Developer
Database Design Group

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