Query rows of an entry into columns

[enatefox]

I have looked far and wide for a way to get rows of results into columns to no avail. I have order numbers that have multiple payments and I need the balance left unpaid. My anger comes from MSSQL2000 w/o pivot

Here's an example:

Column names:
[ip_amt | packout_id | payments_id | date_received | invoice_num | amount_received]
[html]
114.1300 22859 105 2007-02-05 INV123 94.27
NULL 22859 2628 2007-03-05 INV123 19.86[/HTML]

$114.13 is what I paid, and $94.27 + $19.86 is what I was paid over two months.

I need the balance amount but in the first column, I've only got the balance of one month and the next month doesn't have enough data. How can I sum up the two amount_received cells on the far-right column?

What I need is for it to say "Balance = $0.00 for this invoice" since over the two months, it equals the $114.13

This is the view that I am querying to get the columns

select
    sum(user_item_credit_amount) as ip_amt, 
    amount_received,
    p.amount_received - sum(user_item_credit_amount) bal,
    --p.date_received,
    p.check_credit_num,
    p.packout_id,
    pk.invoice_num,
    date_received,
    p.payments_id
from payments p, item_payments i, packout pk
where i.payments_id = p.payments_id
    and pk.packout_id = i.packout_id
group by  date_received,pk.invoice_num,amount_received,check_credit_num,p.packout_id,    p.payments_id

Any suggestions?

[ck9663]

I have looked far and wide for a way to get rows of results into columns to no avail. I have order numbers that have multiple payments and I need the balance left unpaid. My anger comes from MSSQL2000 w/o pivot

Here's an example:

Column names:
[ip_amt | packout_id | payments_id | date_received | invoice_num | amount_received]
[html]
114.1300 22859 105 2007-02-05 INV123 94.27
NULL 22859 2628 2007-03-05 INV123 19.86[/HTML]

$114.13 is what I paid, and $94.27 + $19.86 is what I was paid over two months.

I need the balance amount but in the first column, I've only got the balance of one month and the next month doesn't have enough data. How can I sum up the two amount_received cells on the far-right column?

What I need is for it to say "Balance = $0.00 for this invoice" since over the two months, it equals the $114.13

This is the view that I am querying to get the columns

select
    sum(user_item_credit_amount) as ip_amt, 
    amount_received,
    p.amount_received - sum(user_item_credit_amount) bal,
    --p.date_received,
    p.check_credit_num,
    p.packout_id,
    pk.invoice_num,
    date_received,
    p.payments_id
from payments p, item_payments i, packout pk
where i.payments_id = p.payments_id
    and pk.packout_id = i.packout_id
group by  date_received,pk.invoice_num,amount_received,check_credit_num,p.packout_id,    p.payments_id

Any suggestions?

am assuming INVOICE_NUM is a unique key.

try:

 
select a.invoice_num, a.ip_amt, b.total_paid, balance = a.ip_amt - b.total_paid
from mytable
inner join (select invoice_num, sum(amount_received) as total_paid from mytable group by invoice_num) b on a.invoice_num = b.invoice_num
 
 

[enatefox]

select a.invoice_num, a.ip_amt, b.total_paid, balance = a.ip_amt - b.total_paid
from mytable
inner join (select invoice_num, sum(amount_received) as total_paid from mytable group by invoice_num) b on a.invoice_num = b.invoice_num
 

Thanks for the reply. No, invoice num can be linked to multiple payments and therefore payments_id is. I messed around a bit and found a solution I think others can adapt it as needed. I hope this helps someone.

Let me clarify the application first:
I have orders that are paid over time and an accrued balance. I need to balance for the order at a given month however I only have the total I paid out and the individual amounts I was paid so I can't do something like this:
100 -10(january) - 10(february) because each time I subtract 10, it will be for the total (100). Which sucks. So given the above data, here it is:


select * from
(select ip_amt, payments_id, amount_received,invoice_num,packout_id, date_received,
(case when amount_received - ip_amt < 0 then (select sum(amount_received)-ip_amt from payments where packout_id = temp.packout_id) end) as bal_master,
(case when amount_received - ip_amt > 0 then
(select sum(amount_received)-ip_amt from payments where packout_id = temp.packout_id) end) as bal_overallocated
from temp
as resultset
order by date_received, invoice_num



NOTES:

The key is in the CASE statements. I have a view that is summing the ip_amt which for the first entry is the total for all payments and then each row under it (following payment) is null.

I'm outta time on this project so when I get a chance, I'll elaborate but this should help.