Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Incrementing count within a group of records
 
From:   David Clough

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