Display Average Billing Amount For Each Customer only between years 2019-2021-CodePudding

QUESTION : Display Average Billing Amount For Each Customer ONLY between YEAR(2019-2021). If customer doesn't have any billing amount for any of the particular year then consider as 0.

-------: OUTPUT :
Customer_ID |   Customer_Name   |   AVG_Billed_Amount
-------------------------------------------------------------------------
1           |               A               |               87.00
2           |               B               |               200.00
3           |               C               |               183.00

--------: EXPLANATION : If any customer doesn't have any billing records for these 3 years then we need to consider as one record with billing_amount = 0 Like Customer C doesn't have any record for Year 2020, so for C Average will be (250 300 0)/3 = 183.33 OR 183.00

TEMP TABLE HAS FOLLOWING DATA

DROP TABLE IF EXISTS #TEMP; 
CREATE TABLE #TEMP
(
      Customer_ID           INT
    , Customer_Name     NVARCHAR(100)
    , Billing_ID                NVARCHAR(100)
    , Billing_creation_Date     DATETIME
    , Billed_Amount     INT
);
INSERT INTO #TEMP 
SELECT 1, 'A', 'ID1', TRY_CAST('10-10-2020' AS DATETIME),   100 UNION ALL
SELECT 1, 'A', 'ID2', TRY_CAST('11-11-2020' AS DATETIME),   150 UNION ALL
SELECT 1, 'A', 'ID3', TRY_CAST('12-11-2021' AS DATETIME),   100 UNION ALL
SELECT 2, 'B', 'ID4', TRY_CAST('10-11-2019' AS DATETIME),   150 UNION ALL
SELECT 2, 'B', 'ID5', TRY_CAST('11-11-2020' AS DATETIME),   200 UNION ALL
SELECT 2, 'B', 'ID6', TRY_CAST('12-11-2021' AS DATETIME),   250 UNION ALL
SELECT 3, 'C', 'ID7', TRY_CAST('01-01-2018' AS DATETIME),   100 UNION ALL
SELECT 3, 'C', 'ID8', TRY_CAST('05-01-2019' AS DATETIME),   250 UNION ALL
SELECT 3, 'C', 'ID9', TRY_CAST('06-01-2021' AS DATETIME),   300


-----------------------------------------------------------------------------------

Here, 'A' has 3 transactions - TWICE in year 2020(100 150) and 1 in year 2021(100), but none in 2019(SO, Billed_Amount= 0). so the average will be calculated as (100 150 100 0)/4


DECLARE @BILL_dATE DATE = (SELECT Billing_creation_date from #temp group by customer_id, Billing_creation_date) /*-- THIS THROWS ERROR AS @BILL_DATE WON'T ACCEPT MULTIPLE VALUES.*/

OUTPUT should look like this:

Customer_ID |   Customer_Name   |   AVG_Billed_Amount
-------------------------------------------------------------------------
1           |               A               |               87.00
2           |               B               |               200.00
3           |               C               |               183.00

CodePudding user response：

You just need a formula to count the number of missing years.

That's 3 - COUNT(DISTINCT YEAR(Billing_creation_Date)

Then the average = SUM() / (COUNT() (3 - COUNT(DISTINCT YEAR)))...

SELECT
  Customer_ID,
  Customer_Name,
  SUM(Billed_Amount) * 1.0
  /
  (COUNT(*)   3 - COUNT(DISTINCT YEAR(Billing_creation_Date)))
    AS AVG_Billed_amount
FROM
  #temp
WHERE
      Billing_creation_Date >= '2019-01-01'
  AND Billing_creation_Date <  '2022-01-01'
GROUP BY
  Customer_ID,
  Customer_Name

Demo : https://dbfiddle.uk/ILcfiGWL

Note: The WHERE clause in another answer here would cause a scan of the table, due to hiding the filtered column behind a function. The way I've formed the WHERE clause allows a "Range Seek" if the column is in an index.

CodePudding user response：

Here is a query that can do that :

select s.Customer_ID, s.Customer_Name, sum(Billed_amount)/ ( 6 - count(1)) as AVG_Billed_Amount from (
    select Customer_ID, Customer_Name, sum(Billed_Amount) as Billed_amount
    from TEMP
    where year(Billing_creation_Date) between 2019 and 2021
    group by Customer_ID, year(Billing_creation_Date)
) as s
group by Customer_ID;

According to your description the customer_name C will be 137.5000 not 183.00 since 2018 is not counted and 2020 is not there.