SQL Statement needed for month range.

[jexley]

Hello my fellow geeklings,
I've got an app that needs to chart a bit of data for every day of a selected month. If I had date, text and number, May would be like:
01-05-2009-"Anthony"-4 | 01-05-2009-"Joe"-3 | 01-05-2009-"Mark"-5
02-05-2009-"Anthony"-5 | 02-05-2009-"Joe"-6 | 02-05-2009-"Mark"-8
03-05-2009-"Anthony"-2 | 03-05-2009-"Joe"-4 | 03-05-2009-"Mark"-6

Now, what I've got is easy enough to do that, but I run into problems when I graph it and there's days missing. Like so:
01-05-2009-"Anthony"-4 | 01-05-2009-"Joe"-3 | 01-05-2009-"Mark"-5
03-05-2009-"Anthony"-2 | 03-05-2009-"Joe"-4 | 03-05-2009-"Mark"-6

The graph that I've got isn't smart enough to see that there's a gap, it just displays the value in the graph for "Anthony" as "2" right after the "4" with no "0" in between.

Any ideas on a SQL statement that can pull back a date range, like data for all 30+ days in a month, but plug in 0 where there actually is no data?

Oh, ftr my SQL is:
select b.Date, a.Name, b.Hits from Contacts a, Occurences b
where a.CompanyID = 61
and a.ContactID = b.ContactID
and b.Date BETWEEN '2009-05-01' AND '2009-05-31'
order by a.ContactID, b.DateCheers for the help kids, much appreciated.

[heist]

can you use a temp table?...

CREATE TABLE #calendar
(
dt SMALLDATETIME NOT NULL
PRIMARY KEY CLUSTERED,
)
GO

--insert all the days you need for the report

DECLARE @dt SMALLDATETIME
SET @dt = '20090101' -- your start date
WHILE @dt < '20090531' -- your end date
BEGIN
INSERT #calendar(dt) SELECT @dt
SET @dt = @dt + 1
END


-- Then join into a selection of all the calendar table records

SELECT COUNT(b.[whatever]), Day(#calendar.dt)
FROM #calendar
LEFT JOIN b ON convert(varchar, #calendar.dt, 112) = convert(varchar, b.[Date], 112)
WHERE #calendar.dt >= '2009-05-01'
AND #calendar.dt <= '2009-05-31'
GROUP BY Day(#calendar.dt)

[Basil]

If you need to do it in a single query you could do something like...

select *,cast('01/01/2008' as datetime) + num from
(select 0 as num union
select 1 as num union
select 2 as num union
.....
select 29 as num union
select 30 as num union
select 31 as num) daytable
left join tablename
on cast('01/01/2008' as datetime) + num = tablename.datefield
where ....


With 01/01/2008 being the first day of the month

Depending on the size of your tables you might want to re-structure it for performance optimisation

[jexley]

Those are both stellar suggestions, especially in light of the shit I need to do to it afterward, with putting it into arrays and such.

Thanks heaps for that, and I'm going to give the cast/union thingo a go. You're both rockstars and deserve non-groping hugs (heist gets groped after hugs).