Combine results sets for loop without using TempTable 1

[paulstrovsky]

Hi all,

I have a query that loops through weeks in order to sum up hours worked by employees. Everything works fine, except I get multiple result sets if I do this query without using a temp table. I figure there must be some way to work the UNION operator in at the beginning of the loop so that I can combine all the result sets into one. Any ideas?

Please don't say "Use a temporary table" because I am trying to do this without using one (I know I'm a dork).

Is there a way to UNION my loop? I get an error when I stick a UNION right after my BEGIN statement.

Thanks!
Paul

 

[paulstrovsky]

Nevermind, I don't think this is possible...

But please pipe up if you know a way of doing this without a temp table!

 

[dnoeth]

There's only one way:
Don't use temp tables, don't use loops/cursors, just use one plain SQL statement.

Dieter

 

[paulstrovsky]

I am summing timeclock hours by week with columns breaking down the daily amounts...

For example:

Name WeekStarting Monday Tuesday WeekTotal
Joe Schmoe 1/1/01 8 9 48
Joe Schmoe 1/8/01 4 10 38
Bob Schmoe 1/1/01 5 6 40


How would you propose that this be done without using a loop? Hard code the start date for every single week in question?

I believe that this query cannot be written without using a stored proc or a table of some sort, and that it would be a royal PITA without a loop.

I would love to be proved wrong on this...

 

[dnoeth]

Can you calculate the day of week?
The "date_col - day_of_week(Date_col) + 1" calculates "WeekStarting"

Does your RDBMS support CASE or something similar?

select
name,
date_col - day_of_week(Date_col) + 1 as WeekStarting,
sum(case when day_of_week(Date_col) = 1
then hours_to_sum
else 0
end) as "Monday",
sum(case when day_of_week(Date_col) = 2
then hours_to_sum
else 0
end) as "Tuesday",
...
sum(hours_to_sum) as WeekTotal
from tab
group by name, weekStarting

Dieter

 

[paulstrovsky]

I think I understand what you're saying-

I determine what day of the week (Monday, Tuesday, etc.) a given date is, from there I can figure out the date for the Sunday of that week as my WeekStarting date by simply subtracting from the current date. Then I just use group by on the weekstarting column...

It seems so simple now, thanks for the help!