Re: Using SELECT to fill in missing entries?

On 4 Sep 2004 09:24:25 -0700, laredotornado_at_gmail.com (D. Alvarado) wrote:

>Hello, I am running Oracle 8.1.7 on Solaris 8. I have a table that
>records the number of registrants per day. Here is an example of some
>data.
>
>DAY NUM_REGISTRANTS
>--- ---------------
>2004-07-01 2
>2004-07-04 5
>2004-07-06 3
>
>When a day is not present, it means there were 0 registrants on that
>day. Does anyone know, given a beginning and ending date, how to form
>a SELECT statement such that I can select entries for days even if no
>entries are in the table? That is, given the dates 2004-07-01 and
>2004-07-06, how would I form a SELECT statement that would produce the
>output ...
>
>DAY NUM_REGISTRANTS
>--- ---------------
>2004-07-01 2
>2004-07-02 0
>2004-07-03 0
>2004-07-04 5
>2004-07-05 0
>2004-07-06 3
>
>? Thanks for your help, - Dave

Not possible without an extra table with dates. Or you need to write a pl/sql function to loop to all the dates to be checked (will be inefficient).
Issue comes up now and then, a Google search shouldn't come up blank

--
Sybrand Bakker, Senior Oracle DBA