更新时间:2022-10-18 10:24:12
Here is a solution without a calendar table (which is a must in production). You might have date range in variables, or you might go for min() and max() from the_table
.
EDIT: shorter version incorporating categories into date range generation
declare @startdate datetime = '2012-1-1'
declare @enddate datetime = '2012-1-5'
; with dates([date], category) as (
select distinct @startdate, category
from the_table
union all
select dateadd (day, 1, [date]), category
from dates
where [date] < @enddate
)
select dates.date,
dates.category,
isnull(the_table.amount, 0) Amount
from dates
left join the_table
on dates.date = the_table.date
and dates.category = the_table.category
order by dates.category, dates.date
option (maxrecursion 0)