Archives of the TeradataForum

Message Posted: Tue, 01 Dec 2009 @ 19:34:11 GMT

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

Can anyone help me with some Sql, I wonder.

Here's what I got so far ....

     28/12/1997        1        1998        1
     29/12/1997        2        1998        1
     30/12/1997        3        1998        1
     31/12/1997        4        1998        1
     01/01/1998        5        1998        1
     02/01/1998        6        1998        1
     03/01/1998        7        1998        1
     04/01/1998        8        1998        2
     05/01/1998        9        1998        2
     06/01/1998        10        1998        2
     07/01/1998        11        1998        2
     08/01/1998        12        1998        2
     09/01/1998        13        1998        2
     10/01/1998        14        1998        2
     11/01/1998        15        1998        3
     12/01/1998        16        1998        3
     13/01/1998        17        1998        3
     14/01/1998        18        1998        3
     15/01/1998        19        1998        3
     16/01/1998        20        1998        3
     17/01/1998        21        1998        3

     Column 1 : date
     Column 2 : generated nicely and easily
     Column 3 : Year
     Column 4: week within the Year
     Column 5 : (not shown) ... see text below

The second column is simply generated (using ROW_NUMBER() OVER (ORDER BY DTE_DT ASC) AS DAY_NR), which will continue to increment for all days in the calendar, and continue to increment after 1998 - as required.

Column four (which is week number), however, I want to use to provide another column (column 5) incrementing forever, but not resetting at the start of the new year.

So, basically, I want to have a count which only changes by 1, on change of that fourth column, ordering by Year. Within 1998 it would actually look the same as column four but when we go to 1999, that column would continue to increment, but again only when the week number changes.

I can do this in procedural code, but I suspect it can't be done in SQL.

Don't knock yourself out over this one ... I've already done that ! I guess you either know how to do it or you don't.

Thanks and Regards

David Clough
Database Developer
Database Design Group

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