[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

using T-SQL generate a weekly time frame table

Posted on 2011-02-18
9
Medium Priority
?
1,108 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 400 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 60

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 60

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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 60

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 60

Expert Comment

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

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 60

Assisted Solution

by:HainKurt
HainKurt earned 600 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 1000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

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.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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