June 16, 2008 at 12:53 am
G'day all!
I recently just had a question answered on these forums (looking back in time) and this is part of the next step.
I need to create a column that holds an identifier as to what week grouping a particular day belongs to. For the moment I am calling it "sequence".
ie:
kcckey daytoday semester term week sequence
2008-06-16 Monday 1 2 10 52
continuing on with the sequence increasing every time a new week is reached starting on Monday. ie: Monday 23rd and days all the way to Sunday of that week would have sequence 53 and so on.
How can I write some TSQL that will write 7 records and then increment the number by 1 and then write it to the next 7 records, increment the number by 1 and write it to the next 7 records and so on until all records are labeled?
I don't have any previous experience with cursors (other than try not to use them I hear!) or while loops and would greatly appreciate a steering in the right direction!
Thanks! - Damien 🙂
June 16, 2008 at 3:18 pm
You don't need sequence.
You need number of weeks (7 day groups) from some initial date.
UPDATE KCC
SET sequence = DATEDIFF(dd, @InitDate, kcc_date)/7
WHERE {if you need some}
_____________
Code for TallyGenerator
June 16, 2008 at 4:09 pm
Supplement Sergiy's great suggestion with something like a Tally Table, and you could write years worth of these at a time.
A good starting point would be here:
http://www.sqlservercentral.com/articles/TSQL/62867/[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 16, 2008 at 5:10 pm
Matt Miller (6/16/2008)
Supplement Sergiy's great suggestion with something like a Tally Table, and you could write years worth of these at a time.
His KCC table is actually Calendar table, kind of "Tally for dates".
So, he's on right track.
🙂
The task was to set right sequential numbers for weeks.
_____________
Code for TallyGenerator
June 16, 2008 at 5:16 pm
Sergiy (6/16/2008)
Matt Miller (6/16/2008)
Supplement Sergiy's great suggestion with something like a Tally Table, and you could write years worth of these at a time.His KCC table is actually Calendar table, kind of "Tally for dates".
So, he's on right track.
🙂
The task was to set right sequential numbers for weeks.
Good eye - didn't gather that the first time reading through!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 16, 2008 at 5:34 pm
Matt Miller (6/16/2008)
Good eye - didn't gather that the first time reading through!
It's a follow-up for this:
http://www.sqlservercentral.com/Forums/Topic517329-8-1.aspx
I had a chance to miss the point there.
:hehe:
_____________
Code for TallyGenerator
June 16, 2008 at 6:43 pm
Thank you to everyone for your replies!
And thank you especially to Sergiy, that code is exactly what I wanted. Thanks again!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply