Filling in missing data for a continous range, e.g. x-axis ..

Hi,

I want to display some stats using captured information. the x-axis is the date on which something occured. the y-axis is the number of occurance on that day.

The problem is that the x-axis need to be of continous range, so say from 1st Mar 2006 to 8th Mar 2006, I need to display every date within that range.

I've gathered the neccessary stats with a group statement but I was hoping I can fill in the gaps without having to loop through the returned data to identify missing dates, is there any built int SQL Server function which will allow me to do this within my SELECT statement?

Cheers
james
`I dont think there is such a inbuilt function available in sql server. But if you could post the ddl ,some sample data and the expected result, i m sure someone here would definately be able to help you.|||One way to do this is to enumerate the values that you need, something like:SELECT d
FROM (SELECT Convert(DATETIME, '2006-03-01') AS d
UNION SELECT '2006-03-02'
UNION SELECT '2006-03-03'
UNION SELECT '2006-03-04'
UNION SELECT '2006-03-05'
UNION SELECT '2006-03-06'
UNION SELECT '2006-03-07'
UNION SELECT '2006-03-08') AS z-PatP|||Or more generically, create a table of sequential numbers in your database starting with zero, from which you can dynamically create any sequence of dates. Like this:select XValues.XValue,
YourData.YValue
from --XValues
(select dateadd(day, SeqValue, '2006-01-01') as XValue
from SequentialNumbers) XValues
left outer join YourData on XValues.XValue = YourData.XValue|||Most times that I have need to do something like this, I have generated a calendar master table with dates and perhaps some bit flags to indicate other generic data about that date (eg, weekends, public holidays, non-working day).

Then I can join (or outer join) my recordset to the calendar master table for reporting purposes.

Regards,

hmscott|||Most times that I have need to do something like this, I have generated a calendar master table with dates and perhaps some bit flags to indicate other generic data about that date (eg, weekends, public holidays, non-working day).

Then I can join (or outer join) my recordset to the calendar master table for reporting purposes.

Regards,

hmscott

Thank you all
I think blindman and htmscott have described pretty much the same method.
I will give these a try and see how it goes.

Cheers
James|||Thanks again guys, the method suggested by you guys works great.

Cheers
Jamessql