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 |
|
foutuguy
Starting Member
3 Posts |
 Posted - 2008-01-28 : 10:11:59
|
| Hello,I have a database containing 2 tables:the first one called "weeks" has 4 columns: id, week number (ex:2007/01), start date(ex:01/01/2007), end date(ex: 07/01/2007)the second one is called "tests" and has 5 columns: index, date, name,test name, durationmy goal: I want to make a query to get rows containing 3 columns:week number,name,dates = date1, date2, etc.,tests=test1, test2, etc.,total duration of test for the given week and given personTo be clear, I am trying to get per week a summary of names with thetests they did and how long total it lasted. The first tables is justhere to get the week number, given the test date.My current query returns a list of all tests (table 2) with an addedcolumn of the week number and looks like this:SELECT date, initials, test, [hours outside 8-19], [Week number] FROMtests, weeks WHERE date BETWEEN [start date] AND [end date]What I need now is to have one line per person and per week with their total of hours for the week and the list of tests for the same week. I tried lots of things but does not know to get to my goal. Couldsomeone please help me?Any help will be appreciated. Thank you in advancePierrot |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2008-01-28 : 10:15:38
|
| is this schoolwork? |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-01-28 : 19:00:45
|
| One approach to look at is using a calendar table.Jack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
|
foutuguy
Starting Member
3 Posts |
Posted - 2008-01-29 : 02:07:30
|
| It is no schoolwork. I already have a query integrating the week number so my concern is more about grouping the result lines that have the same name and week number, adding the hours. Am I clear in my explanations? |
|
|
|
|
|
|
|
|
Help writing a query