QUERY HELP! - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

QUERY HELP!

Author

Topic

BigRetina
Posting Yak Master

144 Posts

Posted - 2003-01-29 : 08:38:01

I have the following rows in the following tables
Clients (table)
ClintId BlockedBatches
-------- -------------
1 10000

Blocks(table)
BlockId ClientId NoOfBatches
------- --------- -------------
1 1 20000
2 1 20000

Releases(table)
ReleaseId BlockId NoOfBatches
------- ------- --------
5 1 5000
6 1 10000
7 1 5000
8 2 10000

NOW...I am doing an alignment check..as follows :
SELECT ClientId, a.BlockedBatches,
SUM(b.NoOfBatches) AS TotalBlocked,
SUM(c.NoOfBatches) AS TotalReleased
FROM Clients a
LEFT Join Blocks b ON (a.ClientId= b.ClientId)
LEFT Join Releases c ON (b.BlockId= c.BlockId)
GROUP BY a.ClientId, a.BlockedBalance
HAVING a.BlockedBatches <> (sum(b.NoOfBatches) -sum(c.NoOfBatches))

THIS QUERY is showing the WRONG result :

ClintId BlockedBatches TotalBloacked TotalReleased
------- ------------- ------------- --------------
1 10000 80000 30000
THE 80000 IS WRONG!..as U can see!..why?
Thanks ALOT

Edited by - bigretina on 01/29/2003 08:39:59

Edited by - bigretina on 01/29/2003 08:40:53

Edited by - bigretina on 01/29/2003 09:58:19

Edited by - bigretina on 01/29/2003 10:33:53

SamC
White Water Yakist

3467 Posts

Posted - 2003-01-29 : 09:44:06

You don't show column ClientID in table Blocks -

Where is BlockedBalance defined? Maybe it's BlockedBatches?

It would help if you posted the wrong result and the right result.

Taking a guess....

The second LEFT OUTER JOIN may not be happening on some rows because the first join is NULL sometimes?

If that's not it, could you post correct and incorrect values based on the sample data you have listed? Sometimes data can hint at the problem. For example are the wrong values consistantly low or zero?

Sam

Edited by - SamC on 01/29/2003 09:49:32

BigRetina
Posting Yak Master

144 Posts

Posted - 2003-01-29 : 09:59:49

Thanks..
I altered the Blocks table. Ofcourse, it contains the ClientId..i corrected it. Sorry!.
And no there are no NULLS in the tables!

SamC
White Water Yakist

3467 Posts

Posted - 2003-01-29 : 10:09:45

I haven't tried this, but.. (assuming ClientID is DISTINCT in table Clients)

SELECT a.ClientID, a.BlockedBatches ,
(SELECT SUM(NoOfBatches)FROM Blocks b where b.ClientID=a.ClientID) as TotalBlocked ,
(SELECT SUM(NoOfBatches) FROM Releases r WHERE r.ClientID=a.ClientID) as TotalReleased
FROM Clients a

You didn't clarify where BlockedBalance comes from, but assuming it's a.BlockedBatches, the filtered recordset would be

SELECT ClientId, BlockedBatches, TotalBlocked, TotalReleased
FROM
(

SELECT a.ClientID, a.BlockedBatches ,
(SELECT SUM(NoOfBatches)FROM Blocks b where b.ClientID=a.ClientID) as TotalBlocked ,
(SELECT SUM(NoOfBatches) FROM Releases r WHERE r.ClientID=a.ClientID) as TotalReleased
FROM Clients a
)
WHERE BlockedBatches <> (TotalBlocked - TotalReleased)

The outer select may not be needed to add the WHERE. I threw it in just in case.

Anyone know a way to get this result using GROUP BY ?

Sam

Edited by - SamC on 01/29/2003 10:23:53

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources