Concatenate Multiple values to single total

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

SQL Server 2005 Forums

Transact-SQL (2005)

Concatenate Multiple values to single total

Author

Topic

Tones
Starting Member

17 Posts

Posted - 2010-02-16 : 07:04:20

Hi all came across this forum when searching for a solution to my problem. Hopefully someone can help me out and i can make my problem clear! My SQL knowledge is quite basic so bear with me.

I have 2 tables. One with payments we have made to customers. The other table hold repayments customers (claimants) have made against the payment. For use in reporting services i am wanting to return a single row on each payment, with a calculated total of repayments. If i just select the repayment amount, it will return 3 rows if there have been 3 repaymets. This is causing errors in the report, as when i try to calculate the total amount paid to a customer, it totals the amount in the rows, which is repeated 3 times for 3 repayments.

My research so far leads me to believe i need to concatenate the rows, or calculate the amount and return that total in my select statement instead of the repaid amount(in bold). My SQL statment is as follows:

SELECT     application.application_id_int, claimant.claimant_id_int, claimant.surname_str, claimant.forename_str, claimant.dob_date, claimant.nino_str, 
                      claimant.GB_office_id_int, application.application_date, application.decision_date, application.application_type_id_int, 
                      application_type.application_type_name_str, application.amount_requested_dec, application.amount_awarded_dec, application.repayment_rate_dec, 
                      claimant.date_transferred, repayments.repayment_amount_dec, repayments.repayment_id_int, office.office_name_str
FROM         application INNER JOIN
                      claimant ON application.claimant_id_int = claimant.claimant_id_int LEFT OUTER JOIN
                      repayments ON application.application_id_int = repayments.application_id_int INNER JOIN
                      application_type ON application.application_type_id_int = application_type.application_type_id_int INNER JOIN
                      office ON application.office_no_int = office.office_no_int
WHERE     (claimant.date_transferred >= @date1) AND (claimant.date_transferred <= @date2) AND (claimant.office_no_int = '1000') AND 
                      (application.abandoned_bit = 'False') AND (application.built_on_bit = 'False')

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-16 : 07:28:46

You can try this if it is good enough to get only one record out of that 2, 3 or more...

SELECT APPLICATION.application_id_int, 
       claimant.claimant_id_int, 
       claimant.surname_str, 
       claimant.forename_str, 
       claimant.dob_date, 
       claimant.nino_str, 
       claimant.gb_office_id_int, 
       APPLICATION.application_date, 
       APPLICATION.decision_date, 
       APPLICATION.application_type_id_int, 
       application_type.application_type_name_str, 
       APPLICATION.amount_requested_dec, 
       APPLICATION.amount_awarded_dec, 
       APPLICATION.repayment_rate_dec, 
       claimant.date_transferred, 
       repayments.repayment_amount_dec, 
       repayments.repayment_id_int, 
       office.office_name_str 
FROM   APPLICATION 
       INNER JOIN claimant 
         ON APPLICATION.claimant_id_int = claimant.claimant_id_int 
       LEFT OUTER JOIN (SELECT Row_number() 
                                 OVER(PARTITION BY application_id_int ORDER BY application_id_int) AS rownum, 
                               repayment_amount_dec, 
                               repayment_id_int 
                        FROM   repayments) AS repayments 
         ON APPLICATION.application_id_int = repayments.application_id_int 
            AND repayments.rownum = 1 
       INNER JOIN application_type 
         ON APPLICATION.application_type_id_int = application_type.application_type_id_int 
       INNER JOIN office 
         ON APPLICATION.office_no_int = office.office_no_int 
WHERE  (claimant.date_transferred >= @date1) 
       AND (claimant.date_transferred <= @date2) 
       AND (claimant.office_no_int = '1000') 
       AND (APPLICATION.abandoned_bit = 'False') 
       AND (APPLICATION.built_on_bit = 'False')

No, you're never too old to Yak'n'Roll if you're too young to die.

Tones
Starting Member

17 Posts

Posted - 2010-02-16 : 07:56:26

Many thanks for the very quick reply webfred. I dont fully understand whats going on around the 'PARRTITION BY' area of the statement there, with my limited knowledge. but i am getting an error when i try to run it:

Msg 207, Level 16, State 1, Line 27
Invalid column name 'application_id_int'.

