CODEWITHSUNDEEP
sqldate
LogisticsRay
LogisticsRay

Reputation: 1

How to logically populate dates as 1st column, then have metrics in additional columns?

There must be a better way to do this... I am seeking to show Column 1 as Dates (yyyy-mm-dd) from today through the last 30 days, 31 days total. Column 2 to show the number of records created on that date (CreatedAt), and Column 3 to show number of records completed on that date (Completed At).

When using Group By within a single query (non-Union), I end up getting a row for each date for each record. I.e. if I created 4 records today, then I would have 4 rows showing 4 records created with today's date. My method below works, but I think there must be a better way rather than creating 31 dates & 31 queries and utilizing UNION.

I cannot create any temp tables, only read data. Example below is sample for just today and yesterday.

DECLARE @day1 AS DATE
DECLARE @day2 AS DATE
SET @day1 = GETDATE()
SET @day2 = (GETDATE() - 1)



SELECT
        @day1 AS 'Date'
       ,(SELECT COUNT(OrderId) FROM [tt].[order] as subttOrder
       WHERE CAST(subttOrder.CreatedAt AS DATE) = @day1 AND subttOrder.OrderStatus NOT IN (7)) AS 'Booked Orders'
       ,(SELECT COUNT(OrderId) FROM [tt].[order] as subttOrder
       WHERE CAST(subttOrder.CompletedAt AS DATE) = @day1 AND subttOrder.OrderStatus NOT IN (7)) AS 'Completed Orders'

FROM [tt].[order] as ttOrder

WHERE ttOrder.OrderStatus NOT IN (7)
AND (CAST(ttOrder.CreatedAt AS DATE) = @day1) 
GROUP BY CAST(ttOrder.CreatedAt AS DATE), CAST(ttOrder.CompletedAt AS DATE)

UNION

SELECT
        @day2 AS 'Date'
       ,(SELECT COUNT(OrderId) FROM [tt].[order] as subttOrder
       WHERE CAST(subttOrder.CreatedAt AS DATE) = @day2 AND subttOrder.OrderStatus NOT IN (7)) AS 'Booked Orders'
       ,(SELECT COUNT(OrderId) FROM [tt].[order] as subttOrder
       WHERE CAST(subttOrder.CompletedAt AS DATE) = @day2 AND subttOrder.OrderStatus NOT IN (7)) AS 'Completed Orders'

FROM [tt].[order] as ttOrder

WHERE ttOrder.OrderStatus NOT IN (7)
AND (CAST(ttOrder.CreatedAt AS DATE) = @day2) 
GROUP BY CAST(ttOrder.CreatedAt AS DATE), CAST(ttOrder.CompletedAt AS DATE)


ORDER BY 'Date' DESC

Sample data from tt.order:

OrderId CompletedAt CreatedAt
180 2025-02-28 08:38:22.0893048 -06:00  2025-02-24 07:52:55.7829466 -06:00
167 2025-02-28 08:31:42.0014695 -06:00  2025-02-21 12:15:40.4321509 -06:00
87  2025-02-28 08:27:44.3881454 -06:00  2025-02-18 14:57:23.3019395 -06:00
219 2025-02-28 08:23:21.9861254 -06:00  2025-02-25 13:03:16.5607504 -06:00
64  2025-02-27 13:45:12.2144778 -06:00  2025-02-17 16:24:09.4820968 -06:00

Sample output:

Date    Booked Orders   Completed Orders
2025-02-28  7   4
2025-02-27  40  18

Upvotes: -3

Views: 63

Answers (1)

user29644178
user29644178

Reputation: 48

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.

Upvotes: 3

Related Questions