79476203

Date: 2025-02-28 16:57:26
Score: 0.5
Natty:
Report link

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.

Reasons:
  • Blacklisted phrase (1): Stackoverflow
  • Long answer (-1):
  • Has code block (-0.5):
  • Low reputation (1):
Posted by: user29644178