I have a table with order date, type of order and paymentamount

I have a date range with startdate as 2021-02-01 and Enddate as 2021-02-30

I am trying to create a stored proc to calculate SUM(PaymentAmount) by Type for the date range if it matches the order date to get below result

......

| 2021-02-30 | $200 | $0 |

Declare @StartDate DATETIME2 ='2021-02-01'
Declare @EndDate DATETIME2 ='2021-02-30'

;WITH Dates(RevenueDate) AS 
    (
        SELECT CAST(@StartDate as Date) as day
        UNION ALL
        SELECT CAST(DATEADD(day, 1, RevenueDate) as Date) as day
        FROM Dates
        WHERE CAST(DATEADD(day, 1, RevenueDate) as Date) <= @EndDate
    ),
CTE_Revenue AS
    (
    
    SELECT 
         Type
        , OrderDateTime             
        , CASE
                WHEN Type = 'Delivery' THEN SUM(PaymentAmount)
                WHEN Type = 'Online'  THEN SUM(PaymentAmount) 
                ELSE 0
          END                                                   AS  Revenue     
        FROM OrderInfo      
        WHERE               
                Type = 'Delivery' OR Type ='Online'             
                AND [P].[DeletedFlag] = 0
        GROUP BY                
                 Type               
                , OrderDateTime                         
        )

        SELECT * FROM Dates LD 
        LEFT JOIN CTE_Revenue CS ON FORMAT(LD.RevenueDate, 'MM/dd/yyyy') = CS.OrderDateTime
        ORDER BY CONVERT(DATE, [RevenueDate]) ASC
        OPTION (maxrecursion 0)

I am getting below result

| RevenueDate | Delivery     | Online |
|:----        |:------:      | -----: |
| 2021-02-01  |    NULL      | NULL   |
| 2021-02-02  |    NULL      | NULL   |
| 2021-02-03  |    NULL      | NULL   |
| 2021-02-04  |    NULL      | NULL   |
| 2021-02-05  |    $500      | $0     |
| 2021-02-05  |    $0        | $3000  |
| 2021-02-06  |    $900      | $0     |
| 2021-02-06  |    $0        | $1500  |
| 2021-02-07  |    $0        | $2500  |
| 2021-02-08  |    $500      | $0     |

..... ........

CodePudding user response：

You can use COALAESCE to give NULL value another value

Declare @StartDate DATETIME2 ='2021-02-01';
Declare @EndDate DATETIME2 = '2021-02-28';
with Extract_Dates_CTE (MyDate) as (
    select CAST(@StartDate as Date)
    Union ALL
    select DATEADD(day, 1, MyDate)
    from Extract_Dates_CTE
    where MyDate < @EndDate
), CTEAMOUNT as(
  SELECT
      [OrderDateTime]
  , SUM(CASE WHEN [Type] = 'Delivery' then [PaymentAmount] END)  Delivery
  , SUM(CASE WHEN [Type] = 'Online' then [PaymentAmount] END)  Online
  FROM tab1
  GROUP BY [OrderDateTime]
)
  
SELECT ed.MyDate,COALESCE(Delivery,0) Delivery,COALESCE(Online,0) Online
FROM Extract_Dates_CTE ed  LEFT JOIN CTEAMOUNT c_a ON c_a.[OrderDateTime] = ed.MyDate

fiddle