CODEWITHSUNDEEP
sqldatetimecountdate-range
RustyHamster
RustyHamster

Reputation: 359

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

Upvotes: 1

Views: 370

Answers (2)

VoteyDisciple
VoteyDisciple

Reputation: 37803

When you do COUNT(pm.DateAppIssued) in the last query, all you're counting are the rows that have a value for that field. There's absolutely no logic there about whether the date is in a particular range.

In fact, it's possible if none of the rows overlap (i.e., all the rows with passed in the right range are different from the rows with issued in the right range) and all the rows have a date, you'd actually get a number as big as 349 there (i.e., 126 + 223).

Solution: What's wrong with having two separate queries? You want two different numbers based on two different criteria, so you're selecting them separately. That's not bad design; it's perfectly sensible.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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)

Upvotes: 2

Related Questions