Obtener ultimo registro del mismo dispositivo

Question

Hola buenas tardes

Quisiera ver si me pueden apoyar, tengo 3 tablas,

La tabla ventas contiene todas las facturas que se han emitido por cada orden generada, la tabla orden registra todos servicios que se le realizan a los dispositivos, puede tener 1, 2 o 9 visitas el mismo dispositivo pero con diferente fecha, lo que requiero es obtenerla ultima orden (mas reciente) de cada dispositivo, ya que puede cambiar el cliente de ese dispositivo, por lo que requiere la informacion de ese cliente.

tengo lo siguiente:

select vta.idorden,vta.fechafactura,iddispositivo  
	from ventas vta 
inner join (select MAX(convert(int,substring(idorden,2,10))) as MAXORDEN,iddispositivo 
	from ordenes group by iddispositivo) as vta2 
on vta2.MAXORDEN = convert(int,substring(vta.idorden,2,10))
where vta.VTE_STATUS = 'I'
order by vta2.MAXORDEN desc  

Pero no me filtra por dispositivo, me muestra todas las ordenes del mismo dispositivo

¿Me pudieran orientar en que la estoy regando?

Answer 1

Hola Oscar: En el modelo de datos que muestras hay varias cosas que deberías de mejorar. Por ejemplo las fechas no deberían de ser textos (varchar), sino columnas de tipo date/datetime/datetime2 o cualquier tipo asociado a una fecha.

No obstante. Según lo que pareces comentar, te expongo una posible solución.

Create Table dbo.Ventas (
	idCliente varchar(5),
	idDispositivo varchar(10),
	idFactura varchar(10),
	fechaFactura varchar(10),
	idOrden varchar(10),
	estatus varchar(1)
);
Create table dbo.Ordenes (
	idOrden varchar(10),
	idDispositivo varchar(10),
	fechaOrden varchar(10)
	);
Create TAble dbo.Clientes (
	idCliente varchar(5),
	nombre varchar(20),
	apellido varchar(20),
	telefono varchar(20));
Insert into dbo.Ventas (idCliente, idDispositivo, idFactura, fechaFactura, idOrden, estatus)
values
('5236','DFJ12OP','FAC000256','03/02/2024','N0005265','i');
Insert into dbo.Ordenes (idOrden, idDispositivo, fechaOrden)
Values
('N0005265','DFJ12OP','26/02/2024'),
('N0005265','DFJ12OP','27/02/2024'),
('N0005265','DFJ12OP','28/02/2024');
Insert into dbo.Clientes (idCliente, nombre, apellido, telefono)
Values
('5236','pedro','flores','5511223344');

Ahora con los datos, vamos a usar el operador apply.

set dateFormat dmy
Select vta.*,
	cli.*,
	lastOrden.mifecha
	from dbo.Ventas vta inner join dbo.Clientes cli on vta.idCliente = cli.idCliente
	cross apply (
		select top(1)CAST(o.fechaOrden as date) as mifecha, o.idDispositivo, o.idOrden
			from
				dbo.Ordenes o 
				Where o.idOrden = vta.idOrden
			order by Cast(o.fechaOrden as date) desc
			) lastOrden

Cosas a tener en cuenta. Las fechas, para capturar su orden las he convertido a tipo date y he establecido el formato usando set DateFormat

El operador apply te obtendrá la última orden, en base a la unión de Ordenes, con Ventas por el idOrden.

Operador apply