June 29, 2013 at 4:11 am
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
trns1 V100 V104 5
trns1 V106 V106 1
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 29, 2013 at 6:07 am
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.
June 29, 2013 at 7:09 am
Cadavre (6/29/2013)
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 QuantityFROM 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.
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...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 29, 2013 at 7:51 am
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
June 30, 2013 at 10:53 am
Cadavre (6/29/2013)
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
thnks its working.. but can you plz explain me this part of query:
SUBSTRING(VoucherNo,2,LEN(VoucherNo)) - ROW_NUMBER() OVER(ORDER BY VoucherNo, TransNo)
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 30, 2013 at 3:27 pm
kapil_kk (6/30/2013)
thnks its working.. but can you plz explain me this part of query:SUBSTRING(VoucherNo,2,LEN(VoucherNo)) - ROW_NUMBER() OVER(ORDER BY VoucherNo, TransNo)
Sure. I'm removing the character from the start of the voucherno as I needed a numerical value. I then added a row_number that was partitioned by the voucherno and the transno, if you minus the row_number from the numerical voucherno, it shows a grouping that I can see for the islands and gaps solution. You should check out Itzik Ben-Gan's solutions for this sort of problem. I think there's a solution in one of the SQL Server Deep Dives books, can't remember which volume.
July 1, 2013 at 12:01 am
Cadavre (6/30/2013)
kapil_kk (6/30/2013)
thnks its working.. but can you plz explain me this part of query:SUBSTRING(VoucherNo,2,LEN(VoucherNo)) - ROW_NUMBER() OVER(ORDER BY VoucherNo, TransNo)
Sure. I'm removing the character from the start of the voucherno as I needed a numerical value. I then added a row_number that was partitioned by the voucherno and the transno, if you minus the row_number from the numerical voucherno, it shows a grouping that I can see for the islands and gaps solution. You should check out Itzik Ben-Gan's solutions for this sort of problem. I think there's a solution in one of the SQL Server Deep Dives books, can't remember which volume.
thanks for your explanation.. 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 1, 2013 at 3:21 am
Cadavre (6/29/2013)
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
Very nice solution Cadavre. The logic is absolutely spot on. 🙂
July 1, 2013 at 4:38 am
I think there's a solution in one of the SQL Server Deep Dives books
Yep, this one
http://www.amazon.co.uk/SQL-Server-MVP-Deep-Dives/dp/1935182048
Excellent book!
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply