SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Help


Query Help

Author
Message
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3069 Visits: 2766
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/
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3886 Visits: 8472
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.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3069 Visits: 2766
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 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.


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/
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3886 Visits: 8472
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



Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3069 Visits: 2766
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/
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3886 Visits: 8472
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.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3069 Visits: 2766
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.. Smile

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
vinu512
vinu512
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1687 Visits: 1625
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. :-)

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden ;-)
Andy Hyslop
Andy Hyslop
SSC Eights!
SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)

Group: General Forum Members
Points: 979 Visits: 3037
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search