Query question regarding aggregates over a date range

I have a data set where the structure could be like this

yes_no          date
  0             1/1/2011
  1             1/1/2011
  1             1/2/2011
  0             1/4/2011
  1             1/9/2011

Given a start data and and end date, I开发者_如何学Go would like to create a query where it would aggregate over the date and provide a 0 for dates that do not exist in the table, for dates between start_data and end_date including both

This is in SQL.

I am stumped. I can get the aggregate queries very simply, but i don't know how to get zeros for dates that do not exist in the table.

---

If you're working with a DBMS that supports common table expressions, the following will generate a derived table of dates that you can then left join to your table. This was written for MSSQL, so you may need to derive your dates differently (i.e., an object other than master..spt_values)

with AllDates as (
    select top 100000 
        convert(datetime, row_number() over (order by x.name)) as 'Date' 
    from 
        master..spt_values x 
        cross join master..spt_values y
)

select
    ad.Date, isnull(yt.yn, 0)
from
    AllDates ad
    left join (
        select date, sum(yes_no) yn
        from YourTable yt
    ) yt
        on  ad.date = yt.date
where
    ad.Date between YourStartDate and YourEndDate

---

Generating the dates has to be the way to go.

In ORACLE you could join on to a list of dates, why not..

(SELECT TRUNC(startdate + LEVEL) 
FROM DUAL CONNECT BY LEVEL <(enddate-startdate))

If you can't generate your dates on-the-fly a database agnostic solution would be to create a table containing all of the dates you will ever need and join on to that. (this should be your last resort)

here's the pseudeo code, you will need to substitute mydates for either the on-the fly sql or date table select

SELECT 
    CASE WHEN COUNT(b.date)=0 
    THEN 
        0 
    ELSE 
        1 
    END as yes_no
FROM (mydates) a
LEFT JOIN aggtable b ON a.date=b.date