kapil_kk (6/29/2013)
Hi,I need help in getting this type of output, I tired with MIN and MAX function but didnt get required output:
Create table T1
(Id int identity primary key,
VoucherNo varchar(10),
TransNo varchar(10)
)
Insert into values ('V100','Trns1'),('V101','Trns1'),('V102','Trns1'),('V103','Trns1'),('V104','Trns1'),('V106','Trns1')
Resultant output:
TransNo FirsvoucherNo LastVoucherNo Quantity
trans1 V100 V104 5
trans2 V106 V106 1
Only way that I can see that your sample data can become your expected outcome is if you made a mistake when entering your sample data. This is supported by the fact that your sample data as posted will not execute.
In light of that, I've made the changes that I think are required to correct your errors. See sample data below: -
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', 'Trns2'); -- changed "Trns1" to "Trns2" to match the expected outcome
Based on this new sample data, your requirement makes a lot more sense. We can achieve the expected result like this: -
SELECT TransNo, MIN(VoucherNo) AS FirsvoucherNo, MAX(VoucherNo) AS LastVoucherNo, COUNT(VoucherNo) AS Quantity
FROM T1
GROUP BY TransNo;
Which produces: -
TransNo FirsvoucherNo LastVoucherNo Quantity
---------- ------------- ------------- -----------
Trns1 V100 V104 5
Trns2 V106 V106 1
Note that I didn't bother with the "Trns" to "Trans" conversion.