Skip to Content
0
Dec 03, 2008 at 06:50 AM

HELP TO WRITE A QUERY

41 Views

Please help me to write the query I want to see total without tax of all invoices, returns, credit notes, deliveries, sales order with the same projetc code UDF field called U_Project...

Basically the query is the same role as deviation from control credit...

The project is another limit used for that, so it must gives the total spent for a project but with the query below I have 0 value as a result no error....

SELECT (IsNull(SUM(T0.DocTotal-T0.VATsum), 0)

+ IsNull(SUM(T2.DocTotal-T2.VATsum), 0)

+ IsNull(SUM(T3.DocTotal-T3.VATsum), 0)

- IsNull(SUM(T1.DocTotal-T1.VATsum), 0)

- IsNull(SUM(T4.DocTotal-T4.VATsum), 0))

FROM OINV T0 LEFT JOIN

ORIN T1 ON T0.[U_ZPROJET]=T1.[U_ZPROJET] LEFT JOIN

ORDR T2 ON T0.[U_ZPROJET]=T2.[U_ZPROJET] LEFT JOIN

ODLN T3 ON T0.[U_ZPROJET]=T3.[U_ZPROJET] LEFT JOIN

ORDN T4 ON T0.[U_ZPROJET]=T4.[U_ZPROJET]

WHERE T0.[U_ZPROJET]=$[ORDR.U_ZPROJET] AND IsNull(T2.[DocStatus], '0')='O' AND IsNull(T3.[DocStatus], '0') ='O'