Get Period's Dates for a Definied Year

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 2000 Forums

SQL Server Development (2000)

Get Period's Dates for a Definied Year

Author

Topic

ubravo
Starting Member

6 Posts

Posted - 2006-01-27 : 12:45:39

Hi, I'm trying to do a Query or SP that can I pass StartDate and EndDate ('01/01/2005' & '31/12/2005') and get the Quarter that fits into that dates.

The problem is that I think that I need a 'Loop' or something like that because I need to iterate into the dates to get the Quarters by Month, by example with the parameters below, I have a Table called Intervals that contains all singles dates since 1995 until 2038.
INTERVALS TABLE
Column Name Date Type Length
--------------------------------------
IntervalType int 4
StartDate datetime 8
EndDate datetime 8
---------------------------------------
0 1/1/1995 1/1/1995 11:59:59 PM
0 1/2/1995 1/2/1995 11:59:59 PM
0 1/3/1995 1/3/1995 11:59:59 PM
...
0 12/29/2038 12/29/2038 11:59:59 PM
0 12/30/2038 12/30/2038 11:59:59 PM
0 12/31/2038 12/31/2038 11:59:59 PM

I need to get the next result
RESULT -------------->
Date Quarter No. Month
-------------------------------
1/1/05 1 JAN
1/16/05 2 JAN
2/1/05 1 FEB
2/16/05 2 FEB
3/1/05 1 MAR
3/16/05 2 MAR
4/1/05 1 APR
4/16/05 2 APR
...
11/1/05 1 NOV
11/17/05 2 NOV
12/1/05 1 DEC
12/16/05 2 DEC

Any Idea? I have this code
declare @WeekOfYear int

select
-- Compute week of year as (days since start of year/7)+1
-- Division by 7 gives whole weeks since start of year.
-- Adding 1 starts week number at 1, instead of zero.
@WeekOfYear =
(datediff(dd,
-- Case finds start of year
case
when NextYrStart <= @date
then NextYrStart
when CurrYrStart <= @date
then CurrYrStart
else PriorYrStart
end,@date)/7)+1
from
(
select
-- First day of first week of prior year
PriorYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690),
-- First day of first week of current year
CurrYrStart =
dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690),
-- First day of first week of next year
NextYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690)
from
(
select
--Find Jan 4 for the year of the input date
Jan4 =
dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0))
) aa
) a

return @WeekOfYear

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-27 : 13:18:01

Uhmmm....have you looked up the DATEPART() function? You may end up slapping yourself on the forehead...doh!

ubravo
Starting Member

6 Posts

Posted - 2006-01-27 : 13:50:02

I'm sorry, of course I know DATEPART() function but the problem is how can I generate a loop validating the startdate and enddate to get all the Quarters into, I can't figure out how does it work?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-27 : 14:45:46

You need to better explain what your definition of a Quarter is. You posted this, but I can't understand why 2005/1/16 would be Quarter 2, but 2005/2/1 would be Quarter 1. The usual definition of a calendar quarter is month 1,2,3 = Qtr 1, month 4,5,6 = Qtr 2, month 7,8,9 = Qtr 3, and montn 10,11,12 = Qtr 4.

Also, what are you basing your week of the year on? The code you posted appears to be from the function I posted for the ISO Week of Year Function in this topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510
If you are using the ISO Week of Year, that is OK, but why not just use the function? Then you could use the function in a select from your table, passing the date to the function. However, it is not clear to me that you really want to use the ISO Week of Year, and how you want to convert that to quarters.


Date 		Quarter No. 	Month
-------		-----------	-----
1/1/05		1 		JAN
1/16/05		2 		JAN
2/1/05		1		FEB
2/16/05		2 		FEB
3/1/05		1		MAR
3/16/05		2		MAR
4/1/05		1		APR 
4/16/05 	2		APR

CODO ERGO SUM

ubravo
Starting Member

6 Posts

Posted - 2006-01-27 : 14:56:15

I posted the ISO Week of Year just like something similar the Quarter Number for the example is not important the only important to me is if anybody have an idea how can I get from Intervals Table only the biweekly periods, I mean I have the next rows in that table
---------------------------------------
0 1/1/1995 1/1/1995 11:59:59 PM
0 1/2/1995 1/2/1995 11:59:59 PM
0 1/3/1995 1/3/1995 11:59:59 PM
...
0 12/29/2038 12/29/2038 11:59:59 PM
0 12/30/2038 12/30/2038 11:59:59 PM
0 12/31/2038 12/31/2038 11:59:59 PM
HOW CAN I GET ONLY
1/01/2005
1/16/2005
2/01/2005
2/16/2005
3/01/2005
3/16/2005
4/01/2005
4/16/2005
FOR A GIVEN STARTDATE and ENDDATE for this INTERVALS TABLE

ubravo
Starting Member

6 Posts

Posted - 2006-01-27 : 15:08:21

Maybe, something like this, anybody have a better idea to accomplish this...?
/* CREATE TEMP TABLE TO STORAGE DATA */
create table temp1
(PeriodDate datetime PRIMARY KEY)
declare @i datetime
declare @j datetime
set @i=convert(varchar,'01-01-2005',101)
set @j=convert(varchar,'12-31-2005',101)
/* LOOP is this necessary or not? */
while @i<=@j
begin insert into temp1 select @i set @i=@i+1 end
/* SELECT RECENT INSERTED DATA (Dates) */
select * from temp1
/* DROP TEMP TABLE */
drop table temp1

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-27 : 15:57:36

Select convert(varchar,D,101) from T6 where day(D) = 1 or day(D)=16 and D between '01/18/2005' and '09/07/2005'
order by D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources