Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 split year over 13 periods of 28 days

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 = 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.
Go to Top of Page

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,date
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY datefield) AS Seq,date
FROM table
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-08 : 23:46:42
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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,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."
Go to Top of Page

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,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/

Go to Top of Page

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,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/

Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-01-11 : 07:47:24
I made a mistake. I'm sorry
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 08:43:03
no problem... just checked

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -