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 |
|
pras2007
Posting Yak Master
216 Posts |
 Posted - 2007-09-30 : 16:17:58
|
| Hello All,Below is the query I wrote to formulate “Total_amount”; The problem is that the result I’m getting is not what I want. The After table is exactly what I’m looking for. It seems that my query is subtracting day_length , time_spent and dividing the result by 60. What I want is for it to group the time_spent, then subtract it for day_length , finally dividing it by 60. Does anyone know how to create a subquery to create the desire result? Please advice. Thanks.QuerySELECT SAMPLE.ID, SAMPLE.TIME_SPENT, SAMPLE.DATE, SAMPLE.DAY_LENGTH, ((SAMPLE. DAY_LENGTH - SAMPLE. TIME_SPENT)/60) AS TOTAL_AUMONTFROM SAMPLE;BEFORE ID TIME_SPENT DATE DAY_LENGTH TOTAL_AUMONT11516 60 27-Sep-07 510 7.511516 15 27-Sep-07 510 8.2511516 30 27-Sep-07 510 811846 30 27-Sep-07 480 7.511846 15 27-Sep-07 480 7.7511846 30 27-Sep-07 480 7.5 AFTER ID TIME_SPENT DATE DAY_LENGTH TOTAL_AUMONT11516 105 27-Sep-07 510 6.7511846 75 27-Sep-07 480 6.75 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-09-30 : 17:12:15
|
| SELECT SAMPLE.ID, TIME_SPENT = sum(SAMPLE.TIME_SPENT), SAMPLE.DATE, SAMPLE.DAY_LENGTH, (SAMPLE. DAY_LENGTH - sum(SAMPLE.TIME_SPENT))/60.0) AS TOTAL_AUMONTFROM SAMPLEgroup by SAMPLE.ID, SAMPLE.DATE, SAMPLE.DAY_LENGTH==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
SubQuery help