Solved

using T-SQL generate a weekly time frame table

Posted on 2011-02-18
9
1,085 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 53

Expert Comment

by:Huseyin KAHRAMAN
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 53

Expert Comment

by:Huseyin KAHRAMAN
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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
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 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34929382
this works as long as dates are in the same year :)
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
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 53

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN 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 41

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Title # Comments Views Activity
Many to one in one row 2 46
T-SQL: Only Wanting One Record 8 59
EF5: Update Model from Database not working 3 77
Complex SQL Server WHERE CLause 9 37
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

737 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