Q:

Count Based on 2 date Ranges for 2 different columns

Having an issue writing a report.
I am trying to count on the number of enquiries Issued and passed, they both have a datetime field
The problem im having is the results are coming back incorrect.
Running this code gives me  126 for passed
SELECT COUNT(*) AS Passed FROM BPS.dbo.tbl_Profile AS p
Inner Join  BPS.dbo.tbl_Profile_Mortgage AS pm
ON p.Id = pm.FK_ProfileId
WHERE p.CaseTypeId IN (1,2,9,15) AND CONVERT(DATE,pm.DatePassed,103) BETWEEN @Start AND @End

When i run the issued query I get 223
SELECT COUNT(*) AS Issued FROM BPS.dbo.tbl_Profile AS p
Inner Join  BPS.dbo.tbl_Profile_Mortgage AS pm
ON p.Id = pm.FK_ProfileId
WHERE p.CaseTypeId IN (1,2,9,15) AND CONVERT(DATE,pm.DateAppIssued,103) BETWEEN @Start AND @End

These figures are correct so i put it in one query like so.
SELECT COUNT(pm.DateAppIssued) AS Issued,COUNT(pm.DatePassed) AS Passed FROM BPS.dbo.tbl_Profile AS p
Inner Join  BPS.dbo.tbl_Profile_Mortgage AS pm
ON p.Id = pm.FK_ProfileId
WHERE p.CaseTypeId IN (1,2,9,15) 
AND (CONVERT(DATE,pm.DateAppIssued,103) BETWEEN @Start AND @End
OR CONVERT(DATE,pm.DatePassed,103) BETWEEN @Start AND @End)

This gives me Issued 265 and passed 185
I have tried many different variation but still cant get the correct figures
I hope i have explained this well enough, any help would be much appreciated.
Rusty

A:

Because you have the both the conditions in the where clause with an or condition, you are seeing a different result. Use them in the aggregation itself.
SELECT 
COUNT(case when CONVERT(DATE,pm.DateAppIssued,103) BETWEEN @Start AND @End then 1 end) AS Issued,
COUNT(case when CONVERT(DATE,pm.DatePassed,103) BETWEEN @Start AND @End then 1 end) AS Passed 
FROM BPS.dbo.tbl_Profile AS p
Inner Join  BPS.dbo.tbl_Profile_Mortgage AS pm ON p.Id = pm.FK_ProfileId
WHERE p.CaseTypeId IN (1,2,9,15) 

