Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

selecting weeks between datepart's 2

Status
Not open for further replies.
Dashley

Dashley

Programmer
Dec 5, 2002
925
US
I wrote this statement which gives me a start and end week number.

SELECT DATEPART(wk, '1/11/2007') as StartWeek, DATEPART(wk, '1/28/2007') as EndWeek

produces

StartWeek EndWeek
----------- -----------
2 5


I am trying to modify or just create a statment to use in a derived table join to producce the results 2,3,4,5 which are the weeks including and between the start and stop dates.

The results hopefully will be in one column as shown below

weeks
------
2
3
4
5

Thanks

Dan
 
Sort by date Sort by votes
Example

Code: 
[COLOR=green]-- Create out Pivot table ** do this only once-- populate it with 1000 rows 
[/color][COLOR=blue]CREATE[/color] [COLOR=blue]TABLE[/color] NumberPivot (NumberID [COLOR=blue]INT[/color] [COLOR=blue]PRIMARY[/color] [COLOR=blue]KEY[/color]) 

[COLOR=blue]DECLARE[/color] @intLoopCounter [COLOR=blue]INT[/color] 
[COLOR=blue]SELECT[/color] @intLoopCounter =0 

[COLOR=blue]WHILE[/color] @intLoopCounter <=100 [COLOR=blue]BEGIN[/color] 
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] NumberPivot 
[COLOR=blue]VALUES[/color] (@intLoopCounter) 

[COLOR=blue]SELECT[/color] @intLoopCounter = @intLoopCounter +1 
[COLOR=blue]END[/color] 
[COLOR=blue]GO[/color]


then your code would be

