SQL Link Consolidated Table back to Base Table Line Data

Question

question

Phantom asked Feb 1, '18

SQL Link Consolidated Table back to Base Table Line Data

Hi, Note: This is related to SAP tables JDT1 and PCH1(Service or Item document) I have a Table A(JDT1) that is consolidated (based on Transid, Account and Project) from Table B(PCH1 - Line Details), therefore, there is no line to line relationship. See my image of Tables and required output. Table A has 3 Lines and the base document has 6 lines I need to always use table A as my starting point. Special issues the line numbers in table B may not be consecutive (i.e 0,3,4,5,7,9) Item code may be different for the same account and project I have used a CTE to insert, duplicate the lines from Table A, but don't know how I can get the correct line number etc into the output temp table. I somehow need to loop through the Table B and get data for each unique Transid, Account and Project. Hope this is not too confusing. ![alt text][1] Regards, Eric [1]: /storage/temp/4482-jdt1-link-to-pch1.jpg

cte looping consolidation

jdt1-link-to-pch1.jpg (121.9 KiB)

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ commented · Feb 01, 2018 at 01:41 PM

How are you calculating the Debit Amount and Credit Amount in the output?

0 ·

Phantom commented · Feb 01, 2018 at 11:08 PM

Kev, I am getting the value from Table B, **Amount** field. Table A consolidates the Amount field in Table B based on **Transid**, **Account** and **Project**. I am trying to provide a report that breaks table A down into the line details in Table B. With the critical issue being there is no set order in Table B. If I can somehow load the correct line numbers of Table B into Temp table then all is easy. Regards, Eric

0 ·

Kev Riley ♦♦ commented · Feb 02, 2018 at 09:15 AM

Eric - your picture shows that TableB Amount field goes to the 'Base Doc Amount' in the output, not the 'Debit Amount' column?

0 ·

Answer 1 · 2018-02-02T09:18:59Z

Kev Riley answered Feb 2, '18

Maybe I'm missing something in understanding the question, but I'm not sure why the order of rows in TableB has any bearing on this. How far does this query get you to the desired output? declare @TableA table (Transid int, Line int, Account varchar(50), Project int, BaseDocEntry int, DebitAmount float, CreditAmount float); declare @TableB table (Transid int, DocEntry int, Line int, Account varchar(50), Project int, item varchar(50), Amount float); insert into @TableA (Transid, Line, Account, Project, BaseDocEntry, DebitAmount, CreditAmount) select 127345,0,'_SYS01',100000,13500,50,0 union select 127345,1,'_SYS02',200000,13500,20,0 union select 127345,2,'_SYS03',300000,13500,140,0; insert into @TableB (Transid, DocEntry, Line, Account, Project, item, Amount) select 127345,13500,0,'_SYS01',100000,'A',10 union select 127345,13500,3,'_SYS02',200000,'B',20 union select 127345,13500,4,'_SYS03',300000,'C',30 union select 127345,13500,5,'_SYS01',100000,'D',40 union select 127345,13500,7,'_SYS03',300000,'E',50 union select 127345,13500,9,'_SYS03',300000,'F',60; select A.Transid, A.Line, A.Account, A.Project, A.BaseDocEntry, DebitAmount='', CreditAmount='', B.Line, B.item, B.Amount from @TableA A join @TableB B on B.Transid = A.Transid and B.Account = A.Account and B.Project = A.Project order by A.Line, B.Line; Transid Line Account Project BaseDocEntry DebitAmount CreditAmount Line item Amount ----------- ----- ----------- ----------- ------------ ----------- ------------ ----------- ----- ------ 127345 0 _SYS01 100000 13500 0 A 10 127345 0 _SYS01 100000 13500 5 D 40 127345 1 _SYS02 200000 13500 3 B 20 127345 2 _SYS03 300000 13500 4 C 30 127345 2 _SYS03 300000 13500 7 E 50 127345 2 _SYS03 300000 13500 9 F 60 (6 rows affected)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2018-02-03T02:38:56Z

Phantom answered Feb 3, '18

Kev, Thanks I had tried something similar but it had not worked. I will retry following your idea and get back to you. thanks again Eric

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2018-02-04T19:32:53Z

Phantom answered Feb 4, '18

Kev, OK Please see code below I need to give the full picture, your answer is spot on, however, I need to select data from several tables based on the transaction type in Table A (JDT1) , Docentry(CreatedBy), Account, Project. RIGHT OUTER JOIN seems to be the right choice, but once I apply this to all the external table I get no lines returned. Any suggestions ------ CODE SELECT --DISTINCT T1.TransID, T1.Line_ID AS JDT1_Line, T1.BaseRef, T1.Account, T1.Project, T1.RefDate, T1.TransType, --CAST(T1.Ref1 AS VARCHAR(20)) AS 'Ref1', --T1.Ref2, --T1.Ref3Line, CASE WHEN T1.Transtype = 13 THEN T11.LineNum WHEN T1.Transtype = 14 THEN T13.LineNum WHEN T1.Transtype = 15 THEN T15.LineNum WHEN T1.Transtype = 16 THEN T17.LineNum WHEN T1.Transtype = 17 THEN T19.LineNum WHEN T1.Transtype = 18 THEN T21.LineNum WHEN T1.Transtype = 19 THEN T23.LineNum WHEN T1.Transtype = 20 THEN T25.LineNum WHEN T1.Transtype = 21 THEN T11.LineNum WHEN T1.Transtype = 22 THEN T13.LineNum WHEN T1.Transtype = 23 THEN T15.LineNum WHEN T1.Transtype = 24 THEN T15.LineNum WHEN T1.Transtype = 30 THEN T1.Line_ID WHEN T1.Transtype = 46 THEN T15.LineNum WHEN T1.Transtype = 58 THEN T15.LineNum WHEN T1.Transtype = 59 THEN T11.LineNum WHEN T1.Transtype = 60 THEN T13.LineNum WHEN T1.Transtype = 67 THEN T15.LineNum WHEN T1.Transtype = 202 THEN T15.LineNum WHEN T1.Transtype = 203 THEN T15.LineNum WHEN T1.Transtype = 204 THEN T15.LineNum ELSE 99999 END AS 'Doc_Line_No' , CASE WHEN T1.Transtype = 13 THEN ISNULL(T11.ItemCode,'') WHEN T1.Transtype = 14 THEN ISNULL(T13.ItemCode,'') WHEN T1.Transtype = 15 THEN ISNULL(T15.ItemCode,'') WHEN T1.Transtype = 16 THEN ISNULL(T17.ItemCode,'') WHEN T1.Transtype = 17 THEN ISNULL(T19.ItemCode,'') WHEN T1.Transtype = 18 THEN ISNULL(T21.ItemCode,'') WHEN T1.Transtype = 19 THEN ISNULL(T23.ItemCode,'') WHEN T1.Transtype = 20 THEN ISNULL(T25.ItemCode,'') ELSE 'ITEMCODE-FFFFFFF' END AS 'ItemCode', CASE WHEN T1.Transtype = 13 THEN ISNULL(T11.Dscription,'') WHEN T1.Transtype = 14 THEN ISNULL(T13.Dscription,'') WHEN T1.Transtype = 15 THEN ISNULL(T15.Dscription,'') WHEN T1.Transtype = 16 THEN ISNULL(T17.Dscription,'') WHEN T1.Transtype = 17 THEN ISNULL(T19.Dscription,'') WHEN T1.Transtype = 18 THEN ISNULL(T21.Dscription,'') WHEN T1.Transtype = 19 THEN ISNULL(T23.Dscription,'') WHEN T1.Transtype = 20 THEN ISNULL(T25.Dscription,'') ELSE 'DESCRIPTION-FFFFFFF' END AS 'Item_Description', CASE WHEN T1.Transtype = 13 THEN ISNULL(T11.Quantity,0) WHEN T1.Transtype = 14 THEN ISNULL(T13.Quantity,0) WHEN T1.Transtype = 15 THEN ISNULL(T15.Quantity,0) WHEN T1.Transtype = 16 THEN ISNULL(T17.Quantity,0) WHEN T1.Transtype = 17 THEN ISNULL(T19.Quantity,0) WHEN T1.Transtype = 18 THEN ISNULL(T21.Quantity,0) WHEN T1.Transtype = 19 THEN ISNULL(T23.Quantity,0) WHEN T1.Transtype = 20 THEN ISNULL(T25.Quantity,0) ELSE 0 END AS 'Quantity', CASE WHEN T1.Transtype = 13 THEN ISNULL(T11.StockPrice,0) WHEN T1.Transtype = 14 THEN ISNULL(T13.StockPrice,0) WHEN T1.Transtype = 15 THEN ISNULL(T15.StockPrice,0) WHEN T1.Transtype = 16 THEN ISNULL(T17.StockPrice,0) WHEN T1.Transtype = 17 THEN ISNULL(T19.StockPrice,0) WHEN T1.Transtype = 18 THEN ISNULL(T21.StockPrice,0) WHEN T1.Transtype = 19 THEN ISNULL(T23.StockPrice,0) WHEN T1.Transtype = 20 THEN ISNULL(T25.StockPrice,0) ELSE 0 END AS 'Unit_COGS', CASE WHEN T1.Transtype = 13 THEN ISNULL(T11.StockValue,0) WHEN T1.Transtype = 14 THEN ISNULL(T13.StockValue,0) WHEN T1.Transtype = 15 THEN ISNULL(T15.StockValue,0) WHEN T1.Transtype = 16 THEN ISNULL(T17.StockValue,0) WHEN T1.Transtype = 17 THEN ISNULL(T19.StockValue,0) WHEN T1.Transtype = 18 THEN ISNULL(T21.StockValue,0) WHEN T1.Transtype = 19 THEN ISNULL(T23.StockValue,0) WHEN T1.Transtype = 20 THEN ISNULL(T25.StockValue,0) ELSE 0 END AS 'Total_COGS', CASE WHEN T1.Transtype = 13 THEN ISNULL(T11.StockValue,0) --ok WHEN T1.Transtype = 14 THEN ISNULL(T13.StockValue,0) WHEN T1.Transtype = 15 THEN ISNULL(T15.StockValue,0) --ok WHEN T1.Transtype = 16 THEN ISNULL(T17.StockValue,0) WHEN T1.Transtype = 17 THEN ISNULL(T19.StockValue,0) WHEN T1.Transtype = 18 THEN ISNULL(T21.StockValue,0) WHEN T1.Transtype = 19 THEN ISNULL(T23.StockValue,0) WHEN T1.Transtype = 20 THEN ISNULL(T25.StockValue,0) WHEN T1.TransType = 30 THEN T1.debit ELSE 0 END AS 'Line_Debit_total', CASE WHEN T1.Transtype = 13 THEN 0 -- ok WHEN T1.Transtype = 14 THEN 0 WHEN T1.Transtype = 15 THEN 0 --ok WHEN T1.Transtype = 16 THEN 0 WHEN T1.Transtype = 17 THEN 0 WHEN T1.Transtype = 18 THEN 0 WHEN T1.Transtype = 19 THEN 0 WHEN T1.Transtype = 20 THEN 0 WHEN T1.TransType = 30 THEN T1.Credit -- ok ELSE 0 END AS 'Line_Credit_total', CASE WHEN CAST(T1.TransType AS VARCHAR(4)) = '-3' THEN 'YearEND_Rollover' WHEN CAST(T1.TransType AS VARCHAR(4)) = '13' THEN 'A/R Invoice' WHEN CAST(T1.TransType AS VARCHAR(4)) = '14' THEN 'A/R Credit Memo' WHEN CAST(T1.TransType AS VARCHAR(4)) = '15' THEN 'Delivery' WHEN CAST(T1.TransType AS VARCHAR(4)) = '16' THEN 'Return' WHEN CAST(T1.TransType AS VARCHAR(4)) = '17' THEN 'Sales Order' WHEN CAST(T1.TransType AS VARCHAR(4)) = '18' THEN 'A/P Invoice' WHEN CAST(T1.TransType AS VARCHAR(4)) = '19' THEN 'A/P Credit Memo' WHEN CAST(T1.TransType AS VARCHAR(4)) = '20' THEN 'PO Goods Receipt' WHEN CAST(T1.TransType AS VARCHAR(4)) = '21' THEN 'Good Return' WHEN CAST(T1.TransType AS VARCHAR(4)) = '22' THEN 'Purchase Order' WHEN CAST(T1.TransType AS VARCHAR(4)) = '23' THEN 'Quotations' WHEN CAST(T1.TransType AS VARCHAR(4)) = '24' THEN 'Incoming Payments' WHEN CAST(T1.TransType AS VARCHAR(4)) = '30' THEN 'Journal Entry' WHEN CAST(T1.TransType AS VARCHAR(4)) = '46' THEN 'Outgoing Payment' WHEN CAST(T1.TransType AS VARCHAR(4)) = '58' THEN 'ST To Be defined' WHEN CAST(T1.TransType AS VARCHAR(4)) = '59' THEN 'Goods Receipt' WHEN CAST(T1.TransType AS VARCHAR(4)) = '60' THEN 'Goods Issue' WHEN CAST(T1.TransType AS VARCHAR(4)) = '67' THEN 'Transfer' WHEN CAST(T1.TransType AS VARCHAR(4)) = '162' THEN 'Inventory Re-valuation' WHEN CAST(T1.TransType AS VARCHAR(4)) = '202' THEN 'Production Order' WHEN CAST(T1.TransType AS VARCHAR(4)) = '203' THEN 'A/R Down Payments' WHEN CAST(T1.TransType AS VARCHAR(4)) = '204' THEN 'A/P down Payments' ELSE CAST(T1.TransType AS VARCHAR(4)) END AS 'Transaction_Type', T1.Ref1 AS DocReference, CASE WHEN T1.TransType = 13 THEN (SELECT top 1 CASE WHEN DocType = 'I' THEN 'Item' WHEN DocType = 'S' THEN 'Service' ELSE DocType END FROM OINV WHERE DocNum = T1.BaseRef) WHEN T1.TransType = 15 THEN (SELECT top 1 CASE WHEN DocType = 'I' THEN 'Item' WHEN DocType = 'S' THEN 'Service' ELSE DocType END FROM ODLN WHERE DocNum = T1.BaseRef) WHEN T1.TransType = 18 THEN (SELECT top 1 CASE WHEN DocType = 'I' THEN 'Item' WHEN DocType = 'S' THEN 'Service' ELSE DocType END FROM OPCH WHERE DocNum = T1.BaseRef) WHEN T1.TransType = 59 THEN (SELECT 'Item') WHEN T1.TransType = 60 THEN (SELECT 'Item') WHEN T1.TransType = 30 THEN (SELECT 'Journal Entry') ELSE 'XXXXXX' END AS 'Base_Doc_Type', T3.AcctCode, T3.AcctName, T3.FatherNum, T3.Segment_0+'-'+T3.Segment_1+'-'+T3.Segment_2 AS 'GL_Account', T1.Project, T4.U_BU AS 'Project_BU', --T4.U_dept AS 'Project_Dept', CASE WHEN T1.TransType = 13 THEN (SELECT CardCode FROM OINV WHERE DocNum = CAST(T1.Ref1 AS VARCHAR(20)) ) WHEN T1.TransType = 15 THEN (SELECT CardCode FROM ODLN WHERE DocNum = CAST(T1.Ref1 AS VARCHAR(20)) ) WHEN T1.TransType = 18 THEN (SELECT CardCode FROM OPCH WHERE DocNum = CAST(T1.Ref1 AS VARCHAR(20)) ) WHEN T1.TransType = 59 THEN (SELECT 'Stock Receipt') WHEN T1.TransType = 60 THEN (SELECT 'Stock Issue') WHEN T1.TransType = 30 THEN (SELECT 'Journal Entry') ELSE 'ZZZZZZZZ' END AS 'Customer' FROM JDT1 T1 LEFT OUTER JOIN OACT T3 ON T3.AcctCode = T1.Account LEFT OUTER JOIN OPRJ T4 ON T4.PrjCode = T1.Project -- Get Base Document Details --13 'A/R Invoice' RIGHT OUTER JOIN INV1 T11 ON T11.DocEntry = T1.CreatedBy and T11.ObjType = T1.TransType and T11.CogsAcct =T1.Account and T1.Project = T1.Project --14 'A/R Credit Memo' RIGHT OUTER JOIN RIN1 T13 ON T13.DocEntry = T1.CreatedBy and T13.ObjType = T1.TransType and T13.CogsAcct =T1.Account and T13.Project = T1.Project --15 'Delivery' RIGHT OUTER JOIN DLN1 T15 ON T15.DocEntry = T1.CreatedBy and T15.ObjType = T1.TransType and T15.CogsAcct =T1.Account and T15.Project = T1.Project --16 'Return' RIGHT OUTER JOIN RDN1 T17 ON T17.DocEntry = T1.CreatedBy and T17.ObjType = T1.TransType and T17.CogsAcct =T1.Account and T17.Project = T1.Project --17 'Sales Order' RIGHT OUTER JOIN RDR1 T19 ON T19.DocEntry = T1.CreatedBy and T19.ObjType = T1.TransType and T19.CogsAcct =T1.Account and T19.Project = T1.Project --18 'A/P Invoice' RIGHT OUTER JOIN PCH1 T21 ON T21.DocEntry = T1.CreatedBy and T21.ObjType = T1.TransType and T21.CogsAcct =T1.Account and T21.Project = T1.Project --19 'A/P Credit Memo' RIGHT OUTER JOIN RPC1 T23 ON T23.DocEntry = T1.CreatedBy and T23.ObjType = T1.TransType and T23.CogsAcct =T1.Account and T23.Project = T1.Project --20 'PO Goods Receipt' RIGHT OUTER JOIN PDN1 T25 ON T25.DocEntry = T1.CreatedBy and T25.ObjType = T1.TransType and T25.CogsAcct =T1.Account and T25.Project = T1.Project WHERE T1.RefDate>= '01 Dec, 2017' and T1.RefDate <= '31 Dec, 2017' and T1.Account IN ( SELECT AcctCode FROM OACT WHERE GROUPMASK ='5')

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2018-02-04T23:20:40Z

Phantom answered Feb 4, '18

Kev, Getting closer changed to left outer join and looking better. Will check all data and let you know. Regards, Eric

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question