Monday, March 20, 2023

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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog