ANSI SQL doesn't provide mechanisms for creating data, only manipulating it. You'll have to provide the missing dates or all of the dates within the range. There are many implementations of this. You can create a table that contains one row for each day or maybe create a table that contains integers 1 - 31.
Here's a sample:
DECLARE @allDates TABLE (d datetime)
DECLARE @d datetime
SET @d = @ltQueryFrom
WHILE @d <= @ltQueryTo
BEGIN
INSERT INTO @allDates (d) VALUES (@d)
SET @d = DATEADD(dd, 1, @d)
END
Now you can take this table and UNION in the rows that weren't picked up by your other query.
-Mike
>I need to create a stored procedure that when passed the starting date and ending date, will return a result set that has one record for day of the month and a count for how many records where scheduled for that day, even if there are no records for that day.
>
>I can create a set now, grouped by date, but it will only return records for days where there are records. Days that don't have records are not return. On those days I need to return zero.
>
>
>declare @ltQueryFrom datetime
>declare @ltQueryTo dateTime
>declare @lnDays int
>
>set @ltQueryFrom = '10/01/2002 00:00:00'
>set @ltQueryTo = '10/31/2002 23:59:59'
>
>
>set @lnDays = datediff(dd,@ltQueryFrom,@ltQueryTo)
>
>select datepart(dd,meetings.begintime) as DayOfMonth,
> count(meetings.meetingnumber) as DayCount
>from meetings
>where begintime between @ltQueryFrom and @ltQueryTo
>group by datepart(dd,meetings.begintime)
>order by datepart(dd,meetings.begintime)
>
>
>
>
>Thanks for any ideas
>
>Kirk