How to add a column that has a percentage of other rows

Question

Hello,
I have this query
SELECT MONTH(issue_date), YEAR(issue_date), COUNT(LoanNumber)
FROM LoanTbl
WHERE loan_status = 'Charged Off'
GROUP BY MONTH(issue_date), YEAR(issue_date)

and I'm getting these results
mm , yy, count of loans
1 2019 74
2 2019 47
3 2019 2
1 2020 121
2 2020 117
3 2020 90
7 2020 63
8 2020 58
9 2020 46

I would like to add another column that shows the difference in the percentage of the count of loans in the opposite year,
so next to the row of 1/2020 will have a difference in the percentage of 1/2019 and so on
how can I achieve this?

Answer 1

Hi @Sam332 ，

Please refer to:

declare @test table(month int,year int,count int)  
insert into @test values(1 ,2019 ,74),(2 ,2019 ,47),(3 ,2019, 2)  
,(1, 2020 ,121),(2 ,2020 ,117),(3 ,2020, 90),(7 ,2020, 63),(8 ,2020 ,58),(9, 2020, 46)  
  
;with cte1   
as(select *,CAST(count*1.0*100/sum(count) over(partition by year) as decimal(10,2))  per1,  
row_number() over(partition by year order by month) rn from @test)  
,cte2   
as(select c1.month month1,c1.year year1,c1.count count1,c1.per1,c2.per1 per2  
from cte1 c1  
left join cte1 c2  
on c1.rn=c2.rn and c1.year=c2.year+1)  
  
select *,cast(per1-per2 as varchar(15))+'%' diff from cte2  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--https://learn.microsoft.com/en-us/answers/questions/142427/users-always-get-connection-timeout-problem-when-u.html

Answer 2

Put your current query inside a CTE and check the next approach too:

declare @LoanTbl as table (issue_date date, LoanNumber int, loan_status varchar(max))  
  
set dateformat dmy  
  
insert into @LoanTbl values  
( '1/1/2019', 1, 'Charged Off' ),  
( '3/1/2019', 2, 'Charged Off' ),  
( '1/2/2019', 1, 'Charged Off' ),  
( '1/3/2019', 1, 'Charged Off' ),  
( '7/3/2019', 2, 'Charged Off' ),  
( '1/1/2020', 1, 'Charged Off' ),  
( '4/1/2020', 2, 'Charged Off' ),  
( '9/1/2020', 1, 'Charged Off' ),  
( '1/2/2020', 1, 'Charged Off' ),  
( '2/2/2020', 2, 'Charged Off' ),  
( '1/3/2020', 1, 'Charged Off' ),  
( '1/7/2020', 1, 'Charged Off' ),  
( '1/8/2020', 1, 'Charged Off' ),  
( '1/9/2020', 1, 'Charged Off' )  
  
;  
with CTE as  
(  
    SELECT MONTH(issue_date) as mm, YEAR(issue_date) as yy, COUNT(LoanNumber) as [count of loan]  
    FROM @LoanTbl  
    WHERE loan_status = 'Charged Off'  
    GROUP BY MONTH(issue_date), YEAR(issue_date)  
)  
select t1.*, t1.[count of loan] * 100.0 / t2.[count of loan] as [percentage]  
from CTE as t1  
left join CTE as t2 on t2.mm = t1.mm and t2.yy = t1.yy - 1   
order by yy, mm