Home Page for the TeradataForum
 

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023