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]
-- 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 :
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.