| Author |
 Topic  |
|
|
Luuk123
Starting Member
12 Posts |
 Posted - 01/08/2013 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/08/2013 : 05:43:45
|
Something like
;with cte as
(
select d = convert(datetime,'20000101'), weekno = 1, seq = 1
union all
select 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 end
from cte where d<'20200101'
)
select *
from cte
option (maxrecursion 0)
or maybe
;with cte as
(
select d = convert(datetime,'20000101')
union all
select d = d+1 from cte where d<'20200101'
)
select d, weekno = (datediff(dd,'20000101',d)-1)/28+1
option (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
India
47157 Posts |
Posted - 01/08/2013 : 05:52:55
|
SELECT (((((Seq-1)/28)+1)-1)%13)+1 AS period,date
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY datefield) AS Seq,date
FROM table
)t
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 01/08/2013 05:56:54 |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/08/2013 : 05:57:20
|
second one should have been
;with cte as
(
select d = convert(datetime,'20000101')
union all
select d = d+1 from cte where d<'20200101'
)
select d, weekno = (((datediff(dd,'20000101',d))/28+1)-1)%13+1
from cte
option (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)
USA
6997 Posts |
Posted - 01/08/2013 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/08/2013 : 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
Starting Member
12 Posts |
Posted - 01/08/2013 : 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
India
47157 Posts |
Posted - 01/08/2013 : 23:46:42
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 01/09/2013 : 21:56:08
|
quote:
Originally posted by visakh16
SELECT (((((Seq-1)/28)+1)-1)%13)+1 AS period,date
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY datefield) AS Seq,date
FROM table
)t
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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
India
47157 Posts |
Posted - 01/09/2013 : 22:27:35
|
quote:
Originally posted by Jeff Moden
quote:
Originally posted by visakh16
SELECT (((((Seq-1)/28)+1)-1)%13)+1 AS period,date
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY datefield) AS Seq,date
FROM table
)t
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 MVP
http://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 01/10/2013 : 23:52:45
|
quote:
Originally posted by sigmas
quote:
Originally posted by visakh16
SELECT (((((Seq-1)/28)+1)-1)%13)+1 AS period,date
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY datefield) AS Seq,date
FROM table
)t
Keep it simpler
SELECT ((((Seq+27)/28)-1)%13)+1 AS period,date
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY datefield) AS Seq,date
FROM table
)t
This approach looks similar to mine. Can you explain how this is simpler?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sigmas
Starting Member
33 Posts |
Posted - 01/11/2013 : 07:47:24
|
| I made a mistake. I'm sorry |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 01/11/2013 : 08:43:03
|
no problem... just checked
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
split year over 13 periods of 28 days
