I have a table called Registrations with the following fields:
- Id
- DateStarted (not null)
- DateCompleted (nullable)
I have a bar chart which shows the number of registrations started and completed by date. My query looks like:
;
WITH Initial(DateStarted, StartCount)
as (
select Datestarted, COUNT(*)
FROM Registrations
GROUP BY DateStarted
)
select I.DateStarted, I.StartCount, COUNT(DISTINCT R.RegistrationId) as CompleteCount
from Initial I
inner join Registrations R
ON (I.DateStarted = R.DateCompleted)
GROUP BY I.DateStarted, I.StartCount
which returns a table that looks like:
DateStarted StartCount CompleteCount
2009-08-01 1033 903
2009-08-02 540 498
The query just has one of those code smell problems. What is a better way of doing this?