Hello Everyone ,
i have invoice table (invoice no , invoice type , Net sold QTY , NET AMT...etc)
invoice type : (Regular - Return )
i want to count invoices as a net count ( Count Regular invoice - Count Return invoice )
any help to make case function to calculate that value
------------------------------------------------------------------------------
INVOICE:
LOAD *,
date(CREATED_DATE ,'MM/DD/YYYY') as INVC_CREATED_DATE,
Time(CREATED_DATE ,'HH:MI:SS') as INVC_CREATED_TIME,
year (CREATED_DATE) as INVCYEAR,
month (CREATED_DATE) as INVCMONTH,
week (CREATED_DATE) as INVCWEEK,
day (CREATED_DATE) as INVCDAYS,
Num(TAX_AMT) as TAXAMT;
select t.ITEM_SID,
i.SBS_NO as INVC_SBS,
i.STORE_NO,
i.INVC_NO,
i.CREATED_DATE,
to_char(i.CREATED_DATE,'HH24') as Post_Hour,
i.INVC_SID,
i.INVC_TYPE,
i.DISC_PERC,
i.DISC_AMT,
i.CASHIER_ID,
to_char(i.CREATED_DATE) as INVCDATE,
t.ORIG_PRICE as INVCORIGPRICE,
t.PRICE as INVCPRICE,
t.ORIG_TAX_AMT,
t.TAX_AMT,
t.DISC_REASON_ID,
t.COST as INVCCOST,
case
when i.INVC_TYPE = 2
then t.QTY * - 1
else t.QTY
end as INVCQTY,
case i.INVC_TYPE
when 0 then 'REGULAR'
when 2 then 'RETURN'
else 'NA'
end as INVCTYPE,
