Posted by Plamen Ratchev on 02/08/07 21:58

Here are two ways to do that:

SELECT SUM(CASE WHEN RECORD_TYPE = '1' THEN 1 WHEN RECORD_TYPE = '2' THEN -1
ELSE 0 END) AS CountsDiff
FROM TABLE.RECORDS
WHERE DATEFIELD >= DATEDIFF(day, 0, getdate())
AND DATEFIELD < DATEDIFF(day, 0, getdate() + 1)


SELECT count1 - count2 AS CountsDiff
FROM (SELECT COUNT(*) AS count1
FROM TABLE.RECORDS
WHERE DATEFIELD >= DATEDIFF(day, 0, getdate()) AND DATEFIELD <
DATEDIFF(day, 0, getdate() + 1)
AND RECORD_TYPE = '1') AS C1,
(SELECT COUNT(*) AS count2
FROM TABLE.RECORDS
WHERE DATEFIELD >= DATEDIFF(day, 0, getdate()) AND DATEFIELD <
DATEDIFF(day, 0, getdate() + 1)
AND RECORD_TYPE = '2') AS C2

Regards,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]