selecting weeks between datepart's 2

[Dashley]

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

 

[SQLDenis]

you need a left join for that since you don't have orders for week 5

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

 

[PWise]

A star for this code

select DATEPART(wk, DATEADD(WK,numberid, '12/11/2006'))
from dbo.numberpivot
where numberid < = datediff(wk,'12/11/2006','1/28/2007')

I used to use something like this

Where numberid between DATEPART(wk,'1/1/06') and DATEPART(wk,'12/11/06')
and numberid between DATEPART(wk,'1/28/2007') and DATEPART(wk,'12/31/07')

 

[PWise]

for SQLDenis that is

 

[Dashley]

pwise Give Denis the star

Tried a left same result

SET DATEFIRST 1
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
LEFT 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

 

[MDXer]

try a Right Outer Join. Since you want all week numbers regardless of data in your studentdata Table.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[SQLDenis]

you always left join the table that you don't have data for, if you don't want to change the order in your script then yes a right join has to be used

select * from table a left join table b
is the same as
select * from table b right join table a


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

 

[Dashley]

Like this?

No Luck


SET DATEFIRST 1
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
RIGHT outer join
(select DATEPART(wk, DATEADD(WK,numberid, @startdate)) as nid
from dbo.numberpivot
where numberid < = datediff(wk,@startdate,@enddate)) N on datepart(wk,s.date) = N.nid
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

 

[SQLDenis]

change
where s.userid = 6067

to
and s.userid = 6067

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

 

[Dashley]

Ahh Ha. Now that puppy works.

Thanks denis. Can I bang my head on the desk now?