WITH CTE AS (

SELECT ID,VoucherNo,Status,VoucherType,

ROW_NUMBER() OVER(ORDER BY ID) AS rn1,

ROW_NUMBER() OVER(PARTITION BY Status ORDER BY ID) AS rn2

FROM #temp)

SELECT MIN(VoucherNo) AS FirstVoucherNo,

MAX(VoucherNo) AS LastVoucher,

Status,

MIN(VoucherType) AS VoucherType

FROM CTE

GROUP BY Status,rn1-rn2

ORDER BY MIN(rn1);

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537