Skip to Content
0
Former Member
Aug 04, 2009 at 02:18 PM

QUERY'S

33 Views

BUen dia,

Tengo una duda con el siguiente query

SELECT

T0.DocNum AS 'FACTURA',

T1.CardFName AS 'CLIENTE',

T0.DocDate AS 'F. FACT.',

T2.DocDate AS 'F. PAGO',datediff(day, T0.DocDate,T2.DocDate) as 'DIAS', '%' = CASE WHEN datediff(day, T0.DocDate,T2.DocDate) <= 30 THEN '11'

WHEN datediff(day, T0.DocDate,T2.DocDate) >30 AND datediff(day, T0.DocDate,T2.DocDate) <= 60 THEN '10'

WHEN datediff(day, T0.DocDate,T2.DocDate) > 60 AND datediff(day, T0.DocDate,T2.DocDate) <= 90 THEN '9'

WHEN datediff(day, T0.DocDate,T2.DocDate) > 90 AND datediff(day, T0.DocDate,T2.DocDate) <= 120 THEN '8'

WHEN datediff(day, T0.DocDate,T2.DocDate) > 120 AND datediff(day, T0.DocDate,T2.DocDate) <= 150 THEN '6'

WHEN datediff(day, T0.DocDate,T2.DocDate) > 150 THEN '5'

ELSE '0' END , (T0.DocTotalT0.WTApplied-T0.VatSum-T0.TotalExpns) as 'TOTAL PAGADO', 'COMISION' = CASE WHEN datediff(day, T0.DocDate,T2.DocDate) <= 30 THEN (T0.DocTotalT0.WTApplied-T0.VatSum-T0.TotalExpns)*0.11

WHEN datediff(day, T0.DocDate,T2.DocDate) >30 AND datediff(day, T0.DocDate,T2.DocDate) <= 60 THEN (T0.DocTotal+T0.WTApplied-T0.VatSum-T0.TotalExpns)*0.10

WHEN datediff(day, T0.DocDate,T2.DocDate) > 60 AND datediff(day, T0.DocDate,T2.DocDate) <= 90 THEN (T0.DocTotal+T0.WTApplied-T0.VatSum-T0.TotalExpns)*0.09

WHEN datediff(day, T0.DocDate,T2.DocDate) > 90 AND datediff(day, T0.DocDate,T2.DocDate) <= 120 THEN (T0.DocTotal+T0.WTApplied-T0.VatSum-T0.TotalExpns)*0.08

WHEN datediff(day, T0.DocDate,T2.DocDate) > 120 AND datediff(day, T0.DocDate,T2.DocDate) <= 150 THEN (T0.DocTotal+T0.WTApplied-T0.VatSum-T0.TotalExpns)*0.06

WHEN datediff(day, T0.DocDate,T2.DocDate) > 150 THEN (T0.DocTotal+T0.WTApplied-T0.VatSum-T0.TotalExpns)*0.05

END FROM OINV T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode INNER JOIN ORCT T2 ON T0.ReceiptNum = T2.DocEntry INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode WHERE T2.DocDate >=[%0] and T2.DocDate <=[%1] and T3.SlpName =[%2]

UNION ALL

SELECT '99999','************************* TOTAL ************************', GETDATE(), GETDATE(), '999', '99', SUM(T20.TOTAL_PAGADO), SUM(T20.COMISION)

FROM

(SELECT T0.DocNum AS 'FACTURA', T1.CardFName AS 'CLIENTE', T0.DocDate AS 'F. FACT.', T2.DocDate AS 'F. PAGO',datediff(day, T0.DocDate,T2.DocDate) as 'DIAS', '%' = CASE WHEN datediff(day, T0.DocDate,T2.DocDate) <= 30 THEN '11'

WHEN datediff(day, T0.DocDate,T2.DocDate) >30 AND datediff(day, T0.DocDate,T2.DocDate) <= 60 THEN '10'

WHEN datediff(day, T0.DocDate,T2.DocDate) > 60 AND datediff(day, T0.DocDate,T2.DocDate) <= 90 THEN '9'

WHEN datediff(day, T0.DocDate,T2.DocDate) > 90 AND datediff(day, T0.DocDate,T2.DocDate) <= 120 THEN '8'

WHEN datediff(day, T0.DocDate,T2.DocDate) > 120 AND datediff(day, T0.DocDate,T2.DocDate) <= 150 THEN '6'

WHEN datediff(day, T0.DocDate,T2.DocDate) > 150 THEN '5'

ELSE '0' END , (T0.DocTotalT0.WTApplied-T0.VatSum-T0.TotalExpns) as 'TOTAL PAGADO', 'COMISION' = CASE WHEN datediff(day, T0.DocDate,T2.DocDate) <= 30 THEN (T0.DocTotalT0.WTApplied-T0.VatSum-T0.TotalExpns)*0.11

WHEN datediff(day, T0.DocDate,T2.DocDate) >30 AND datediff(day, T0.DocDate,T2.DocDate) <= 60 THEN (T0.DocTotal+T0.WTApplied-T0.VatSum-T0.TotalExpns)*0.10

WHEN datediff(day, T0.DocDate,T2.DocDate) > 60 AND datediff(day, T0.DocDate,T2.DocDate) <= 90 THEN (T0.DocTotal+T0.WTApplied-T0.VatSum-T0.TotalExpns)*0.09

WHEN datediff(day, T0.DocDate,T2.DocDate) > 90 AND datediff(day, T0.DocDate,T2.DocDate) <= 120 THEN (T0.DocTotal+T0.WTApplied-T0.VatSum-T0.TotalExpns)*0.08

WHEN datediff(day, T0.DocDate,T2.DocDate) > 120 AND datediff(day, T0.DocDate,T2.DocDate) <= 150 THEN (T0.DocTotal+T0.WTApplied-T0.VatSum-T0.TotalExpns)*0.06

WHEN datediff(day, T0.DocDate,T2.DocDate) > 150 THEN (T0.DocTotal+T0.WTApplied-T0.VatSum-T0.TotalExpns)*0.05

END FROM OINV T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode INNER JOIN ORCT T2 ON T0.ReceiptNum = T2.DocEntry INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode WHERE T2.DocDate >=[%0] and T2.DocDate <=[%1] and T3.SlpName =[%2])T20

Me arroja el siguiente error

04/08/2009 09:10:55: 1). [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'UNION'.

2). [Microsoft][ODBC SQ