Maybe doesnt recognise the repayments.applicaion_id_int as the FROM REPAYMENTS is in a sort of sub query?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-16 : 08:00:48

My bad, her comes the corrected part:

       LEFT OUTER JOIN (SELECT Row_number() 
                                 OVER(PARTITION BY application_id_int ORDER BY application_id_int) AS rownum, 
                               application_id_int,
                               repayment_amount_dec, 
                               repayment_id_int 
                        FROM   repayments) AS repayments 
         ON APPLICATION.application_id_int = repayments.application_id_int 
            AND repayments.rownum = 1

No, you're never too old to Yak'n'Roll if you're too young to die.

Tones
Starting Member

17 Posts

Posted - 2010-02-16 : 08:51:21

Thanks again webfred, that indeed brought back only one row for each payment (application). However it is only beinging back one repayment for each application, i think the first repayment made. I would need a total repaid against each application for the report. So i would need that to be calculated and returned instead of repayment_amount_dec.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-16 : 10:25:45

quote:
So i would need that to be calculated and returned instead of repayment_amount_dec.

I thought that repayment_amount_dec is already the calculated total repaid and it is repeated in each row...

Maybe you can get better help providing some simple sample data and wanted output?

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-16 : 10:32:17

Can you give this a try?

SELECT APPLICATION.application_id_int, 
       claimant.claimant_id_int, 
       claimant.surname_str, 
       claimant.forename_str, 
       claimant.dob_date, 
       claimant.nino_str, 
       claimant.gb_office_id_int, 
       APPLICATION.application_date, 
       APPLICATION.decision_date, 
       APPLICATION.application_type_id_int, 
       application_type.application_type_name_str, 
       APPLICATION.amount_requested_dec, 
       APPLICATION.amount_awarded_dec, 
       APPLICATION.repayment_rate_dec, 
       claimant.date_transferred, 
       repayments.repayment_amount_dec, 
       office.office_name_str 
FROM   APPLICATION 
       INNER JOIN claimant 
         ON APPLICATION.claimant_id_int = claimant.claimant_id_int 
       LEFT OUTER JOIN (SELECT application_id_int, 
                               SUM(repayment_amount_dec) AS repayment_amount_dec 
                        FROM   repayments
			GROUP BY application_id_int) AS repayments 
         ON APPLICATION.application_id_int = repayments.application_id_int 
       INNER JOIN application_type 
         ON APPLICATION.application_type_id_int = application_type.application_type_id_int 
       INNER JOIN office 
         ON APPLICATION.office_no_int = office.office_no_int 
WHERE  (claimant.date_transferred >= @date1) 
       AND (claimant.date_transferred <= @date2) 
       AND (claimant.office_no_int = '1000') 
       AND (APPLICATION.abandoned_bit = 'False') 
       AND (APPLICATION.built_on_bit = 'False')

Tones
Starting Member

17 Posts

Posted - 2010-02-16 : 10:40:02

Sorry. The repayment_amount_dec is for one repayment, and i need the total repayments for each application. If we have 3 repayments recorded against application 111:-

111 Smith Paul 01/01/1971 200 180 50
111 Smith Paul 01/01/1971 200 180 40
111 Smith Paul 01/01/1971 200 180 30

112 Smith Anne 02/02/1970 500 300 100
112 Smith Anne 02/02/1971 500 300 50

Should return as:

111 Smith Paul 01/01/1971 200 180 120
112 Smith Anne 02/02/1970 500 300 150

where the final column was repayment_amount_dec, but i would like it to bring back the total of repayment_amount_dec for each application.

So then i can work out in my report how much was awarded (180, 300) and i can see how much has still to be repaid (180-120, 500-150)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-16 : 10:43:16

Did you try the query posted.. 02/16/2010 : 10:32:17

I guess that should give you this result...no?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-16 : 10:43:48

Then vijay's solution should work.

No, you're never too old to Yak'n'Roll if you're too young to die.

Tones
Starting Member

17 Posts

Posted - 2010-02-16 : 10:53:59

Apologies Vijay, i hadnt seen your reply when i started to reply to webfred. this does indeed return the results i want. Thank you so much to both of you, as i was really stumped on this and would not have got this sorted without your help. I will now take on board what is in this statement for further use.

Thanks again

Tones

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-16 : 11:20:19

Np. You're welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources