sql - How does one summarise data grouped by a column and date, accounting for dates with missing data -

i'm creating report i'd number of events grouped day , tag, filtered specific tags (like '%sample%') , specific period (e.g. past week).

i'm using sql server 2008.

there days when no events specific tag occurred. problem have how generate rows days no rows exist , give them value of zero. similar following:

tag                 date            count =================== =============== ==================== sample              2013-07-07      0 sample              2013-07-08      0 sample              2013-07-09      0 sample              2013-07-10      0 sample              2013-07-11      0 sample              2013-07-12      1 sample              2013-07-13      0 xxx sample xxx      2013-07-07      0 xxx sample xxx      2013-07-08      0 xxx sample xxx      2013-07-09      0 xxx sample xxx      2013-07-10      3 xxx sample xxx      2013-07-11      0 xxx sample xxx      2013-07-12      0 xxx sample xxx      2013-07-13      0 yyy sample yyy      2013-07-07      0 yyy sample yyy      2013-07-08      0 yyy sample yyy      2013-07-09      0 yyy sample yyy      2013-07-10      1 yyy sample yyy      2013-07-11      0 yyy sample yyy      2013-07-12      0 yyy sample yyy      2013-07-13      0 

the 0 days important in order render data in graphs, each "tag" own graph, time x-axis or count y-axis.

schema

the tags table follows:

create table tags (     [id] [int] identity(1,1) not null,     [name] [nvarchar](64) not null,     constraint [pk_tags] primary key clustered     (           [id] asc     ) on [primary] ) on [primary] 

and events table follows:

create table [dbo].[events](       [id] [int] identity(1,1) not null,       [message] [varchar](128) null,       [tagid] [int] not null,       [createdat] [datetime] null,     constraint [pk_events] primary key clustered     (       [id] asc     ) on [primary] ) 

where tagid foreign key tags table.

sample data

the events table has following data

id  message     tagid   createdat === =========== ======= ========================= 1   message 1   1       2013-07-10 18:46:04.967 2   message 2   2       2013-07-14 18:46:10.547 3   message 3   3       2013-07-12 18:46:15.190 4   message 4   4       2013-07-14 18:46:20.673 5   message 5   2       2013-07-14 18:46:28.133 8   message 6   1       2013-07-10 14:46:04.967 9   message 7   1       2013-07-10 12:46:04.967 10  message 6   2       2013-07-10 14:46:04.967  

and tags table has following data:

id  name === =========================== 3   sample 4   test1 5   test2 6   test3 1   xxx sample xxx 2   yyy sample yyy 

what i've tried

so, joined table getting following:

select tags.name, convert(date, events.createdat) date,count(*) count events inner join tags on events.tagid = tags.id tags.name '%sample%' group tags.name, convert(date, events.createdat) order tags.name, convert(date, events.createdat) 

which returned

name                date            count =================== =============== ================ sample              2013-07-12      1 xxx sample xxx      2013-07-10      3 yyy sample yyy      2013-07-10      1 yyy sample yyy      2013-07-14      2 

i searched ways generated rows days there no data. found entry sql server: how select days in date range if no data exists days unable work.

to verify got right days, ran following query:

with datetable (     select convert(date, dateadd(week, -1, getdate())) [date]     union     select dateadd(dd, 1, [date])     datetable     dateadd(dd, 1, [date]) < convert(date, getdate()) ) select datetable.date datetable 

which returned:

2013-07-07 2013-07-08 2013-07-09 2013-07-10 2013-07-11 2013-07-12 2013-07-13 

my first attempt work without specifying like '%sample%' in clause.

with datetable (     select convert(date, dateadd(week, -1, getdate())) [date]     union     select dateadd(dd, 1, [date])     datetable     dateadd(dd, 1, [date]) < convert(date, getdate()) ) select tags.name, dt.[date] date, count(events.id) count       events       inner join tags on tags.id = events.tagid right join [datetable] dt on dt.[date] = convert(date, events.[createdat]) tagid not null group tags.name, dt.[date] 

i following result:

name                date            count =================== =============== ================ xxx sample xxx      2013-07-10      3 yyy sample yyy      2013-07-10      1 sample              2013-07-12      1 

i have tried other things, changing right join left join, i'm unable desired result.

with datetable (     select convert(date, dateadd(week, -1, getdate())) [date]     union     select dateadd(dd, 1, [date])     datetable     dateadd(dd, 1, [date]) < convert(date, getdate()) ) select tags.name, dt.date date,count(events.id) count datetable dt cross join tags tags left join events on dt.[date] = convert(date, events.[createdat]) , tags.id = events.tagid tags.name '%sample%' group tags.name, date order tags.name, date; 

fiddle