July 26, 2011 at 7:38 am
I have two tables that represent different data. The first table is called InvoiceSplit, the second is called AccountSplit. Basically commission splitting tables, one splits based on Invoice, the other based on Account. The Invoice table takes precedence so if the invoice exists in the table then utilize the Invoice Split otherwise look in the account split table. I am looking to do this in a single sql statement but can only think of doing it with a temp table, where I first join on the invoice split table then join in the account split table where invoice does not exist in the temp table (Ugly) ...
Ex.
Invoice_Split_Table
InvID UserID Prct
1 A1 .25
1 A2 .75
Account_Split_Table
AccountID UserID Prct
AC1 A3 .5
AC1 A8 .5
Invoice_Table
InvID AccountID OwnerId SalesAmt
1 AC1 A3 1000
2 AC1 A3 10000
3 AC2 A9 1000
Required Result -
InvID AccountID OwnerIDComm SalesAmtComm
1 AC1 A1 250
1 AC1 A2 750
2 AC1 A3 5000
2 AC1 A8 5000
3 AC2 A9 1000
July 26, 2011 at 7:44 am
Please post what you've tried already. 🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJuly 26, 2011 at 8:32 am
Figured it out... Left Outer Joins and a compound ON qualifier completed this task
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply