December 13, 2016 at 2:06 am
Hi all,
I got my join tables and displayed what I needed:
- ClientId
- NI Number
- Contribution Amount
So of course I have multiple contributions for one client meaning multiple rows like:
Client 1 NINUmber Contribution of 1100.00
Client 1 NINUmber Contribution of 3250.00
Client 1 NINUmber Contribution of 4500.00
I need to have an additional column, let's say 'Contribution above limit' that counts the total contribution for the client and if it is above the limit - let's say 7500 - a message is displayed like 'Total over £7,500'
So far I have it sorted when the actual amount is above 7500 but not the total of all the contributions for the client.
Thank you in advance for your help.
Celine
December 13, 2016 at 2:14 am
CASE
WHEN SUM([Contribution Amount]) <= 7500 THEN ''
ELSE 'Total over £7,500'
END
John
December 13, 2016 at 2:19 am
Hi John,
Thank you for this but by doing the SUM of contribution amount, it is doing exactly that and not for the client. Is my query even possible?
Thanks again
Celine
December 13, 2016 at 2:28 am
Celine
I'm sorry, you've lost me. Please will you provide table DDL in the form of a CREATE TABLE statement, sample data in the form of INSERT statements, expected results, and the query you've already tried?
Thanks
John
December 13, 2016 at 2:51 am
SELECT
C.ClientRef,
C.ClientName,
C.NINumber,
A.AccountRef,
P.ProductRef,
CO.ContributionAmount,
CASE
WHEN CO.Contribution > '7500'
THEN 'Total over £7,500'
END AS 'Contribution above Limit'
FROM Client C
INNER JOIN ClientAccount A
ON A.ClientRef = C. ClientRef
INNER JOIN Product P
ON P.ProductRef = A.ProductRef
INNER JOIN Contribution CO
ON CO.ProductRef = P.ProductRef
WHERE CO.Contribution > '0.00'
ClientRefClientName NINumber AccountRef ProductRef ContributionAmountContribution above Limit
1 Client 1 NINumber Client 1 AccountRef1 ProductRef Client 16000 NULL
1 Client 1 NINumber Client 1 AccountRef1 ProductRef Client 11000 NULL
2 Client 2 NINumber Client 2 AccountRef2 ProductRef Client 215240 Total over £7,500
2 Client 2 NINumber Client 2 AccountRef2 ProductRef Client 25640 NULL
2 Client 2 NINumber Client 2 AccountRef2 ProductRef Client 211520 Total over £7,500
Client 1 is fine as the total contribution is 7000 and not above the 7500 limit
Client 2 should have ‘Total over £7,500’ for the 3 products and not only the ones that are above the limit as the total contribution for all product combined is above the limit.
Let me know if you need me to provide anything else.
Thanks again for your time and your help with this John
Celine
December 13, 2016 at 3:26 am
celine.godines (12/13/2016)
Let me know if you need me to provide anything else.
Please will you provide table DDL in the form of a CREATE TABLE statement, sample data in the form of INSERT statements
Let's see what we can do with what we have, though. It looks as if you're missing a good old-fashioned GROUP BY clause. Something like the below will show the aggregated data for each client and product; if you want to show the individual rows as well, you'll need to use a UNION ALL or a slightly more advanced technique such as this. There's other ways of doing it as well - you could try a SUM with an OVER clause.
SELECT
C.ClientRef
,C.ClientName
,C.NINumber
,A.AccountRef
,P.ProductRef
,SUM(CO.ContributionAmount) AS TotalContribution
,CASE
WHEN SUM(CO.ContributionAmount) > '7500' THEN 'Total over £7,500'
ELSE ''
END AS 'Contribution above Limit'
FROM Client C
INNER JOIN ClientAccount A ON A.ClientRef = C.ClientRef
INNER JOIN Product P ON P.ProductRef = A.ProductRef
INNER JOIN Contribution CO ON CO.ProductRef = P.ProductRef
GROUP BY
C.ClientRef
,C.ClientName
,C.NINumber
,A.AccountRef
,P.ProductRef
WHERE CO.Contribution > '0.00'
John
December 13, 2016 at 3:57 am
Thanks for your help John!!
Have a great day
Celine
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply