Calculating Percentage of Total Group

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculating Percentage of Total Group

Author

Topic

dekizian
Starting Member

8 Posts

Posted - 2008-09-04 : 21:58:31

I'm working on the following query which segregates loan data by the number of days delinquent. I would also like to calculate the percentage delinquent of each group. I need some help calculating the sum of each of the total branches (FA_BRANCH) to divide the result of each delinquent group by the total outstandings of the branch to get the percentage past due. Any ideas???

Thanks in advance

SELECT CO as COMPANY
,FA_BRANCH as BRANCH
,CASE WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '1' and '9' THEN CAST(DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101))as varchar(10))
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '10' and '30' THEN '10-30 Days'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '31' and '60' THEN '31-60 Days'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '61' and '90' THEN '61-90 Days'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '91' and '120' THEN '91-120 Days'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) > '120' THEN '121+ Days' else '0' end as DPD
,SUM(CASE WHEN fa_intmeth = 'C1' AND fa_curbal > 0 THEN fa_curbal - (fa_ueinc - fa_uedisc)
ELSE fa_curbal END * case when fa_codate >0 then 0 else 1 end) as CURBAL

FROM megaCON.dbo.FA_ccccACCT

WHERE fa_pstatus = 'a'
AND fa_branch = '86'
AND co = '1'

GROUP BY CO
,FA_BRANCH
,CASE WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '1' and '9' THEN cast(DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101))as varchar (10))
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '10' and '30' THEN '10-30 Days'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '31' and '60' THEN '31-60 Days'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '61' and '90' THEN '61-90 Days'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '91' and '120' THEN '91-120 Days'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) > '120' THEN '121+ Days' else '0' end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-05 : 00:56:01

give some sample data and illustrate what you want.

dekizian
Starting Member

8 Posts

Posted - 2008-09-05 : 10:03:21

COMPANY BRANCH DPD CURBAL PERCENT
1 1 10-30 Days 455250.00 5.25%
1 1 31-60 Days 252360.00 2.76%
1 1 61-90 Days 152698.00 1.85%

For example, in the first line 455250.00 divided by the total receivables of branch 1 equals 5.25%. The total receivables equals all loans current and past due so essentially the sum of all CURBAL for Branch 1.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-06 : 23:47:43

quote:
Originally posted by dekizian

COMPANY BRANCH DPD CURBAL PERCENT
1 1 10-30 Days 455250.00 5.25%
1 1 31-60 Days 252360.00 2.76%
1 1 61-90 Days 152698.00 1.85%

For example, in the first line 455250.00 divided by the total receivables of branch 1 equals 5.25%. The total receivables equals all loans current and past due so essentially the sum of all CURBAL for Branch 1.

UPDATE t
SET t.PERCENT=t.CURBAL * 100.0/(SELECT SUM(CURBAL) FROM YourTable WHERE COMPANY=t.COMPANY AND BRANCH=t.BRANCH)
FROM YourTable t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources