--- CONTEXT ---
I have this view/exhibition, that lists records with sent date, client name, client position, type of refusal, total of IDClientPosition and total of IDRefusal. I use it to check how many claims sent on each date, were accepted, refused and why they were refused by each Client. (I have related tables to use for ClientPosition, Refusal and Client)
DtSent | IDClient | IDClientPosition | TOTAL Position | IDRefusal | TOTAL Refusal
---------------------------------------------------------------------------------------------
2020-01-10 1 1 40 NULL 0
2020-01-10 1 1 3 7 3
2020-01-10 1 2 1 NULL 0
2020-02-10 2 2 27 1 27
2020-02-10 2 2 13 2 13
Query so far, that shows the above values:
SELECT TOP (100) PERCENT dbo.LBA.DtSent, dbo.Claims.IDClient, dbo.LBA.IDClientPosition,
COUNT(dbo.LBA.IDClientPosition) AS [TOTAL Position], dbo.LBA.IDRefusal,
COUNT(dbo.LBA.IDRefusal) AS [TOTAL Refusal]
FROM dbo.LBA INNER JOIN
dbo.ClientPositionLBA ON dbo.LBA.IDClientPosition =
dbo.ClientPositionLBA.IDClientPositLBA LEFT OUTER JOIN
dbo.RefusalLBA ON dbo.LBA.IDRefusal = dbo.RefusalLBA.IDRefusal LEFT OUTER JOIN
dbo.Claims LEFT OUTER JOIN
dbo.Clients ON dbo.Claims.IDClient = dbo.Clients.IDClients ON
dbo.LBA.ClaimID = dbo.Claims.ClaimID
GROUP BY dbo.LBA.IDClientPosition, dbo.Claims.IDClient, dbo.LBA.IDRefusal, dbo.LBA.DtSent
--- WHAT I WOULD LIKE ---
I would like to add 2 columns, for percentages, based on the basis of values of TOTAL Position and TOTAL Refusal.
With this, besides the total values that I already have (count), I could also know the percentage of how many claims were accepted, rejected and with what justification (IDRefusal), for that date, for each client. Summing up, it's more or less like converting those totals to percentage.
Per month there should be around 100 records per client in a total of 20 clients, so it has to have in consideration the performance.
--- TRIED ---
Adding:
COUNT(dbo.LBA.IDClientPosition) * 1.0 / COUNT(*),
COUNT(dbo.LBA.IDRefusal) * 1.0 / COUNT(*)
But it returns for both, 1.000000000000 or 0.000000000000.
Any help would be much appreciated. Thanks in advance!