How do I insert additional table between 2 others? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How do I insert additional table between 2 others?

Author

Topic

BIGGY
Starting Member

17 Posts

Posted - 2006-03-10 : 12:30:37

Part of the code as follows:

SELECT
ARDoc."Cpnyid", ARDoc."Custid", ARDoc."CuryOrigDocAmt", ARDoc."DocBal", ARDoc."DocDate", ARDoc."Doctype", ARDoc."slsperid", ARDoc."Territory", ARDoc."RecordType", ARDoc."user7",
ARTran."CmmnPct", ARTran."CuryTranAmt", ARTran."DrCr", ARTran."ExtCost", ARTran."InvtId", ARTran."JrnlType", ARTran."Qty", ARTran."RefNbr", ARTran."Rlsed", ARTran."S4Future04", ARTran."S4Future05", ARTran."TranAmt", ARTran."TranClass", ARTran."TranDate", ARTran."TranType", ARTran."UnitDesc", ARTran."UnitPrice",
RptCompany."CpnyName", RptCompany."RI_ID",
Customer."Name",
Salesperson."CmmnPct", Salesperson."Name", Salesperson."SlsperId"
FROM
{ oj ((("SOLUSBS02APP"."dbo"."zARDoc_Comm" ARDoc INNER JOIN "SOLUSBS02APP"."dbo"."RptCompany" RptCompany ON
ARDoc."Cpnyid" = RptCompany."CpnyID")
INNER JOIN "SOLUSBS02APP"."dbo"."Customer" Customer ON
ARDoc."Custid" = Customer."CustId")
LEFT OUTER JOIN "SOLUSBS02APP"."dbo"."Salesperson" Salesperson ON
ARDoc."slsperid" = Salesperson."SlsperId")
LEFT OUTER JOIN "SOLUSBS02APP"."dbo"."ARTran" ARTran ON
ARDoc."Custid" = ARTran."CustId" AND
ARDoc."Refnbr" = ARTran."RefNbr" AND
ARDoc."Doctype" = ARTran."TranType"}

Currently, if a new rep takes over for an old ones invoices and accounts...he will also get credit on the report which this query is for. Instead I need to use a table SOShipHeader to be 'date sensitive'. SOShipHeader will have the correct 'SlsperID', but will still need to pull the name from Salesperson."Name"

My guess, would be that I need to wedge the SOShipHeader table between the ARDoc and Salesperson tables?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-10 : 12:38:54

Is this something in MS SQL server or not? This forum is for MS SQL server Qs

BIGGY
Starting Member

17 Posts

Posted - 2006-03-10 : 12:41:42

Sorry, this may be posted in the wrong spot. I'm just using the SQL Query Analyzer to pull results...if this is the wrong place, I can move it to another sub-forum. Or is there a moderator that can move this topic?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-10 : 12:49:00

Hi Biggy,

Sorry if I make you feel uncomfortable. Actually I was confused.

By looking at ur query I thought its something in any other language because, I never knew about characters like "{" , even double quote ... used in MS SQL Server.

OK
If this is something to do with MS SQL Server u r in the correct forum.

Tell us what ur tables are (I mean the structure), and give some sample data and the results u want to see.

BIGGY
Starting Member

17 Posts

Posted - 2006-03-10 : 13:20:04

Ok, let's see how well I can explain it. I hope I'm giving you the table structure correctly. This is all relatively new to me so if somethings wrong, then I'm sure you'll let me know.

ARDoc - Table which holds main AR data such as invoice numbers, invoice amounts, etc...

All other tables link to it as follows:
Left Outer to Salesperson on slsperID
Left Outer to ARTran on custID, Refnbr, trantype
Equal to Customer on CustID
Equal to RptCompany on cpnyID

Now from what I'm told, when an account goes from one salesperson to another, the ID is changed in the ARDoc table which means that the new rep will receive commission credit for the whole month.

The fix would be to use the salesperson ID from the table, SOShipHeader because that table would actually list which rep the order shipped under. Examples below.

Currently:
REP1 (has) Account X with $1000 sales -- if Account X moved to REP2
then REP2 now has $1000 sales Account X

Should be:
REP1 has Account X with $1000 sales -- if Account X moved to REP2
SOShipHeader shows REP1 with $1000 sales shipped and $0 for REP2

Man I hope that made some sense :)

BIGGY
Starting Member

17 Posts

Posted - 2006-03-10 : 13:43:16

Grr...ok nevermind on this topic. Seems the the guys were wrong with their numbers and as it turns out the main table ARDoc, does indeed receive the correct salesperson ID's. In that case everything should be ok. They just *thought* without checking that the tables were linked incorrectly. Good waste of 4 hours in trying to fix a problem that is not there. But to get something out of it, let's see if my logic is correct.

If ARDoc was giving me incorrect rep ID's, then it seems that linking ARDoc one-to-one to SOShipHeader on ORDERNUMBER and then pulling the ID by SOShipHeader.slsperID would be my fix. After that I would still have to link to Salesperson.Name though, which is where my initial question of 'wedging' comes in to play.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources