Author |
Topic |
Luuk123
Yak Posting Veteran
52 Posts |
Posted - 2013-01-08 : 05:04:34
|
Hi All,I have all the dates of the years 2000 to 2020 in a table.I don't want to use the regular weeknumbers, but I want 13 periodes of 28 days. I want to set a starting date. The query I need has to count the first 28 days en give it period number 1. The next 28 days have to be period 2.When period 13 has ended, the following 28 days after period 13 have to be period 1.Can somebody help me?Thanks a lot. |
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-08 : 05:43:45
|
Something like;with cte as(select d = convert(datetime,'20000101'), weekno = 1, seq = 1union allselect d = d+1,weekno = case when seq = 28 then case when weekno = 13 then 1 else weekno+1 end else weekno end,seq = case when seq = 28 then 1 else seq + 1 endfrom cte where d<'20200101')select *from cteoption (maxrecursion 0)or maybe;with cte as(select d = convert(datetime,'20000101')union allselect d = d+1 from cte where d<'20200101')select d, weekno = (datediff(dd,'20000101',d)-1)/28+1option (maxrecursion 0)==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-08 : 05:52:55
|
[code]SELECT (((((Seq-1)/28)+1)-1)%13)+1 AS period,dateFROM(SELECT ROW_NUMBER() OVER (ORDER BY datefield) AS Seq,dateFROM table)t[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-08 : 05:57:20
|
second one should have been;with cte as(select d = convert(datetime,'20000101')union allselect d = d+1 from cte where d<'20200101')select d, weekno = (((datediff(dd,'20000101',d))/28+1)-1)%13+1from cteoption (maxrecursion 0)==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2013-01-08 : 09:47:17
|
quote: Originally posted by Luuk123 Hi All,I have all the dates of the years 2000 to 2020 in a table.I don't want to use the regular weeknumbers, but I want 13 periodes of 28 days. I want to set a starting date. The query I need has to count the first 28 days en give it period number 1. The next 28 days have to be period 2.When period 13 has ended, the following 28 days after period 13 have to be period 1.Can somebody help me?Thanks a lot.
Since your year would only have 364 days, what do you do with the one or two days that are left?CODO ERGO SUM |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-08 : 10:21:01
|
Don't think a year matters - it's just periods starting with 20000101.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Luuk123
Yak Posting Veteran
52 Posts |
Posted - 2013-01-08 : 13:09:08
|
Thank you all for your replies.I've used visakh16's solution. That did the trick.Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-08 : 23:46:42
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-09 : 21:56:08
|
quote: Originally posted by visakh16
SELECT (((((Seq-1)/28)+1)-1)%13)+1 AS period,dateFROM(SELECT ROW_NUMBER() OVER (ORDER BY datefield) AS Seq,dateFROM table)t ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
+1 --Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-09 : 22:27:35
|
quote: Originally posted by Jeff Moden
quote: Originally posted by visakh16
SELECT (((((Seq-1)/28)+1)-1)%13)+1 AS period,dateFROM(SELECT ROW_NUMBER() OVER (ORDER BY datefield) AS Seq,dateFROM table)t ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
+1 --Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it."
Thanks ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-10 : 23:52:45
|
quote: Originally posted by sigmas
quote: Originally posted by visakh16
SELECT (((((Seq-1)/28)+1)-1)%13)+1 AS period,dateFROM(SELECT ROW_NUMBER() OVER (ORDER BY datefield) AS Seq,dateFROM table)t
Keep it simplerSELECT ((((Seq+27)/28)-1)%13)+1 AS period,dateFROM(SELECT ROW_NUMBER() OVER (ORDER BY datefield) AS Seq,dateFROM table)t
This approach looks similar to mine. Can you explain how this is simpler?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sigmas
Posting Yak Master
172 Posts |
Posted - 2013-01-11 : 07:47:24
|
I made a mistake. I'm sorry |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-11 : 08:43:03
|
no problem... just checked------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|