Code: 
[COLOR=blue]select[/color] * [COLOR=blue]from[/color] numberpivot
[COLOR=blue]where[/color] numberid between [COLOR=#FF00FF]DATEPART[/color](wk, [COLOR=red]'1/11/2007'[/color]) and [COLOR=#FF00FF]DATEPART[/color](wk, [COLOR=red]'1/28/2007'[/color])

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions





 
Upvote 0 Downvote
Denis,


Hi. Thanks. I'll do it. I was trying to get it to wwork without using a table but was having no luck.

enjoy

dan
 
Upvote 0 Downvote
Denis

good point.

It returns nothing. Uhh Oh

select * from numberpivot where numberid between DATEPART(wk, '12/11/2006') and DATEPART(wk, '1/28/2007')

 
Upvote 0 Downvote
Any how you slice it you will have to use some kind of table.

You can use a temp table and drop it when finished

Code: 
DECLARE @dte int
SET @dte = DATEPART(wk, '1/11/2007')
SELECT @dte as EndWeek into #Weeks 
WHILE @dte < DATEPART(wk, '1/28/2007')
BEGIN
   SET @dte = @dte + 1
   INSERT INTO #Weeks  values(@dte)
   
END
select * from #Weeks ORDER BY EndWeek
drop table #Weeks

Well Done is better than well said
- Ben Franklin
 
Upvote 0 Downvote
Thanks nice95gle.

Denis pointed out about what happens when the weeks are between two years. Thats a problem in itself. I'm going to have to figure out how to handle it.

enjoy


 
Upvote 0 Downvote
Denis Wow you saved me from a headach which was starting to form. Thanks.

I'll have to mod this inherited mess which has the same problem. It breaks over the year change too. Well time to go play

thanks again.


dan

Code: 
SET DATEFIRST 1
DECLARE @CurItemID INT
SELECT TOP 1 @CurItemID = CurItemID
FROM ReportLayout 
WHERE ReportID = 864
SELECT DATEPART(wk, '1/11/2007') as StartWeek, DATEPART(wk, '1/28/2007') as EndWeek,
	S.[UserID], DATEPART(wk,[Date]) as Wk, isnull(SUM(Quantity),0) as Total,
	(SELECT LastName + ', ' + FirstName FROM Users WHERE UserID = S.UserID) as Student
FROM StudentData S
Join numberpivot N on n.Numberid = DATEPART(wk,[Date])
INNER JOIN StudentLocation L ON L.UserID = S.UserID AND L.TS = S.[Date]
WHERE S.UserID IN (SELECT UserID
			FROM Users
			WHERE Class = 239 AND UserType = 'S')
AND CurItemID = @CurItemID
AND [Date] BETWEEN '1/11/2007' AND '1/28/2007'
AND L.LocationID = 430
GROUP BY S.[UserID], DATEPART(Wk,[Date])
ORDER BY Student, Wk
SET DATEFIRST 7


 
Upvote 0 Downvote
just to make your life harder....you know that SQL server week numbers are not ISO Week numbers right?


Code: 
In this example, a user-defined function, ISOweek, takes a date argument and calculates the ISO week number.  For this function to calculate properly, SET DATEFIRST 1 must be invoked before the function is called. 

CREATE FUNCTION ISOweek  (@DATE datetime)
RETURNS int
AS
BEGIN
   DECLARE @ISOweek int
   SET @ISOweek= DATEPART(wk,@DATE)+1
      -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
   IF (@ISOweek=0) 
      SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 
         AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
   IF ((DATEPART(mm,@DATE)=12) AND 
      ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
      SET @ISOweek=1
   RETURN(@ISOweek)
END


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions





 
Upvote 0 Downvote
Denis,

No I didn't but I do now and just went out an researched ISO Weeks. This is my first time using datepart in SQL.
A client called about some strange data and while I was chasing it down I ran accross the SP. I'm thinking of redoing it all. The data that was pulling was correct but the front end was displaying it wrong. Now I find out (as you pointed out) as years change the report blows up as does the SP (just tested it).

Gee today is getting better all the time HA

-dan
 
Upvote 0 Downvote
I have some thing similar to this in a data warehouse time dimension. I don't identify weeks as being weeks but Rather Year+Week. By appending the Year to the week number you make the week unique when it crosses years.

You can have a look up table that is populated with the YYYYWK and WeekNumber.
Such as:
YYYYWK WKNumber
------ --------
200650 50
...... ..
200705 5
Click to expand...

Remember however that you can't select the end from the start to tell how many weeks were between.
Code: 
Declare @sDate datetime
Declare @eDate datetime
Declare @sDateID int
Declare @eDateID int

Set @sDate = '12/11/2006'
Set @eDate = '1/28/2007'

select 
@sDateID = CAST(Cast(DATEPART(yy, @sDate) as varchar) + RIGHT('00' + Cast(DATEPART(wk, @sDate) as varchar),2) as int)
,@eDateID = Cast(Cast(DATEPART(yy, @eDate) as varchar) + RIGHT('00' + Cast(DATEPART(wk, @eDate) as varchar),2) as int)

Select
WKNumber
From lkp_Weeks
Where YYYYWW between @sDateID and @eDateID
Order By YYYYWW

It doesn't avoid the issue of needing a table but it does deal with the Year issue



Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck
 
Upvote 0 Downvote
Or you can just create a calendar table

Code: 
create table MyCalendar( Id identity not null,
			Date datetime, 
			isUSholiday bit,
			isUKholiday bit,
			isNLholiday bit,
			isweekend bit,
			DayOfweek tinyint,
			WeekNumber tinyint,
			Year int,
			Month tinyint
			-- whatever else
			--
			--)

So for this table we have 20 bytes per row, a year is only 365 rows, 10 years is 3650 rows and a century is only 36500 rows
So this table is going to be blazing fast no matter what you do
in you other tables you just store the id of this table. when you join you just select the columns that you need in this case WeekNumber



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions





 
Upvote 0 Downvote
denis

I'm trying. I put your snipplet into this statement to use as a dervied table.

Code: 
declare @startdate smalldatetime
declare @enddate smalldatetime
set @startdate='1/11/2007'
set @enddate='1/28/2007'
Select isnull(sum(quantity),0) as qty, datepart(wk,s.date) as wk
from studentdata s
inner join 
(select DATEPART(wk, DATEADD(WK,numberid, @startdate)) as nid
from dbo.numberpivot
where numberid < = datediff(wk,@startdate,@enddate)) N on N.nid = datepart(wk,s.date) 
where s.userid = 6067
and datepart(wk,date) in (select DATEPART(wk, DATEADD(WK,numberid, @startdate)) as nid 
from numberpivot where numberid < = datediff(wk,@startdate,@enddate))
and CurItemID = 51558
and date BETWEEN @startdate AND @enddate
group by datepart(wk,date), s.userid


the data rturned like this

qty wk
---------------------------------------- -----------
24.00 2
24.00 3
26.50 4

I was wanting (expecting) a zero for wk 5

declare @startdate smalldatetime
declare @enddate smalldatetime
set @startdate='1/11/2007'
set @enddate='1/28/2007'
select DATEPART(wk, DATEADD(WK,numberid, @startdate)) as nid
from dbo.numberpivot
where numberid < = datediff(wk,@startdate,@enddate)

returns

nid
-----------
2
3
4
5
 
Upvote 0 Downvote
I forgot to mention I'm using SET DATEFIRST 1
before I run the procedure.

It's giving me this

qty wk
---------------------------------------- -----------
24.00 2
24.00 3
26.50 4

I"m looking for this


qty wk
---------------------------------------- -----------
24.00 2
24.00 3
26.50 4
0 5
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

CodeWell
  • Locked
  • Question
Calculate Duration between Timestamps on Different Rows
Replies
2
Views
254
dhookom
dhookom
tn_sudheer
  • Locked
  • Question
Accessing data from database between given time stamps
Replies
11
Views
494
Andrzejek
Andrzejek
Niki_S
  • Locked
  • Question
Add two databases for connection between sql and foxpro
Replies
1
Views
223
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
Difference between COMMIT and ROLLBACK 3
Replies
5
Views
620
grnzbra
grnzbra
kermit01de
  • Locked
  • Question
gaps between item numbers
Replies
5
Views
353
bborissov
bborissov

Part and Inventory Search

Sponsor

Back
Top