no temp table please 1

[Scoty]

Hello everyone..
I am about to pull my hair out with this one and I know it is something very simple that I don't know about or don't know what to google on.

here is my situation I have a table which contains data stored by "end of week" with a week day indicator. I have to be able to pull the data dynamically for a report. So far I have been able to generate the week endind dates from the dates passed in, along with the day of week int's. I have also been able to pull all info in between the two dates.

A little back groud the day of week field is 0=Monday to 5=Saturday (Friday is the end of week, don't ask me why I didn't set it up). And the end of week field is formatted mmddyyyy so I do someformatting to bring in a true date.

So here is my stored procedure:

Create Procedure [sp_RetrieveOIRDataByEndDate1]
/*This gathers the Between Dates
Created by SF for Jasper reports
for the written release web app
rewrite 02/15/06*/

@BeginDate as [DateTime], @EndDate as [DateTime]

as

Declare @Begin_DayOfWeek int, 
@End_DayOfWeek int, 
@Begin_Week_Ending DateTime, 
@End_Week_Ending DateTime

Set @Begin_DayOfWeek = DatePart(dw,@BeginDate) - 2
Set @End_DayOfWeek = DatePart(dw,@EndDate) - 2
If @End_DayOfWeek  = 4
Set @End_DayOfWeek = 5
Set @Begin_Week_Ending = DateAdd(day,5 - (DatePart(dw,@BeginDate) - 1),@BeginDate)
Set @End_Week_Ending = DateAdd(day,5 - (DatePart(dw,@EndDate) - 1),@EndDate)

/*First week with weekday*/
Select * From IORData
Where DateAdd(day,0,(left(WeekEnding,2) + '/' + substring(WeekEnding,3,2) + '/' + right(WeekEnding,4))) = @Begin_Week_Ending
And cast([weekday] as integer) >= @Begin_DayOfWeek

/*Middle dates*/
Select * From IORData
Where DateAdd(day,0,(left(WeekEnding,2) + '/' + substring(WeekEnding,3,2) + '/' + right(WeekEnding,4))) between DateAdd(Day,7,@Begin_Week_Ending) and DateAdd(Day,-7,@End_Week_Ending)

/*End week with week day*/
Select * From IORData
Where DateAdd(day,0,(left(WeekEnding,2) + '/' + substring(WeekEnding,3,2) + '/' + right(WeekEnding,4))) = @End_Week_Ending
And cast([weekday] as integer) <= @End_DayOfWeek

This returns all the data I need but returns multiple rowsets. I need it to be all one rowset.

How do I do this with out making temp tables?

tia

Scoty :


Learn from others' mistakes. You could not live long enough to make them all yourself
-- Hyman George Rickover (1900-86)

 

[vongrunt]

OR all three WHERE expressions into one - WHERE (first) OR (second) OR (third).

Or use UNION ALL.

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[Scoty]

vongrunt,
thanks for the quick reply... I knew it had to be something easy. I don't have a lot of advanced experience with writing my own SQL.

thanks again

scoty

Learn from others' mistakes. You could not live long enough to make them all yourself
-- Hyman George Rickover (1900-86)