Calculate additional colum as a percentage

Transact-SQL
1

Hi I am trying to add an additional col as a percentage so i have 3 columes as Account Balance, Current and Overdue
I need an additional col as a percentage (the calculation is Overdue*100/AccountBalance) but unsure of the syntax

sum(i.unall_amount) as 'Account Balance',
sum(case when (i.kind <> 'CSH') and (due_date >= getdate()) then i.unall_amount else 0 end) as 'Current',
sum(i.unall_amount)-sum(case when (i.kind <> 'CSH') and (due_date >= getdate()) then i.unall_amount else 0 end) as 'Overdue'

Any help would be FAB

2

See if this works:

select Account Balance, Current,
Overdue, (Overdue/Accountbalance) as Percent
From yourTable

1 Like
3

Thanks you have given me a clue, the results i will pass to a temp table and do the percentage from there

1 Like
4

Just realized I left out the quotes..
(Overdue/Accountbalance) as 'Percent'

5

No need for a temp table - you have 2 options:

  1. Repeat the calculations in the query as is...

sum(i.unall_amount) as 'Account Balance',
sum(case when (i.kind <> 'CSH') and (due_date >= getdate()) then i.unall_amount else 0 end) as 'Current',
sum(i.unall_amount)-sum(case when (i.kind <> 'CSH') and (due_date >= getdate()) then i.unall_amount else 0 end) as 'Overdue',
sum(i.unall_amount)-sum(case when (i.kind <> 'CSH') and (due_date >= getdate()) then i.unall_amount else 0 end) * 100 / sum(i.unall_amount) As Percentage

  1. Move your query to a CTE with the calculations - then in the outer query reference the columns:

WITH details
AS (
...
sum(i.unall_amount) as 'Account Balance',
sum(case when (i.kind <> 'CSH') and (due_date >= getdate()) then i.unall_amount else 0 end) as 'Current',
sum(i.unall_amount)-sum(case when (i.kind <> 'CSH') and (due_date >= getdate()) then i.unall_amount else 0 end) as 'Overdue'
...
)
SELECT ...,
OverDue * 100 / [Account Balance]
...
FROM details

1 Like
6

I know this is not part of your question, but haven't you switched formula on current and overdue?

7

Thank you