Archives of the TeradataForum

Message Posted: Wed, 02 Dec 2009 @ 20:56:02 GMT


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

Subj:		Re: Incrementing count within a group of records

From:		Dieter Noeth

Hi David,

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.

I wondered if it was really that simple :-)

How is that financial week calculated? You could use the same rule set within your query.

But a more interesting question is: Is this a one-time query to populate a new column in a calendar table or will it be a calculation in a view? Hopefully not.

Of course the existing query might be cleaned a bit:

     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 UNBOUNDED PRECEDING) AS RUNNING_WEEK_nr
     FROM
     (
        SELECT
          CASE
            WHEN ROW_NUMBER ()
                 OVER (PARTITION BY DTE_YEAR_NR, DTE_WEEK_NR_FIN
                       ORDER BY dte_dt) = 1
            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;

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

As long as it's not that traditional lukewarm english beer :-)

Dieter


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

Attachments

Library

Quick Reference