How to find Average for each Category for the Selected Period in SQL Server

How to find Average Billing amount for each Category for the Selected Years

Scenario:

Suppose we have a sample billings data table "tbl_billings", with the fields Cust_Id, Cust_Name,

Billing_ID, Billing_Date, Billing_Ammount.

Select * From [dbo].[tbl_billings]

Now we need to calculate the Average Billing Amount for each Customer for the period between 2019 to 2021, with the following assumptions:

-- We need to consider the Billing amount as Zero(0) for the Year where there is no billing.

-- We need to consider the Billing count as One(1) for the Year where there is no billing (=0).

Now lets implement the above Scenario using the below Queries.

In the following Query, we can calculate the Sum of Billing Amounts and Count of Billings for the Periods 2019, 2020, 2021 :

WITH cte_Billings  AS

(

Select Cust_ID , Cust_Name,

SUM( Case When DATEPART(Year,[Billing_Date]) = 2019 Then [Billing_Amount] Else 0 End) As  'Amt_2019',

SUM( Case When DATEPART(Year,[Billing_Date]) = 2020 Then [Billing_Amount] Else 0 End) As 'Amt_2020',

SUM( Case When DATEPART(Year,[Billing_Date]) = 2021 Then [Billing_Amount] Else 0 End) As 'Amt_2021',

COUNT( Case When DATEPART(Year,[Billing_Date]) = 2019 Then [Billing_Amount] Else null End) As  'Cnt_2019',

COUNT( Case When DATEPART(Year,[Billing_Date]) = 2020 Then [Billing_Amount] Else null End) As 'Cnt_2020',

COUNT( Case When DATEPART(Year,[Billing_Date]) = 2021 Then [Billing_Amount] Else null End) As 'Cnt_2021'

From [dbo].[tbl_billings]

Group By Cust_ID , Cust_Name

)

Select *  From cte_Billings

Result:

As per our Scenario, when there is no Billing Amount(Eg: Amt_2019 = 0), we have to consider the respective Year Billing Amount as 0 and the Count as 1.

In this way, we adjust the final Output Query, the Select part of CTE as per below:

WITH cte_Billings  AS

(

Select Cust_ID , Cust_Name,

SUM( Case When DATEPART(Year,[Billing_Date]) = 2019 Then [Billing_Amount] Else 0 End) As  'Amt_2019',

SUM( Case When DATEPART(Year,[Billing_Date]) = 2020 Then [Billing_Amount] Else 0 End) As 'Amt_2020',

SUM( Case When DATEPART(Year,[Billing_Date]) = 2021 Then [Billing_Amount] Else 0 End) As 'Amt_2021',

COUNT( Case When DATEPART(Year,[Billing_Date]) = 2019 Then [Billing_Amount] Else null End) As  'Cnt_2019',

COUNT( Case When DATEPART(Year,[Billing_Date]) = 2020 Then [Billing_Amount] Else null End) As 'Cnt_2020',

COUNT( Case When DATEPART(Year,[Billing_Date]) = 2021 Then [Billing_Amount] Else null End) As 'Cnt_2021'

From [dbo].[tbl_billings]

Group By Cust_ID , Cust_Name

)

Select Cust_ID , Cust_Name,

(Amt_2019+Amt_2020+Amt_2021) As BillAmt_2019_21,

((Case When Cnt_2019 = 0 Then 1 Else Cnt_2019 End)

+(Case When Cnt_2020 = 0 Then 1 Else Cnt_2020 End)+

(Case When Cnt_2021= 0 Then 1 Else Cnt_2021 End )) As BillCnt_2019_21,

(Amt_2019+Amt_2020+Amt_2021) / 

((Case When Cnt_2019 = 0 Then 1 Else Cnt_2019 End)

+(Case When Cnt_2020 = 0 Then 1 Else Cnt_2020 End)+

(Case When Cnt_2021= 0 Then 1 Else Cnt_2021 End )) As Avg_Billing_2019_21

From cte_Billings

Final Result :

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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