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 |
|
eugz
Posting Yak Master
210 Posts |
 Posted - 2007-08-22 : 14:35:52
|
| Hi All.I have Request, Device and Receive table. All table has DeviceID. I create query to calculate total amount of each type of devices which was requested by link Request and Device tables. And query to calculate total amount of each type of devices which was received by link Receive and Device tables. I would like to create Balance query to calculate:Total_Receive - Total_Request = BalanceIf devices in Request and Receive table is the same result is correct. But if in Receive table add new type of devices I cannot see amount of new device in Balance query. How to fix that problem?Thanks. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-22 : 14:41:04
|
[code]SELECT DeviceID, SUM(Receive) AS Receive, SUM(Request) AS Request, SUM(Receive) - SUM(Request) AS BalanceFROM ( SELECT DeviceID, 0 AS Receive, 0 AS Request FROM Device UNION ALL SELECT DeviceID, Amount, 0 FROM Receive UNION ALL SELECT DeviceID, 0, Amount FROM Request ) AS dGROUP BY DeviceIDORDER BY DeviceID[/code]
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-22 : 14:44:48
|
[code]SELECT d.DeviceID, COALESCE(Receive, 0) AS Receive, COALESCE(Request, 0) AS Request, COALESCE(Receive, 0) - COALESCE(Request, 0) AS BalanceFROM Device AS dLFET JOIN ( SELECT DeviceID, SUM(Amount) AS Receive FROM Receive GROUP BY DeviceID ) AS r ON r.DeviceID = d.DeviceIDLEFT JOIN ( SELECT DeviceID, SUM(Amount) AS Request FROM Request GROUP BY DeviceID ) AS s ON s.DeviceID = d.DeviceIDORDER BY d.DeviceID[/code]
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
|
|
calculate balance query