c# - I am storing date in my table.I want to select data and in that if there is no date in table then corresponging field to date should be 0 -

tables : collegereview,

fields - collegeid ,review desc, reviewdate,

i have created report table shows:

date          no of reviews 12-jan-2012         10 13-jan-2012         8 15-jan-2012         2 

this query not printing date - 14-jan-2012

i want report like...

date          no of reviews 12-jan-2012         10 13-jan-2012         8 14-jan-2012         0 15-jan-2012         2 

there date field in table. if there no entry particular date should return 0.

sql query

select convert(varchar(20),cast(reviewdate date),100)as 'date',count(*) 'review(s)' reviews  group convert(varchar(20),cast(reviewdate date),100) order convert(varchar(20),cast(reviewdate date),100) desc 

first need create additional table dates want.

create table dates ([date] date)   declare @stdate date, @enddate date, @insertdate date set @stdate='01.01.2000';--choose possible dates set @enddate='01.01.2020'; set @insertdate=@stdate;  while (@insertdate<@enddate) begin insert dates values(@insertdate) set @insertdate=dateadd(d,1,@insertdate) end 

now can write query using right join dates table

select dates.[date] 'date', count(*) 'review(s)'  reviews right join dates on dates.[date]=cast(reviewdate date) dates.[date]>'01.01.2014' , dates.[date]<'01.02.2014' --use dates here     group dates.[date], cast(reviewdate date)     order dates.[date] desc 

this additional table quite useful. may create table in master db , use in reports well.