rmtogether
asked on
using T-SQL generate a weekly time frame table
Hi, experts
I would like to generate a weekly time frame table which consists of 2 columns (start_day & end_day). start_day is ALWAYS Sunday, end_day is ALWAYS Saturday
The input for start_day is ALWAYS Sunday as well, but the input of end_day might not necessary be Saturday.
In the case of input of end_day is not Saturday, the query will automatically figure out the Saturday for that week.
Here is an example:
giving the start_day "2011-02-06" (it is Sunday) and end_day "2011-02-28" (it is not Saturday)
my T-SQL will generate a result like below
Start_Day End_Day
2011-02-06 2011-02-12
2011-02-13 2011-02-19
2011-02-20 2011-02-26
2011-02-27 2011-03-05 --> 3/5/2011 is the Saturday of the giving end_day ("2011-02-28"
Is there a good way to do this? thank you in advance.
I would like to generate a weekly time frame table which consists of 2 columns (start_day & end_day). start_day is ALWAYS Sunday, end_day is ALWAYS Saturday
The input for start_day is ALWAYS Sunday as well, but the input of end_day might not necessary be Saturday.
In the case of input of end_day is not Saturday, the query will automatically figure out the Saturday for that week.
Here is an example:
giving the start_day "2011-02-06" (it is Sunday) and end_day "2011-02-28" (it is not Saturday)
my T-SQL will generate a result like below
Start_Day End_Day
2011-02-06 2011-02-12
2011-02-13 2011-02-19
2011-02-20 2011-02-26
2011-02-27 2011-03-05 --> 3/5/2011 is the Saturday of the giving end_day ("2011-02-28"
Is there a good way to do this? thank you in advance.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
and this one:
select n WEEK_NO,
@startday - DATEPART(w, @startday)+1+7*(n-DATEPART(ww,@startday)) WEEK_START,
@startday - DATEPART(w, @startday)+1+7*(n+1-DATEPART(ww,@startday))-1 WEEK_END
from v_nn where n between DATEPART(ww,@startday) and DATEPART(ww,@endday)
7 2011-02-06 00:00:00.000 2011-02-12 00:00:00.000
8 2011-02-13 00:00:00.000 2011-02-19 00:00:00.000
9 2011-02-20 00:00:00.000 2011-02-26 00:00:00.000
10 2011-02-27 00:00:00.000 2011-03-05 00:00:00.000
and this
select n WEEK_NO,
cast(@startday - DATEPART(w, @startday)+1+7*(n-DATEPART(ww,@startday)) as date) WEEK_START,
cast(@startday - DATEPART(w, @startday)+1+7*(n+1-DATEPART(ww,@startday))-1 as date) WEEK_END
from v_nn where n between DATEPART(ww,@startday) and DATEPART(ww,@endday)
7 2011-02-06 2011-02-12
8 2011-02-13 2011-02-19
9 2011-02-20 2011-02-26
10 2011-02-27 2011-03-05
this works as long as dates are in the same year :)
this works if they are in different years:
declare @startday datetime
declare @endday datetime
set @startday = '2011-02-06'
set @endday = '2012-02-28'
select n+1 WEEK_NO,
cast(@startday + 7*n as date) WEEK_START,
cast(@startday + 7*n+6 as date) WEEK_END
from v_nnn where n <= DATEDIFF(ww,@startday,@endday)
order by n
WEEK_NO WEEK_START WEEK_END
1 2011-02-06 2011-02-12
2 2011-02-13 2011-02-19
...
55 2012-02-19 2012-02-25
56 2012-02-26 2012-03-03
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you so much. you guys are true experts
create view v_nn as
select n1.n*10+n2.n as n from numbers n1, numbers n2
this view will give you numbers from n=0 to 99
now
select * from v_nn where n between DATEPART(ww,@startday) and DATEPART(ww,@endday)
will give you week numbers
then
Open in new window