Hi Expert,
Based on below requirement I need to populate three fields GROSS,COMM_INC and RETURN_AMT
then caluculate NET SALE i.e. gross-(comm_inc+Return_Amt)
Logic for gross -- WHERE REF_ACC IN ('GROSS SL','ADF GROSS SL')
Logic for comm_inc -- WHERE REF_ACC IN ('COMMERCIAL INCENTIVES', 'OTHER ADF REBATES')
Logic for Return_Amt -- WHERE REV_ACCOUNT = 'SALES RETURNS'
I implemented below logic GROSS,COMM_INC and RETURN_AMT are populated below.
How to do gross-(comm_inc+Return_Amt) Could you please help me.
SELECT distinct country_cd COUNTRY,CUSTOMER_NAME,CREATED_DATE ,MAKER,
CASE WHEN REF_ACC IN ('GROSS SL','ADF GROSS SL') THEN price END gross,
CASE WHEN REF_ACC IN ('COMMERCIAL INCENTIVES', 'OTHER ADF REBATES') THEN price END comm_inc
FROM CUSTOMER_TBL CT
INNER JOIN SALES_TBL ST
ON ST.SALE_CUSTOMER= CT.SALE_ID
INNER JOIN PRODUCT PT
ON ST.PRODUCT_CD =PT.PDC_CODE
WHERE ST.country_cd='EN'
AND maker='ADAB'
and customer_name IN ('THOMAS')
COUNTRY CUSTOMER_NAME CREATED_DATE MAKER GROSS COMM_INC NET_SALES gross-(comm_inc+Return_Amt)
EN THOMAS 20231206 ADAB 8482.70330539899987 NULL
EN THOMAS 20231219 ADAB NULL 406.64136963599998
EN THOMAS 20231206 ADAB 167803.71943347901106 NULL
EN THOMAS 20231219 ADAB 7270.88983203299995 NULL
EN THOMAS 20231206 ADAB NULL 493.56021628100001
EN THOMAS 20231212 ADAB NULL -9203.33776930500062
EN THOMAS 20231229 ADAB NULL 3511.76311908800017
EN THOMAS 20231229 ADAB NULL 3653.48463020800000
EN THOMAS 20231206 ADAB 8825.03320922700004 NULL
EN THOMAS 20231212 ADAB NULL -9574.74976145499932
EN THOMAS 20231216 ADAB 3680.41937920400005 NULL
Thanks in advance.