SQL Server: Two table join show all rows where there's no data per day -

SQL Server: Two table join show all rows where there's no data per day -

i'm trying create study query shows given date range, names, dates, , if there's data. i'm close i'm @ loss on how these final 0 values in data. here's want:

name id mth day count auburn 7261 10 14 0 auburn 7261 10 15 0 auburn 7261 10 16 0 auburn 7261 10 17 0 auburn 7261 10 18 0 concord 7262 10 14 2 concord 7262 10 15 0 concord 7262 10 15 0 concord 7262 10 17 1 katey 7263 10 14 0 katey 7263 10 15 0 katey 7263 10 16 0 katey 7263 10 17 0 katey 7263 10 18 0

instead i'm getting:

name id mth day count auburn 7261 10 14 0 auburn 7261 10 15 0 auburn 7261 10 16 0 auburn 7261 10 17 0 auburn 7261 10 18 0 concord 7262 10 14 2 concord 7262 10 17 1 katey 7263 10 14 0 katey 7263 10 15 0 katey 7263 10 16 0 katey 7263 10 17 0 katey 7263 10 18 0

here's query:

select pue.name [name], pue.eventid, month(dates.date) month, day(dates.date) day, puc.idcount picturestaken ( select name, eventid events tourid = 444 , eventid > 7256 , eventid < 7323 ) pue outer apply ( select date = dateadd( day, n1.number * 10 + n0.number, '2014-10-14' ) (select 1 number union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) n0 cross bring together (select 1 number union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) n1 dateadd( day, n1.number * 10 + n0.number, '2014-10-14' ) between '2014-10-14' , '2014-10-18' ) dates left outer bring together ( select eventid, convert(date,picturetakendate) picdate, count(consumerdataid) idcount consumerdata eventid > 7256 , eventid < 7323 grouping eventid, convert(date,picturetakendate) ) puc on pue.eventid = puc.eventid puc.picdate = dates.date or puc.idcount null order pue.eventid, month(dates.date), day(dates.date)

i why zeros not showing names, here's outer joined table results:

7262 2014-10-14 2 7262 2014-10-17 1 7265 2014-10-14 2 7266 2014-10-14 2

so makes sense in clause not null or matching date. if take out clause row every count every date i.e.:

concord 7262 10 14 2 concord 7262 10 14 1 concord 7262 10 15 1 concord 7262 10 15 2 concord 7262 10 16 2 concord 7262 10 16 1 concord 7262 10 17 1 concord 7262 10 17 2 concord 7262 10 18 2 concord 7262 10 18 1

i'm close, know there just simple i'm missing each effort create prepare makes results worse, i'm here help. figure need wither prepare query in outer bring together somehow show every date , if reference dates apply easier or alter where. i've been unable figure out how.

add isnull status in select clause

select pue.name [name], pue.eventid, month(dates.date) month, day(dates.date) day, isnull(puc.idcount,0) picturestaken

convert clause on

on pue.eventid = puc.eventid , puc.picdate = dates.date

instead of

where puc.picdate = dates.date or puc.idcount null

sql sql-server join report outer-join