Hi Guys,
Please how do I relate the tables GACCENTRY, GACCENTRYA and GACCENTRYD together in sql correctly.
I have been stuck trying to do this.
Hi Guys,
Please how do I relate the tables GACCENTRY, GACCENTRYA and GACCENTRYD together in sql correctly.
I have been stuck trying to do this.
SELECT
D.TYP_0,
E.NUM_0,
D.DES_0,
D.LEDTYP_0,
D.LIN_0,
A.ANALIN_0,
D.ACC_0,
AMTCUR_0=ISNULL(A.AMTCUR_0,D.AMTCUR_0),
AMTLED_0=ISNULL(A.AMTLED_0,D.AMTLED_0),
A.CCE_0,
A.CCE_1,
A.CCE_2,
D.SNS_0
FROM SEED.GACCENTRY E
INNER JOIN SEED.GACCENTRYD D ON E.NUM_0=D.NUM_0
LEFT OUTER JOIN SEED.GACCENTRYA A ON D.NUM_0=A.NUM_0 AND D.LIN_0=A.LIN_0 and D.LEDTYP_0=A.LEDTYP_0 AND D.TYP_0=A.TYP_0
WHERE D.LEDTYP_0=1 And E.NUM_0='ATC-ES01213-000001'
Thanks a lot for this. But can I join GACCOUNT table to this query directly to get the dimension account name itself. The below query is what i wrote using the previous SAGE Evolution tables. Now i want to replicate same logic using these new tables. I understand how the Analytical dimensioning works but i just done want to miss out any important field in the joins
SELECT a.*
,CASE
WHEN b.account LIKE '40%'
THEN total_credit - total_debit
WHEN b.account LIKE '41%'
THEN total_credit - total_debit
WHEN b.account LIKE '50%'
THEN total_debit - total_credit
WHEN b.account LIKE '51%'
THEN total_debit - total_credit
WHEN b.account LIKE '52%'
THEN total_debit - total_credit
WHEN b.account LIKE '53%'
THEN total_debit - total_credit
WHEN b.account LIKE '54%'
THEN total_debit - total_credit
WHEN b.account LIKE '45%'
THEN total_credit - total_debit
WHEN b.account LIKE '6%'
THEN total_debit - total_credit
WHEN b.account LIKE '7%'
THEN total_debit - total_credit
WHEN b.account LIKE '8%'
THEN total_debit - total_credit
WHEN b.account LIKE '9%'
THEN total_debit - total_credit
ELSE 0
END AS net_amount
,b.account
,CASE
WHEN b.account LIKE '40%'
THEN 'Revenue'
WHEN b.account LIKE '41%'
THEN 'Revenue'
WHEN b.account LIKE '50%'
THEN 'Cost of Sales'
WHEN b.account LIKE '51%'
THEN 'Cost of Sales'
WHEN b.account LIKE '52%'
THEN 'Cost of Sales'
WHEN b.account LIKE '53%'
THEN 'Cost of Sales'
WHEN b.account LIKE '54%'
THEN 'Cost of Sales'
WHEN b.account LIKE '45%'
THEN 'Other Income'
WHEN b.account LIKE '6%'
THEN 'Other Expense'
WHEN b.account LIKE '7%'
THEN 'Other Expense'
WHEN b.account LIKE '8%'
THEN 'Other Expense'
WHEN b.account LIKE '9%'
THEN 'Other Expense'
ELSE 'Unknown'
END AS category
,b.description
FROM (
SELECT TxDate
,accountlink
,trcodeid
,sum(debit) AS total_debit
,sum(credit) AS total_credit
,drcraccount
,project
--sum(credit) - sum(debit) as net_amount
FROM PostGL
WHERE txdate >= '2020-01-01'
--and AccountLink like '40%'
GROUP BY TxDate
,AccountLink
,TrCodeID
,DrCrAccount
,Project
) a
LEFT JOIN Accounts b ON a.accountlink = b.accountlink
WHERE b.account BETWEEN '4000'
AND '9998';
You are heaven sent. this worked perfectly. thanks a lot
You are heaven sent. this worked perfectly. thanks a lot
*Community Hub is the new name for Sage City