How to count total receipts by date

Question

Would like to know why do I keep getting the error message?

Created query with SQL Server Management Studio, my output to display correctly has parameters, and I have tried the following with my query.

What I really would like is to have a specific start and end dates and change selected dates with any other combination of MinDate, MaxDate as follows:

@MinDate DATE = '2020-01-01',
@MaxDate DATE = '2020-02-01';

Totals:

COUNT(Receipt_ID) AS 'Total_Number_Of_Receipts',
SUM(Receipt_Amount) AS 'Total_Amount'

This is the error I get:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '.'.

Msg 8158, Level 16, State 1, Line 5
'Receipt_Date' has more columns than were specified in the column list.

Query:

DECLARE 
    @MinDate DATE = '2020-01-01',
    @MaxDate DATE = '2020-02-01';

WITH Receipt_Date(day) AS
(
    SELECT,
        DATEADD(d, -1, GETDATE()) AS 'Yesterday',
        COUNT(Receipt_ID) AS 'Total_Number_Of_Receipts',
        SUM(Receipt_Amount) AS 'Total_Amount'
    FROM 
        receipts AS r
    WHERE 
        CAST(DATEADD(day, 1, day) AS Date) < @MaxDate
)
SELECT * 
FROM receipts AS r

Answer 1

A few things:

1: You've got a comma after the select inside your CTE

SELECT, --<< here
        DATEADD(d, -1, GETDATE()) AS 'Yesterday',

2: You need to match the definition of the CTE with the columns being returned, so you can't just have 'day' if you're returning 4 columns in the CTE

3: In your select statement you need to reference the CTE.

This returns, but I've no example data:

DROP TABLE IF EXISTS #receipts;

CREATE TABLE #receipts
(
Receipt_ID INTEGER IDENTITY(1,1),
d DATE,
Receipt_Amount MONEY
);

DECLARE 
    @MinDate DATE = '2020-01-01',
    @MaxDate DATE = '2020-02-01';

WITH Receipt_Date(Yesterday, Total_Number_Of_Receipts, Total_Amount) AS
(
    SELECT
        DATEADD([day], -1, GETDATE()) AS 'Yesterday',
        COUNT(Receipt_ID) AS 'Total_Number_Of_Receipts',
        SUM(Receipt_Amount) AS 'Total_Amount'
    FROM 
        #receipts AS r
    WHERE 
        CAST(DATEADD(day, 1, d) AS Date) < @MaxDate
    GROUP BY DATEADD([day], -1, GETDATE())
)

SELECT * 
FROM Receipt_Date;

Answer 2

Based on your requirements, you can approach the solution by using a recursive CTE to help generate the list of dates which fall between a MinDate and MaxDate.

DECLARE @MinDate DATE = '2020-01-01', @MaxDate DATE = '2020-02-01';

;WITH Receipts_CTE(receipt_date) AS
(
    SELECT @MinDate
    UNION ALL
    SELECT DATEADD(DAY, 1, receipt_date)
    FROM Receipts_CTE
    WHERE receipt_date < @MaxDate
)

This will recursively call itself to add 1 day to the running receipt_date column until it reaches the specified MaxDate.

With this CTE building out all the necessary dates, you can then use the dates from this CTE to JOIN against your source table to build out the necessary aggregate.

Without knowing the schema of the receipts table in question, the below is merely used for illustration purposes:

SELECT receipts.Receipt_Date,
       COUNT(receipts.Receipt_ID) AS TotalReceipts,
       SUM(receipts.Receipt_Amount) AS TotalAmount
FROM Receipts receipts
INNER JOIN Reciepts_CTE cte ON cte.receipt_date = receipts.Receipt_Date
GROUP BY receipts.Receipt_Date