Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Help Expand / Collapse
Author
Message
Posted Saturday, June 29, 2013 4:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,893, Visits: 2,329
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/
Post #1468784
Posted Saturday, June 29, 2013 6:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 2,443, Visits: 7,559
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1468790
Posted Saturday, June 29, 2013 7:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,893, Visits: 2,329
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/
Post #1468794
Posted Saturday, June 29, 2013 7:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 2,443, Visits: 7,559
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



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1468797
Posted Sunday, June 30, 2013 10:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,893, Visits: 2,329
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/
Post #1468876
Posted Sunday, June 30, 2013 3:27 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 2,443, Visits: 7,559
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1468894
Posted Monday, July 1, 2013 12:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,893, Visits: 2,329
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/
Post #1468932
Posted Monday, July 1, 2013 3:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 1,127, Visits: 1,594
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
Post #1468980
Posted Monday, July 1, 2013 4:38 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 5:48 AM
Points: 689, Visits: 2,785

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
Post #1468997
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse