Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-03-07 : 08:42:16
|
| Will writes "I have an employees table, which gives all emp. ids. I have a second table, time_log with tasks each employee has logged:empID date log_time duration etc===================================and a 3rd table - a pivot table with a single column named "i" containing consecutive integers from 0 - 1000I need to know for each date in a series, e.g seven consecutive days, how much time each has logged. easy if everyone has logged a task for every day, but I need to include every day where they have not logged a task.so, a cartesian join on all the dates in a series(produced using dateadd on p.i and the pivot table)SELECT dateadd('d',p.i, #02/19/2007#), e.empIDFROM pivot1 p, employees eWHERE i<no_datesHowever I need to do a left join with time log where the date and employee ids are the same, and I have summed the durations for each date. The following query does this, but does not include dates and times where nothing has been logged.SELECT empID, log_date, sum(duration)FROM time_logGROUP BY empEIN, log_dateGIVING, EVENTUALLY, ALL DATES AND EMPIDS AND THE TOTAL AMOUNT OF TIME THEY HAVE LOGGED FOR EACH DAY." |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Two join types in one select statment 