Query Help

  • 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/

  • 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
    My blog - http://www.cadavre.co.uk/
    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/

  • 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/

  • 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
    My blog - http://www.cadavre.co.uk/
    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/

  • 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/

  • 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
    My blog - http://www.cadavre.co.uk/
    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/

  • 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/

  • 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[/url] 😉

  • 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