Solved

using T-SQL generate a weekly time frame table

Posted on 2011-02-18
9
1,067 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now