Hello,
I have created the following query to get a report of the sales and purchase for the specified items and it works
Select * from (
SELECT T0.DocNum, T2.[WhsName], T0.DocDate,'Invoice' as 'Type',T1.[ItemCode], T1.[Dscription]
,T1.[LineTotal]-T1.[LineTotal]/( T0.[DocTotal]+T0.[DiscSum])*T0.[DiscSum] as 'Amount'
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OWHS T2 ON T1.[WhsCode]= T2.WhsCode
WHERE T1.[ItemCode] in ('VCDM','VOXM','VOXE')
-- and T0.docdate >= [%0] and T0.docdate <= [%1]
Union All
SELECT T0.DocNum, T2.[WhsName], T0.DocDate,'Expenses' as 'Type', T1.[ItemCode], T1.[Dscription]
,-(T1.[LineTotal]-T1.[LineTotal]/( T0.[DocTotal]+T0.[DiscSum])*T0.[DiscSum]) as 'Amount'
FROM OPCH T0
INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OWHS T2 ON T1.WhsCode = T2.WhsCode
WHERE T1.[ItemCode] in ('VCDM','VOXM','VOXE')
-- and T0.docdate >= [%0] and T0.docdate <= [%1]
) tbl
ORDER BY tbl.[WhsName], tbl.[ItemCode]
When I add the commented line of code "and T0.docdate >= [%0] and T0.docdate <= [%1] " , I get the following error:
1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near 'This'.
2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'Received Alerts' (OAIB) (s) could not be prepared.
any help is appreciated.
Thanks,
Alaa