How to combine a date series with my regular selection?-CodePudding

I am using the following MSSQL query to count timestamps within a table. Each row stands for one transaction (one car washed) at my carwash company.

SELECT
    count(st.date) as NumberOfWashes,
    cast(st.date as date) as DayOfWashes
FROM 
    POS.dbo.sales_transaction_line_item as stli
    join POS.dbo.sales_transaction as st on st.sales_transaction_id = stli.fk_sales_transaction
    join POS.dbo.sales_item as si on si.sales_item_id = stli.fk_sales_item
WHERE
    st.fk_sales_status <> 3
    and si.fk_sales_item_type = 1
    and st.date BETWEEN @start_date and @end_date
Group by
    cast(st.date as date)
order by
    cast(st.date as date) desc

I am using the two joins to eliminate cancelled washes (sales_status) and transactions which sell products but no car wash (sales_item_type).

The result of this list looks like:

NumberofWashes DayOfWashes
42             2023-01-26
71             2023-01-25
57             2023-01-24
87             2023-01-23
104            2023-01-21
114            2023-01-20

As you can see the Date 2023-01-22 is missing (it's a sunday and we are closed). However, I need that day as well with 0 washes.

Therefore I have the code like this:

DECLARE @start_date DATE = '2021-01-26';
DECLARE @end_date DATE = '2023-01-27';

WITH AllDays
AS ( SELECT   @start_date AS [Date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date])
    FROM AllDays
    WHERE [Date] < @end_date )
SELECT [Date]
FROM   AllDays 
OPTION (MAXRECURSION 0)

It produces a simple list with all dates:

How can I combine those two statements so the DayOfWashes includes all available dates?

CodePudding user response：

Just combine the 2 queries in a nice way, something like this:

DECLARE @start_date DATE = '2021-01-26';
DECLARE @end_date DATE = '2023-01-27';

WITH AllDays
AS ( SELECT   @start_date AS [Date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date])
    FROM AllDays
    WHERE [Date] < @end_date ),
WashData as (
    SELECT
        count(st.date) as NumberOfWashes,
        cast(st.date as date) as DayOfWashes
    FROM 
        POS.dbo.sales_transaction_line_item as stli
        join POS.dbo.sales_transaction as st on st.sales_transaction_id = stli.fk_sales_transaction
        join POS.dbo.sales_item as si on si.sales_item_id = stli.fk_sales_item
    WHERE
        st.fk_sales_status <> 3
        and si.fk_sales_item_type = 1
        and st.date BETWEEN @start_date and @end_date
    Group by
        cast(st.date as date)
)
SELECT 
  [Date]
  ,NumberOfWashes
FROM   AllDays ad
left join WashData wd
  on ad.[Date] = wd.DayOfWashes
OPTION (MAXRECURSION 0)