Share
Report
1,859Dear Experts,
I have write a SQL query to get the Item charges applied on document no and charged through document no. But charge amount value is calculating wrong, what I am doing wrong. Please help me to resolve it.
SELECT a.[Document No_]"Charged Through Doc No",a.[Document Line No_]"Charged Through Doc Line No",a.[Item Charge No_],a.[Source No_]"Charged Through Doc VendorNo",
COALesce(SUM(a.[Cost Amount (Actual)]),0)-COALesce(sum(a.[Cost Amount (Non-Invtbl_)]),0)"Charge Amount",
MAX(b.[Document No_]) as [Document No_],
MAX(b.[Document Line No_]) as [Document Line No_],
MAX(b.[Source No_])"Source No.",a.Description
FROM [TEST Private Limited$Value Entry] as a
left join [TEST Private Limited$Value Entry] as b on a.[Item Ledger Entry No_]=b.[Item Ledger Entry No_] and b.[Item Charge No_]='' and b.[Document Type] IN (2,6,10)
GROUP BY a.[Document No_],a.[Item Charge No_],a.Description,a.[Document Line No_],a.[Source No_]
HAVING a.[Item Charge No_]<>'' and max(b.[Document No_]) = 'EXP-101-19-0026'
*This post is locked for comments
All responses (
Answers (
