There isn't really a build-in way in SQL to create sequential data like a range of dates (unless you're using SQL Server 2022 or higher, then you have GENERATE_SERIES
, which was recently added). Otherwise you have to accept the fact that there will be gaps for dates where there were no created or completed orders.
I've seen a few solutions to overcome that problem (see this Stackoverflow question). My solution to your problem:
with dates(Date) as (
select cast(GETDATE() as date)
union all
select dateadd(day, -1, Date)
from dates
where Date >= dateadd(day, -30, cast(GETDATE() as date))
), created_orders as (
select
cast(CreatedAt as Date) as CreatedAt,
count(*) as CreatedCount
from [tt].[order]
group by cast(StartDate as date)
), completed_orders as (
select
cast(CompletedAt as Date) as CompletedAt,
count(*) as CompletedCount
from [tt].[order]
group by cast(StartDate as date)
)
select
d.Date,
isnull(cr.CreatedCount, 0) as Created,
isnull(co.CompletedCount, 0) as Completed
from dates d
left join created_orders cr on cr.CreatedAt = d.Date
left join completed_orders co on co.CompletedAt = d.Date
Dates CTE is an example solution to fill the gaps. As you can see I created separate CTEs for created and completed orders. You cannot group by both dates at the same time as the output would be a cartesian product of those two columns. You need to group by them separately and only later join the results.