SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 split year over 13 periods of 28 days
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Luuk123
Starting Member

45 Posts

Posted - 01/08/2013 :  05:04:34  Show Profile  Reply with Quote
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
3383 Posts

Posted - 01/08/2013 :  05:43:45  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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

India
52249 Posts

Posted - 01/08/2013 :  05:52:55  Show Profile  Reply with Quote

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

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/08/2013 :  05:57:20  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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)

USA
7020 Posts

Posted - 01/08/2013 :  09:47:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/08/2013 :  10:21:01  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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
Starting Member

45 Posts

Posted - 01/08/2013 :  13:09:08  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 01/08/2013 :  23:46:42  Show Profile  Reply with Quote
welcome

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

Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/09/2013 :  21:56:08  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 01/09/2013 :  22:27:35  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 01/10/2013 :  23:52:45  Show Profile  Reply with Quote
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

Belarus
172 Posts

Posted - 01/11/2013 :  07:47:24  Show Profile  Reply with Quote
I made a mistake. I'm sorry
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/11/2013 :  08:43:03  Show Profile  Reply with Quote
no problem... just checked

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000