T-SQL for following problem

Last Post 01 Jun 2008 07:27 AM by boonchiat. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
boonchiat
New Member
New Member

--
29 May 2008 06:26 PM
Hi,

I have the following three table, Invoice, InvoiceDetail and PaymentType. * is the primary key.

InvoiceTable

*InvoiceID
InvoiceDate
CustomerName

InvoiceDetail

*InvoiceID
*InvoiceDetailID
Product
Unit Price
Qty
Total

PaymentType
*InvoiceID
*PaymentTypeID
PaymentType
PaymentAmount

Each product can be paid by different payment amount. For example, let say i buy product A with US300, but i may pay by master 130, cash 70 and visa 100. How to create a T-SQL to get the following result
Product a , unit price ,qty , total, payment type , amount
A 300 1 300 master 130
A 300 1 300 cash 70
A 300 1 300 visa 100


And it can be multiple product with multiple payment type. But i just need to properly divide the payment type to each product. I am still not very good a T-SQL programming. Your help is much appreciated
SQLUSA
New Member
New Member

--
29 May 2008 10:52 PM
Let us know if this works:

SELECT i.InvoiceID, i.CustomerName,
id.Product, id.UnitPrice, id.Qty, id.Total,
pt.PaymentType, pt.PaymentAmount

FROM InvoiceTable i
JOIN INvoiceDetail id
on i.InvoiceID = id.InvoiceID
JOIN PaymentType pt
on pt.InvoiceID = i.InvoiceID


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
boonchiat
New Member
New Member

--
29 May 2008 11:56 PM
Hi,

Thanks for reply.

Your answer only can work if it is one product with multiple payment. What about 2 product with 3 different payment or other possibility. For example, let say product a is US300 and b is US200 and customer pay Cash 100 and Visa 120 and Master 280. So the results, it can be

Product , unit price ,qty , total, payment type , amount
A 300 1 300 Cash 100
A 300 1 300 Visa 120
A 300 1 300 Master 80
B 200 1 300 Master 200

or it can be other possibility.

Should i write any Select Case or other T-sql . Your guide is much appreciated


boonchiat
New Member
New Member

--
01 Jun 2008 07:27 AM
Hi Pro Pete,

Thanks for your reply.

There are many possibilities to split the payments. Any can be accepted but not all.

In my case, Customer can even pay one product for three different payments. If i need to link every product with different payment methods and manually split it during the payment process, the front end staff will complain it for the software that are not designed in user friendly way.

Furthermore, if i have product A costs US200 and product B costs US100 and Customer pays Visa with US100 and Master with US200. It doesn't mean customer will tell you that she/he actually pay product B with VISA US100 and product A with Master with US200. It can be product A with VISA US100 and MASTER with US100 and product B with Master US100. As long as I can split the payment, it should be accepted.

Of Course, I can solve this problem with link table but may not be accepted as good solution from front end users. And the requirement is that the invoice must be able to handle multiple products with multiple payments.

In reporting purpose, the account people want to properly allocate each type payment method with respective product.

What is the better solutions for this problems? Or it's not practical at all to do in this way?


Acceptable Use Policy
---