November 21, 2019 at 3:49 pm
I'm trying to find the correct way of using a subquery count to subtract from main query count... thanks for any help!:
SELECT count(p.[peopleId]) AS 'Inactives'
FROM [BCC_DB].[dbo].[People] p
--MINUS
(SELECT count(p.[peopleId]) AS 'Actives'
FROM [BCC_DB].[dbo].[People] p
INNER JOIN
[BCC_DB].[dbo].[certs] c
ON p.peopleId = c.peopleId
WHERE p.status = 1
AND c.certificationDate Is Not Null
AND c.certStatusID = 3
and p.ethicsflag = 0
and p.ethicshold = 0)
November 21, 2019 at 4:01 pm
SELECT count(c.people_id) AS 'Inactives' --<<-- note counting from *right* table
FROM [BCC_DB].[dbo].[People] p
LEFT OUTER JOIN --<<-- note *LEFT* join
[BCC_DB].[dbo].[certs] c
ON p.peopleId = c.peopleId
WHERE p.status = 1
AND c.certificationDate Is Not Null
AND c.certStatusID = 3
and p.ethicsflag = 0
and p.ethicshold = 0
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 21, 2019 at 4:09 pm
I think this is a direct conversion of your SQL, just subtracting 2 inline SQL statements:
SELECT (SELECT count(p.[peopleId])
FROM [BCC_DB].[dbo].[People] p)
- (SELECT count(p.[peopleId])
FROM [BCC_DB].[dbo].[People] p
INNER JOIN [BCC_DB].[dbo].[certs] c
ON p.peopleId = c.peopleId
AND c.certificationDate Is Not Null
AND c.certStatusID = 3
WHERE p.status = 1
AND p.ethicsflag = 0
AND p.ethicshold = 0) AS Count
This is probably more efficient than two inline selects, but I'm not sure how it will compare to Scott's answer.
SELECT COUNT(p.[peopleId]) AS [Count]
FROM [BCC_DB].[dbo].[People] p
WHERE NOT EXISTS(SELECT *
FROM [BCC_DB].[dbo].[certs] c
WHERE c.peopleId = p.peopleId
AND c.certificationDate Is Not Null
AND c.certStatusID = 3
AND p.status = 1
AND p.ethicsflag = 0
AND p.ethicshold = 0)
November 21, 2019 at 4:13 pm
My problem is this part/count gives the correct figure of 4,090:
SELECT count(p.[peopleId]) AS 'Inactives'
FROM [BCC_DB].[dbo].[People] p
And this gives the correct figure of 2,126:
(SELECT count(p.[peopleId]) AS 'Actives'
FROM [BCC_DB].[dbo].[People] p
INNER JOIN
[BCC_DB].[dbo].[certs] c
ON p.peopleId = c.peopleId
WHERE p.status = 1
AND c.certificationDate Is Not Null
AND c.certStatusID = 3
and p.ethicsflag = 0
and p.ethicshold = 0)
And my difference should come out to 1,964
November 21, 2019 at 4:15 pm
Thanks Jonathan it worked!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply