SQL Server - Another quick recordset problem

Asked By GTN17077

19-Aug-08 10:13 AM

I had a query that was working fine, however i had to add another field
(JBOOrderStatus) to one of the tables, now the query doesn't work -the
original query is -

SELECT C.JBCLTokens    - COALESCE((SELECT SUM(O.JBOTokens) FROM JBOrder O
WHERE C.JBCLID =  O.JBOClientID),0)    + (SELECT COUNT(*) FROM JBAdvert A
WHERE C.JBCLID = A.JBAClientID)  As CurTokens
FROM JBClient C
WHERE C.JBCLID = MMColParam

I have added JBOOrderStatus to the JBOrder table, now ~~I'm trying to run
the same query, but only selecting the sum of JBOTokens where JBOOrderStatus
= 'OK'

has anyone got any ideas? thanks all

19-Aug-08 11:02 AM

SELECT C.JBCLTokens    - COALESCE((SELECT SUM(O.JBOTokens) FROM JBOrder O
WHERE C.JBCLID =  O.JBOClientID AND O.JBOOrderStatus = 'OK'),0)    + (SELECT
COUNT(*) FROM JBAdvert A
WHERE C.JBCLID = A.JBAClientID)  As CurTokens
FROM JBClient C
WHERE C.JBCLID = MMColParam

Tom

19-Aug-08 11:18 AM

Thanks Tom