All,
Please see the below query. I'm trying to run a report of how many users cancelled their subscription per day in a given reporting period. The report runs good, but skips days where a user did not cancel. I'd like the result to show 0, instead it skips that date. Please advise.
DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate = '7/1/2009', @EndDate = '9/1/2009'
SELECT Date = CAST(MAX(Convert(varchar(10), CancelDate, 101)) AS datetime), CancelledUsers = Count(*)
FROM MemberInfo
WHERE CancelDate BETWEEN @StartDate AND @EndDate
GROUP BY Year(CancelDate), Month(CancelDate), Day(CancelDate)
ORDER BY Year(CancelDate) DESC, Month(CancelDate) DESC, Day(CancelDate) DESC
Stephen