Select comparando dos registros de la misma tabla SQL SERVER

[leandro_reyes]

Iniciado por Libras

siguiendo tu logica:

Código SQL:

CREATE TABLE #temp
(STATUS VARCHAR(20),
 cuota INT,
 factura INT)
 
INSERT INTO #temp VALUES ('C', 1, 345 )
INSERT INTO #temp VALUES ('C', 2, 345 )
INSERT INTO #temp VALUES ('O', 3, 345 )
INSERT INTO #temp VALUES ('O', 4, 345 )
 
INSERT INTO #temp VALUES ('C', 1, 346 )
INSERT INTO #temp VALUES ('C', 2, 346 )
INSERT INTO #temp VALUES ('O', 3, 346 )
 
INSERT INTO #temp VALUES ('C', 1, 347 )
INSERT INTO #temp VALUES ('C', 2, 347 )
INSERT INTO #temp VALUES ('O', 3, 347 )
INSERT INTO #temp VALUES ('O', 4, 347 )
INSERT INTO #temp VALUES ('O', 5, 347) 
 
 
 
 
SELECT CASE WHEN test=3 THEN factura END AS resultado FROM
(
SELECT SUM(cuenta) AS test, factura FROM(
SELECT 
CASE WHEN STATUS='o' THEN 1 ELSE 2 END AS cuenta, t1.*
 FROM #temp AS t1
LEFT JOIN(
SELECT MAX(cuota) AS maximo, factura FROM #temp GROUP BY factura
UNION
SELECT MAX(cuota)-1 AS maximo, factura FROM #temp GROUP BY factura
) AS t2 ON (t1.cuota=t2.maximo AND t1.factura=t2.factura)
WHERE t2.maximo IS NOT NULL
) AS t3 GROUP BY factura
) AS t4
 

saludos!

Lo probaré y adaptaré a mi necesidad. Gracias hay les aviso como me queda