We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
--orginal query....notice that the assigned, pending and then Available values are coming from a table in this query....I need them to come from the bottom two queries below...I just think there is a better way to join all of this up for speed. This database is sql server 2000. SELECT A.ACN, A.AUTHORIZED, AA.ASSIGNED, AA.PENDING, (A.AUTHORIZED - (AA.ASSIGNED + AA.PENDING)) As Available, AR.FILE_NAME FROM ACN A INNER JOIN ACN_ACCOUNT AA ON A.ACN = AA.ACN INNER JOIN ACN_REQUEST AR ON A.ACN = AR.ACN ---assigned SELECT COUNT(SERIAL_NUMBER) AS ASSIGNED, ACN, ACCOUNT_ID FROM dbo.ACN_SAASM GROUP BY ACN, ACCOUNT_ID --pending SELECT TOP 100 PERCENT s.ACN, SUBSTRING(sd.SHIPMENT_ID, 1, 7) AS ACCOUNT_ID, COUNT(sd.SERIAL_NUMBER) AS PENDING FROM dbo.SHIPMENT_DETAIL AS sd INNER JOIN dbo.SHIPMENT AS s ON sd.SHIPMENT_ID = s.SHIPMENT_ID LEFT OUTER JOIN dbo.ACN_SAASM AS A ON SUBSTRING(sd.SERIAL_NUMBER, 4, 11) = A.SERIAL_NUMBER AND SUBSTRING(sd.SHIPMENT_ID, 1, 7) = A.ACCOUNT_ID WHERE (A.SERIAL_NUMBER IS NULL) AND (sd.SHIP_STATUS_CODE <> 1) AND (LEN(s.ACN) = 11) GROUP BY SUBSTRING(sd.SHIPMENT_ID, 1, 7), s.ACN ORDER BY s.ACN, SUBSTRING(sd.SHIPMENT_ID, 1, 7)