Query que no funciona

[victordominguez]

Hola,

He hecho esta query, que me da como resultado lo que necesito:

SELECT T0.DocEntry, T0.CardCode, T0.CardName, T0.DocDate,

  (Select MAX(T1.DocEntry)

  FROM  OINV T1

  WHERE T1.CardCode = T0.CardCode AND

  T1.DocDate BETWEEN '20130101' AND '20131231') AS FactAnt

FROM OINV T0

WHERE T0.DocDate BETWEEN '20140101' AND '20140131'

Lo que quiero, es tirar un listado de facturas en un rango de fechas x, y que en una columna, me aparezca la ULTIMA factura, anterior.

O sea, tiro un listado desde el 01/01/2014 hasta el 31/01/2014, y me dice a quien le he facturado, ahora quiero saber, si a esos clientes a los que les he facturado, ya les habia facturado en el ultimo año, o sea en el periodo (01/01/2014) - 365 hasta (01/01/2014) -1.

Vale, esta query que puse arriba me dá ese resultado, pero quiero que el rango de fechas sea variable, por lo que utilizo esta otra query:

SELECT T0.DocEntry, T0.CardCode, T0.CardName, T0.DocDate,

  (Select MAX(T1.DocEntry)

  FROM  OINV T1

  WHERE T1.CardCode = T0.CardCode AND

  T1.DocDate BETWEEN '[%0]'-365 AND '[%0]'-1) AS FactAnt

FROM OINV T0

WHERE T0.DocDate BETWEEN '[%0]' AND '[%1]'

y no funciona.

Probé con esto:

SELECT T0.DocEntry, T0.CardCode, T0.CardName, T0.DocDate,

  (Select MAX(T1.DocEntry)

  FROM  OINV T1

  WHERE T1.CardCode = T0.CardCode AND

  T1.DocDate BETWEEN '20130101' AND '20131231') AS FactAnt

FROM OINV T0

WHERE T0.DocDate BETWEEN '[%0]' AND '[%1]'

y tampoco funciona, sin embargo, al poner esto:

SELECT T0.DocEntry, T0.CardCode, T0.CardName, T0.DocDate,

  (Select MAX(T1.DocEntry)

  FROM  OINV T1

  WHERE T1.CardCode = 'C022543' AND

  T1.DocDate BETWEEN '[%0]'-365 AND '[%0]-1') AS FactAnt

FROM OINV T0

WHERE T0.DocDate BETWEEN '[%0]' AND '[%1]'

funciona, pero obviamente, en la columna FactAnt repite el mismo numero para todos los clientes.

Alguna idea de cómo solucionarlo?

Muchas gracias

[agustin_marcoscividanes · Accepted solution]

Hola

te pongo una:

/* SELECT T0.docdate FROM OINV T0 */

DECLARE @fecini DATETIME

DECLARE @fecfin DATETIME

SET @fecini = '[%0]'

SET @fecfin = '[%1]'

SELECT Distinct Listado.Cardcode, Listado.Cardname, MAX(Listado.Docentry), MAX(listado.DocDAte), MAX(Listado.FactAnt) FROM

( SELECT T0.DocEntry AS Docentry, T0.CardCode AS Cardcode, T0.CardName AS Cardname,  

(Select MAX(T1.DocEntry) FROM  OINV T1   WHERE T1.CardCode = T0.CardCode AND   T1.DocDate BETWEEN @fecini AND @fecfin   ) AS FactAnt,   (Select MAX(T1.DocDAte) FROM  OINV T1   WHERE T1.CardCode = T0.CardCode AND   T1.DocDate BETWEEN @fecini AND @fecfin   ) AS DocDAte

FROM OINV T0

WHERE T0.DocDate BETWEEN @fecini AND @fecfin

GROUP BY T0.DocEntry, T0.CardCode, T0.CardName, T0.DocDate ) Listado

GROUP BY Listado.Cardcode, Listado.Cardname, listado.DocDAte, Listado.FactAnt

Espero te sirva.

Un saludo

Agustín Marcos Cividanes

[victordominguez · Accepted solution]

CASI!!!!

No daba exactamente el resultado, pero me dio la pista para solucionarlo, al final lo hice así:

/* SELECT T0.docdate FROM OINV T0 */

DECLARE @fecini DATETIME

DECLARE @fecfin DATETIME

SET @fecini = '[%0]'

SET @fecfin = '[%1]'

SELECT T0.Docentry, T0.Cardcode, T0.Cardname, T0.DocDAte,

  (Select MAX(T1.DocEntry)

  FROM  OINV T1

  WHERE T1.CardCode = T0.CardCode AND

  T1.DocDate BETWEEN @fecini-365 AND @fecini-1) AS FactAnt

FROM OINV T0

WHERE T0.DocDate BETWEEN @fecini AND @fecfin

MUCHAS GRACIAS!!!

[agustin_marcoscividanes · Accepted solution]

Hola

cierra el hilo.

Un saludo