RustyHamster RustyHamster - 4 months ago 9
SQL Question

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

vkp vkp
Answer

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)