Sql Server Self JOIN (bajando los valores de columna)

Me piden que haga lo siguiente: "CycleStartDate debe ser el BillDate del logging anterior de BillDate. Si no existe un logging anterior, debe usar el CycleEndDate más reciente de la tabla DataTime".

CycleStartDate y CycleEndDate son columnas en una tabla llamada DataTime

BillDate es una columna en una tabla llamada BillingData

Estos son los valores de BillDate:

2012-07-27 00:00:00.000 2012-07-27 00:00:00.000 2012-08-27 00:00:00.000 2012-08-27 00:00:00.000 2012-09-28 00:00:00.000 2012-09-28 00:00:00.000 2012-10-26 00:00:00.000 2012-10-26 00:00:00.000 2012-11-27 00:00:00.000 2012-11-27 00:00:00.000 2012-12-27 00:00:00.000 

¿Cómo establecería los valores de CycleStartDate según los requisitos? Las tablas Datetime y BillingData están conectadas por una columna llamada MeterID.

Intenta algo similar a esto …

 SELECT B.BillDate, ISNULL( B2.BillDate, (SELECT MAX(CycleEndDate) FROM DataTime DT WHERE DT.MeterID = B.MeterID) ) CycleStartDate FROM BillingData B OUTER APPLY ( SELECT TOP 1 B2.BillDate FROM BillingData B2 WHERE B2.MeterID = B.MeterID AND B2.BillingData < B.BillingData ORDER BY B2.BillingData DESC ) B2 

Todavía tengo una duda … ¿Necesita tomar SELECT MAX(CycleEndDate) FROM DataTime DT WHERE DT.MeterID = B.MeterID o SELECT MAX(CycleEndDate) FROM DataTime DT WHERE DT.MeterID = B.MeterID AND DT.CycleEndDate < B.BillDate ?

Pero puede hacerse sin la APLICACIÓN EXTERIOR …

 SELECT B.BillDate, ISNULL( (SELECT MAX(B2.BillDate) FROM BillingData B2 WHERE B2.MeterID = B.MeterID AND B2.BillingData < B.BillingData), (SELECT MAX(CycleEndDate) FROM DataTime DT WHERE DT.MeterID = B.MeterID) ) CycleStartDate FROM BillingData B 

Creo que la segunda versión es bastante legible … Para cada fila de BillingData B, busque el BillDate más BillDate ( MAX(B2.BillDate) ) menor que el BillDate actual y del mismo MeterID . Si no está presente (el ISNULL , si el primero no está presente, entonces es NULL , por lo que va a la segunda parte del ISNULL ), busque el CycleEndDate más CycleEndDate de DataTime con el mismo MeterID y devuélvalo.

Puede usar la function ROW_NUMBER() para compensar un JOIN :

 SELECT a.BillDate, COALESCE(b.BillDate,c.CycleEndDate) 'CycleEndDate' FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY MeterID ORDER BY BillDate DESC)'RowRank' FROM YourTable )a LEFT JOIN (SELECT *,ROW_NUMBER() OVER (PARTITION BY MeterID ORDER BY BillDate DESC)'RowRank' FROM YourTable )b ON a.RowRank = b.RowRank - 1 AND a.MeterID = b.MeterID LEFT JOIN (SELECT MeterID,MAX(CycleEndDate)'CycleEndDate' FROM DataTime GROUP BY MeterID ) c ON a.MeterID = c.MeterID 

El PARTITION BY puede no ser necesario, así como los criterios MeterID en JOIN , su networkingacción es un poco confusa en cuanto a si el ORDER BY debe ser ascendente o descendente, ya que está por encima del logging más reciente será el que obtiene su date de la tabla DateTime, elimine DESC para que sea el logging más antiguo que obtiene su valor de esa tabla.