April 23, 2009 at 7:27 am
I need to break the Start date and End date for every 7 days. The maximum number of 7 days period which I was thinking will be 52 weeks.
I thought of declaring 52 Start and End dates, but I ‘m little confused as Start and End dates will be changing.
Can anybody please help me?
April 23, 2009 at 7:56 am
I'm not entirely clear on what you're asking.
Is what you need a list of the possible start and end dates for all weeks in a year? If so, have you thought about using a standard Calendar table?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2009 at 8:16 am
Suppose if the start date is ‘2009/04/01’ and end date is ‘2009/04/19’ then I need to split them as
Startdate1=’2009/04/01’
Enddate1=’2009/04/07’
Startdate2=’2009/04/08’
Enddate2=’2009/04/15
Startdate3=’2009/04/16’
Enddate3=’2009/04/19’
Like this I need to split the days. The maximum which I was thinking to allow is 52 periods.
April 23, 2009 at 8:37 am
If you have a Numbers/Tally table, I think this will do what you need:
declare @SDate datetime, @EDate datetime;
select @SDate = '4/1/09', @EDate = '4/19/09'
select Number as Week, dateadd(week, number-1, @SDate) as StartDate,
case
when dateadd(week, number, @SDate)-1 > @EDate then @EDate
else dateadd(week, number, @SDate)-1
end as EndDate
from dbo.Numbers
where number <= datediff(week, @SDate, @EDate);
If you don't have one, here's a way to build one:
create table dbo.Numbers (Number int identity primary key, PlaceHolder bit);
go
insert into dbo.Numbers (PlaceHolder)
select top 10000 null
from master.dbo.sysobjects t1
cross join master.dbo.sysobjects t2;
go
alter table dbo.Numbers
drop column PlaceHolder;
(There are more efficient ways to build one in SQL 2005, but I'm assuming you're using 2000 because of the forum you posted in.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2009 at 9:11 am
If I’m running for @SDate = '3/1/09', @EDate = '4/21/09' then it is giving me only 7 weeks.
It’s not giving me the last case which is ‘04/19/2009’ to ‘04/21/2009’.
April 23, 2009 at 9:19 am
I figured it out. I added one to datediff in where clause. I made a temp table and removed where startdate is greater that end date. Thank you so much for it.
But now I have one more question, if the Start and end dates are null then they want to run for previous 52 weeks.
The week start from Sunday to Saturday. We have a function called PreviousWeek function which gives the start and end dates of each week.
Select WeestStart,WeekEnd from fnPeriousWeek(Getdate())
Can we use that function and get previous 52 weeks? I declared 52 date variables and ran that function 52 times but your method is simple and efficient?
April 23, 2009 at 9:31 am
Try this. It does the "last 52 weeks" thing, and I fixed the problem with the date range you mentioned.
-- "Params"
declare @SDate datetime, @EDate datetime;
select @SDate = '3/1/09', @EDate = '4/21/09';
-- Proc
select
@SDate = isnull(@SDate, dateadd(week, -52, dateadd(week, datediff(week, 0, getdate()), 0))),
@EDate = isnull(@EDate, getdate());
select Number as Week, dateadd(week, number-1, @SDate) as StartDate,
case
when dateadd(week, number, @SDate)-1 > @EDate then @EDate
else dateadd(week, number, @SDate)-1
end as EndDate
from dbo.Numbers
where number <= datediff(week, @SDate, @EDate)+1;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2009 at 9:44 am
Shree (4/23/2009)
I figured it out. I added one to datediff in where clause. I made a temp table and removed where startdate is greater that end date. Thank you so much for it.But now I have one more question, if the Start and end dates are null then they want to run for previous 52 weeks.
The week start from Sunday to Saturday. We have a function called PreviousWeek function which gives the start and end dates of each week.
Select WeestStart,WeekEnd from fnPeriousWeek(Getdate())
Can we use that function and get previous 52 weeks? I declared 52 date variables and ran that function 52 times but your method is simple and efficient?
Try it in the following way:
declare@FromDatedatetime
,@ToDatedatetime
set@FromDate= '03/01/2009'
set@ToDate= getdate()
while(@FromDate <= @ToDate)
begin
select[Date]= @FromDate
,[Next Week Date]= dateadd (ww, 1, @FromDate - 1)
set@FromDate = dateadd (ww, 1, @FromDate)
end
go
Regards.
April 23, 2009 at 12:26 pm
Thank you so much, it worked.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply