| | |
Return invoices of the given product with last available contact information
![]() |
•
•
Join Date: Aug 2007
Posts: 2
Reputation:
Solved Threads: 0
I have to write a view that will return all invoices, but with the contact person of the last invoice issued for the same product.
It is hard to explain so I will try to show on example:
Table1: "Invoice"
Table2: "Invoice_row"
Table3: "Contacts"
Each Invoice record contains Contact_id field which points to the contact person who order it.
Each Invoice_row record contains product information.
Products are based on codes (1100, 1101, ...) and to make things a bit harder, products have updates which have the same base number: 110001, 110002, 110101, ...
I would like to get:
Company, Product, Contact, InvoiceNo, InvoiceDate
I can get this with the contact person stated on the individual invoice record, but I don't know how to get the last contact person for each product:
If John ordered 1100 and then Jane ordered 110001 (which is the update to 1100 and both of them are contacts for them same company) I would like to get:
InvoiceNo 1, Product 1100, Contact person Jane
InvoiceNo 2, Product 110001, Contact person Jane
Makes any sense? I want Jane to be returned for all records.
I realy need this so I would appreciate any help.
If something is not clear I will try to explain again.
-thanks
It is hard to explain so I will try to show on example:
Table1: "Invoice"
Table2: "Invoice_row"
Table3: "Contacts"
Each Invoice record contains Contact_id field which points to the contact person who order it.
Each Invoice_row record contains product information.
Products are based on codes (1100, 1101, ...) and to make things a bit harder, products have updates which have the same base number: 110001, 110002, 110101, ...
I would like to get:
Company, Product, Contact, InvoiceNo, InvoiceDate
I can get this with the contact person stated on the individual invoice record, but I don't know how to get the last contact person for each product:
If John ordered 1100 and then Jane ordered 110001 (which is the update to 1100 and both of them are contacts for them same company) I would like to get:
InvoiceNo 1, Product 1100, Contact person Jane
InvoiceNo 2, Product 110001, Contact person Jane
Makes any sense? I want Jane to be returned for all records.
I realy need this so I would appreciate any help.
If something is not clear I will try to explain again.
-thanks
![]() |
Similar Threads
- Product List and Order Form Submit (JavaScript / DHTML / AJAX)
- What should I charge? (eCommerce)
- Help with asp form will not display contact information (ASP)
- Hotmail Login (Windows NT / 2000 / XP)
Other Threads in the MS SQL Forum
- Previous Thread: Replication Issues
- Next Thread: UPDATE rows WHERE array[] ?
Views: 942 | Replies: 1
| Thread Tools | Search this Thread |
Tag cloud for MS SQL
"last 2005 access age auto autogrowth business comparing comparison. count csharp cursor data database dataset dateadd datediff datepart dates day" dbsize difference exploit fill function getdate hack helpwithhomework install join joins limit live maximum money ms mssql mssql2005 multiple mysql news null number partitioning password position query reporting result rules security select server services sets single source sql sql-injection sqlserver subtype sum supertype tables tsql unicode-sql update view weekday





