Display an entry from one row on all rows within a query?

[japeconsulting]

Hi
I have the following table entries

Date Acc Ref Amount Type
01/02/2018 ABC001 123 100 PAY
05/01/2018 ABC001 123 200 INV
06/01/2018 ABC001 123 300 INV

so - Select Date, Acc, Ref, Amount, Type from Transactions

I need to write a sql server statement that results in these entries, so basically takes the date and the amount from the payment line and displays it on all lines

Date Acc Ref Amount Type Payment Amount Payment date
01/02/2018 ABC001 123 100 PAY 100 01/02/2018
05/01/2018 ABC001 123 200 INV 100 01/02/2018
06/01/2018 ABC001 123 300 INV 100 01/02/2018

I dont want to group by the reference field as I need to see all rows but use that as the selection that the payment comes from.

Hope this makes sense and thanks in advance

 

[pjw001]

Try this.

select t1.*, t2.amount, t2.[Payment date]
from transtable t1, transtable t2
where t1.[acc ref] = t2.[acc ref]
and t1.type <> 'PAY'
and t2.type == 'PAY'

You will probably want an order by clause, and possibly use distinct.

Also, if you have more than one PAY transaction for an account there will be problems.

I've not tried this.

 

[bborissov]

SELECT YourTable.*,
       PayTable.Date AS PaymentDate
FROM YourTable
INNER JOIN (SELECT * FROM YourTable WHERE AmountType = 'PAY')  PayTable
      ON  YourTable.Ref  = PayTable.Ref

Borislav Borissov
VFP9 SP2, SQL Server