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
 General SQL Server Forums
 New to SQL Server Programming
 Combining multiple records

Author  Topic 

Vack
Aged Yak Warrior

530 Posts

Posted - 2014-12-19 : 09:59:09
I have data that looks like this:


Cus_no Inv_No Trans_type Amt_Inv Amt_Paid
100 12345 Other 0 -21.76
100 12345 Discount 0 -6.39
100 12345 Discount 6.39 6.39
100 12345 Discount 21.76 21.76
100 12345 Sales Inv 218.99 218.99


What I would like to do is a select statement that returns:

Cus_no Inv_no Amt_Inv Amt_Paid Disc_amt Other
100 12345 247.14 218.99 -6.39 -21.76


I've tried something like this but since I'm referencing trans_type I get a message that trans_type must be in Group by. doing that give me multiple records.

select cus_no, Inv_no, Sum(Amount_Invoiced_DC) AS InvAmt,
case trans_type when 'Sales Inv' then sum(Amount_Paid_DC) else 0 end as AmtPaid,
case when trans_type = 'Discount' and sum(Amount_Paid_DC)<0 then sum(Amount_Paid_DC) else 0 end as DiscountAmt
FROM BI50_BankTransactions_AR_InvcDt_H
where cus_no is not null
group by cus_no, Inv_no


mhorseman
Starting Member

44 Posts

Posted - 2014-12-19 : 10:39:07
I think:

select cus_no, Inv_no, Sum(Amt_Inv) AS InvAmt,
sum(case trans_type when 'Sales Inv' then Amt_Paid else 0 end) as AmtPaid,
sum(case when trans_type = 'Discount' and amt_paid < 0 then Amt_Paid else 0 end) as DiscountAmt,
sum(case when trans_type = 'Other' then Amt_Paid else 0 end) as Other
FROM BI50_BankTransactions_AR_InvcDt_H
where cus_no is not null
group by cus_no, Inv_no

should do it.

Looks like positive discounts are ignored?


Mark
Go to Top of Page

Vack
Aged Yak Warrior

530 Posts

Posted - 2014-12-19 : 10:57:58
Positives are really part of the Invoice Amt. The negative means they took the discount. I'll give that a try.
Go to Top of Page
   

- Advertisement -