[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1119
  • Last Modified:

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.
0
rmtogether
Asked:
rmtogether
3 Solutions
 
mcv22Commented:
Create a table nums (n int) which has numbers from 1 to a large number say 10000.

Use the following code:


declare @startday datetime
declare @endday datetime

set @startday = '2011-02-06'
set @endday = '2011-02-28'

select
	dateadd(dd, (n-1)*7, @startday),
	dateadd(dd, (n-1)*7 + 6, @startday)
from
	nums
where
	n between 1 and 
	case datepart(dw, @endday)
		when 7 then datediff(ww, @startday, @endday)
		else datediff(ww, @startday, @endday) + 1
	end

Open in new window

0
 
HainKurtSr. System AnalystCommented:
create a table numbers (n int) put 0,1,..,9

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


declare @startday datetime
declare @endday datetime

set @startday = '2011-02-06'
set @endday = '2011-02-28'

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)) 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-13 00:00:00.000
8	2011-02-13 00:00:00.000	2011-02-20 00:00:00.000
9	2011-02-20 00:00:00.000	2011-02-27 00:00:00.000
10	2011-02-27 00:00:00.000	2011-03-06 00:00:00.000

Open in new window

0
 
HainKurtSr. System AnalystCommented:
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

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
HainKurtSr. System AnalystCommented:
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

Open in new window

0
 
HainKurtSr. System AnalystCommented:
this works as long as dates are in the same year :)
0
 
HainKurtSr. System AnalystCommented:
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

Open in new window

0
 
HainKurtSr. System AnalystCommented:
above one works for all dates as long as enddate > startdate

where v_nnn is defined as

create view [dbo].[v_nnn] as
select n1.n*100+n2.n*10+n3.n as n from numbers n1, numbers n2, numbers n3
0
 
SharathData EngineerCommented:
You can try CTE.
declare @StartDate datetime,@EndDate datetime
 select @StartDate = '2011-02-06',@EndDate = '2011-02-28'
declare @Dates table (StartDate datetime,EndDate datetime)
 insert @Dates 
 select @StartDate,
        DATEADD(DAY,
                CASE DATEPART(W,@EndDate) WHEN 1 THEN 6 WHEN 2 THEN 5 WHEN 3 THEN 4 WHEN 4 THEN 3 WHEN 5 THEN 2 WHEN 6 THEN 1 WHEN 7 THEN 0 END,
                @EndDate)

;with DateCTE as (
select StartDate,DATEADD(DAY,6,StartDate) EndDate from @Dates union all
select DATEADD(WEEK,1,StartDate) StartDate, DATEADD(DAY,6,DATEADD(WEEK,1,StartDate)) EndDate 
  from DateCTE
 where EndDate < @EndDate)
select * from DateCTE 
/*
StartDate	EndDate
2011-02-06 00:00:00.000	2011-02-12 00:00:00.000
2011-02-13 00:00:00.000	2011-02-19 00:00:00.000
2011-02-20 00:00:00.000	2011-02-26 00:00:00.000
2011-02-27 00:00:00.000	2011-03-05 00:00:00.000
*/

Open in new window

0
 
rmtogetherAuthor Commented:
thank you so much. you guys are true experts
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now