Solved

using T-SQL generate a weekly time frame table

Posted on 2011-02-18
9
1,078 Views
Last Modified: 2012-05-11
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
Comment
Question by:rmtogether
9 Comments
 
LVL 12

Assisted Solution

by:mcv22
mcv22 earned 100 total points
ID: 34929181
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 34929349
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 34929361
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 51

Expert Comment

by:HainKurt
ID: 34929368
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 34929382
this works as long as dates are in the same year :)
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34929437
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
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 150 total points
ID: 34929448
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
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
ID: 34930554
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
 

Author Closing Comment

by:rmtogether
ID: 34932838
thank you so much. you guys are true experts
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question