Cómo obtener el ultimo registro de un día anterior en una serie temporal con varios registros por día

Question

Hola

Tengo el siguiente problema en mi base de datos.

Necesito obtener el dato de la ultima factura realizada el dia anterior a un cliente. En un mismo día pueden haber varias facturas de un cliente. 

La idea es obtener esto:

La tabla utilizada es:

DECLARE @Ventas AS TABLE
(
   CodigoCliente integer,
   FechaFactura  Date,
   Hora time
  
);

INSERT INTO @Ventas
VALUES(1,'01/01/2016','15:55'),
 (1,'01/01/2016','18:30'),
 (1,'01/01/2016','18:45'),
 (1,'02/01/2016','15:25'),
 (1,'02/01/2016','19:40'),
 (1,'04/01/2016','20:30'),
 (1,'03/01/2016','21:15'),
 (2,'11/01/2016','15:10'),
 (2,'12/01/2016','15:34'),
 (2,'12/01/2016','18:00'),
 (2,'14/01/2016','17:30'),
 (2,'14/01/2016','21:00'),
 (2,'15/01/2016','15:55')

 select *, ROW_NUMBER() OVER(PARTITION BY codigocliente ORDER BY FechaFactura desc) as ordenFecha,
 ROW_NUMBER() OVER(PARTITION BY codigocliente,fechafactura ORDER BY Hora desc) as ordenhora  from @Ventas

Yo he probado el select que pongo en el ejemplo pero no consigo ordenar las fechas de forma que cada día cuente como uno en todas sus facturas. De conseguirlo podría seleccionar el dia con numero de orden -1 el orden hora=1.

¿Alguna idea de cómo podría solventarse?

Gracias

Angel

Answer 1

Lo puedes conseguir particionando por fecha y ordenando por fecha y hora:

;with cte as
(
select CodigoCliente, FechaFactura, Hora, ROW_NUMBER() OVER(PARTITION BY FechaFactura ORDER BY FechaFactura desc, hora desc) as orden
from @Ventas
)
select * from CTE
where orden = 1

Answer 2

Hola Alberto,

Si, es cierto que consigo tener los registros que suponen la ultima factura del día. 

He estado un rato trabajándola y no veo como llegar desde ahí a la tabla final que necesito y que no se ha subido a mi pregunta por algún motivo. Es esta:

Alguna idea?

Gracias.

Angel

Answer 3

No se me ocurre ninguna forma buena de conseguirlo con la cláusula OVER, pero en cambio no es demasiado complejo hacerlo con una subconsulta:

Select CodigoCliente, FechaFactura, Hora,
(select MAX(cast(FechaFactura as datetime) + cast(Hora as datetime))
 from @Ventas v2 
 where v2.FechaFactura < v1.FechaFactura and v2.CodigoCliente = v1.CodigoCliente
) as [Fecha Hora Buscada]
from @Ventas v1

Answer 4

Cierto, tienes razón.

No era tan complejo. Yo me había empecinado en hacerlo con un over y no veía otras posibilidades. Veo que me queda muchísimo por aprender.

Gracias

Angel