cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

QUERY'S

Go to solution
Former Member

on ‎08-04-2009 3:18 PM

0 Kudos

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

Show replies
Show replies
Answer

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
‎08-04-2009 4:13 PM
0 Kudos

Aqui la tienes corecta :

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

Saludos

Show replies
Show replies

Answers (2)

Answers (2)

Former Member
‎08-05-2009 7:40 AM
0 Kudos

Hola,

los problemas eran dos:

1) lo de [ ] porque en el sub query le habias dado el nombre 'TOTAL PAGADO'

2) Si utilizas los parametros [%0] por campo de tipo texto y fechas tienes q poner las ' 

T2.DocDate >='[%0]' and  T2.DocDate <='[%1]' and  T3.SlpName ='[%2]'

Ciao.

Roberto Montenovo

Show replies
Show replies
Former Member
‎08-04-2009 4:25 PM
0 Kudos

Muchas gracias Roberto, me funciono perfecto

el error era ..... SELECT '99999','************************* TOTAL ***********************', GETDATE(), GETDATE(), '999', '99', SUM(T20.[TOTAL PAGADO]), SUM(T20.COMISION) los [ ] de TOTAL PAGADO?

Show replies
Show replies
Ask a Question