Conocer si los datos de un registro son correlativos

[Former Member]

Buen dia a tod@s

Estoy generando una consulta en MSSQL SERVER 2008

Esta me tiene que arrojar el primer y ultimo numero de un rango de facturas de un mes, pero, si estos son correlativos, de no ser asi los numero que dentro de ese rango de datos no sean correlativos me los imprima aparte.

SELECT T1.[SlpName] 'VENDEDOR',
        (SELECT MIN(T0.[NumAtCard]) 
         FROM OINV T0
					 LEFT JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode] 
			   WHERE T0.DocDate = '[%1]' AND T0.[NumAtCard] LIKE '%F%' AND T1.[SlpName] = '[%0]'), 
        (SELECT MIN(T0.[NumAtCard]) 
         FROM OINV T0
					 LEFT JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode] 
			   WHERE T0.DocDate = '[%1]' AND T0.[NumAtCard] LIKE '%C%' AND T1.[SlpName] = '[%0]')      
FROM OINV T0
	LEFT JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode]
WHERE T0.[DocDate] = '[%1]' AND
         T0.[NumAtCard] NOT LIKE '%REDIGIT%' AND
         T0.[NumAtCard] NOT LIKE '%ANUL%' AND
         T1.[SlpName] = '[%0]'
GROUP BY T0.[DocDate], T1.[SlpName]

UNION

SELECT T1.[SlpName] 'VENDEDOR',
        (SELECT MAX(T0.[NumAtCard]) 
         FROM OINV T0 
					 LEFT JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode] 
			   WHERE T0.DocDate = '[%2]' AND T0.[NumAtCard] LIKE '%F%' AND T1.[SlpName] = '[%0]'), 
        (SELECT MAX(T0.[NumAtCard]) 
         FROM OINV T0 
					 LEFT JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode] 
			   WHERE T0.DocDate = '[%2]' AND T0.[NumAtCard] LIKE '%C%' AND T1.[SlpName] = '[%0]')        
FROM OINV T0
	LEFT JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode]
WHERE T0.[DocDate] = '[%2]' AND
         T0.[NumAtCard] NOT LIKE '%REDIGIT%' AND
         T0.[NumAtCard] NOT LIKE '%ANUL%' AND
         T1.[SlpName] = '[%0]'
GROUP BY T0.[DocDate], T1.[SlpName]

Se que para esto debe haber una funcion y por eso acudoa ustedes cual es la mas conveniente?

[former_member188440]

Se me ocurre que hagas uso de la funcion Row_Number y una tablita temporal

Con el Row_Number podrias asignar un consecutivo a tu consulta y quizas concatenando con el dato que viene en el NumAtCard puedas generar una especie de validacion para saber que columna NumAtCard no es igual a tu Columna de RowNumber