kapil_kk (6/29/2013)
Srry that was typo misktake in my question n i Have corrected that...that was trns1 only not trns2...
I tried it but not it is not possible with min max function...
I see. What you're doing is an islands and gaps problem. There are countless resources on methods to do this sort of thing online. With your sample data, we can do this: -
CREATE TABLE T1 (
Id INT IDENTITY PRIMARY KEY,
VoucherNo VARCHAR(10),
TransNo VARCHAR(10));
INSERT INTO T1 (VoucherNo, TransNo)
VALUES('V100', 'Trns1'),
('V101', 'Trns1'),
('V102', 'Trns1'),
('V103', 'Trns1'),
('V104', 'Trns1'),
('V106', 'Trns1');
SELECT TransNo, MIN(VoucherNo) AS FirsvoucherNo, MAX(VoucherNo) AS LastVoucherNo, COUNT(Groups) AS Quantity
FROM (SELECT VoucherNo, TransNo,
SUBSTRING(VoucherNo,2,LEN(VoucherNo)) - ROW_NUMBER() OVER(ORDER BY VoucherNo, TransNo)
FROM T1
GROUP BY VoucherNo, TransNo
)a(VoucherNo, TransNo, Groups)
GROUP BY TransNo, Groups;
To produce: -
TransNo FirsvoucherNo LastVoucherNo Quantity
---------- ------------- ------------- -----------
Trns1 V100 V104 5
Trns1 V106 V106 1