Tengo una base de datos con dos tipos de informes que se agregan semanalmente pero en fechas diferentes: ActiveSystems, LicenseReports. Ambos tienen una fecha de informe, ambos tienen un número de serie correspondiente.

Necesito una consulta sql que proporcione todos los últimos ActiveSystems y la primera fila coincidente en la tabla LicenseReport desde la última fecha también. Aquí hay un pequeño ejemplo:

ActiveSystem
ReportDate    SerialNo
9/03/18       111111
9/03/18       112211
9/03/18       114411
9/10/18       111111
9/10/18       112211
9/10/18       113311


LicenseReports
ReportDate    Serial_Number
9/7/18        111111
9/7/18        111111
9/7/18        112211
9/7/18        112211

Necesito todos los elementos de la última fecha en la lista de ActiveSystem, pero solo el primer número de serie coincidente en la tabla de LicenseReport, que puede o no existir.

Hasta ahora tengo algo como esto

SELECT * FROM [dbo].[ActiveSystemReports]  AS ASR 
LEFT JOIN [dbo].[LicenseReports] AS LCR ON ASR.SerialNo = LCR.Serial_Number
Where ASR.ReportDate >= (SELECT MAX(ReportDate) From [dbo].[ActiveSystemReports]) AND 
LCR.ReportDate >= (SELECT MAX(ReportDate) From [dbo].[LicenseReports])

Pero esto no devuelve todos los registros de la primera tabla y contiene duplicados para todos los registros coincidentes en la segunda tabla. Cualquier ayuda sería muy apreciada. Gracias.

Mi salida esperada es:

ActiveSystem                     LicenseReport
ReportDate    SerialNo  ect      ReportDate     Serial_Number
9/10/18       111111    ...      9/7/18         111111
9/10/18       112211    ...      9/7/18         112211
9/10/18       113311    ...      null           null
1
BeLEEver 10 sep. 2018 a las 17:18

5 respuestas

La mejor respuesta

¿Tal vez use un TOP 1 WITH TIES y ordene por row_number para el ReportDate de LicenseReports?

SELECT TOP 1 WITH TIES ASR.*, LCR.*
FROM [dbo].[ActiveSystemReports] AS ASR
LEFT JOIN [dbo].[LicenseReports] AS LCR
  ON ASR.SerialNo = LCR.Serial_Number
WHERE ASR.ReportDate = (SELECT MAX(ReportDate) FROM [dbo].[ActiveSystemReports])
ORDER BY ROW_NUMBER() OVER (PARTITION BY ASR.Serial_Number ORDER BY LCR.ReportDate DESC);
1
LukStorms 10 sep. 2018 a las 14:45

Puede usar OUTER APPLY en lugar de left join. Por favor, consulte la siguiente consulta para obtener más detalles.

SELECT ASR.* , D.Serial_Number as LicenseReportsDate
FROM [dbo].[ActiveSystemReports]  AS ASR 
OUTER APPLY
(
    select TOP 1 Serial_Number from [dbo].[LicenseReports] AS LCR
    where LCR ON ASR.SerialNo = LCR.Serial_Number
    ORder by LCR.ReportDate DESC 
) as  D
0
Bhargav J Patel 10 sep. 2018 a las 14:27

Yo usaría APPLY:

SELECT ASR.*, LCR.* 
FROM [dbo].[ActiveSystemReports] AS ASR OUTER APPLY
     ( SELECT TOP (1) LCR.*
       FROM [dbo].[LicenseReports] AS LCR
       WHERE LCR.Serial_Number = ASR.SerialNo 
       ORDER BY LCR.ReportDate DESC 
     ) LCR; 
0
Yogesh Sharma 10 sep. 2018 a las 14:34
SELECT * 
FROM [dbo].[ActiveSystemReports]  AS ASR 
LEFT JOIN (
    SELECT MIN(LCR.ReportDate), SerialNo
    FROM [dbo].[LicenseReports]
    GROUP BY SerialNo) AS LCR ON ASR.SerialNo = LCR.Serial_Number

Obtiene los registros ASR y el registro LCR primero (por fecha) para la misma serie. Lo que puede no ser realmente lo que quieres, ya que la pregunta no está clara ...

0
Auspex 10 sep. 2018 a las 14:30

Mi intuición me dice que esto es lo que necesitas:

SELECT * FROM (
  SELECT AS.*, LR.ReportDate LicenseReportDate, ROW_NUMBER() OVER (PARTITION BY AS.SerialNo ORDER BY LR.ReportDate DESC) N
  FROM ActiveSystemReports AS
  LEFT JOIN LicenseReports LR ON AS.SerialNo=LR.Serial_Number
) T WHERE N=1
0
Paweł Dyl 10 sep. 2018 a las 14:29