none
Query help

    Question

  • Hi,
    I need help in getting this type of output:

    Create table T1
    (Id int identity primary key,
    VoucherNo varchar(10),
    TransNo varchar(10)
    )

    Insert into T1 values ('V100','Trns1'),('V101','Trns1'),('V102','Trns1'),('V103','Trns1'),('V104','Trns1'),('V106','Trns1')

    Resultant output:
    TransNo FirsvoucherNo  LastVoucherNo Quantity
    trans1    V100     V104  5
    trans1   V106      V106   1

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh




    • Edited by Kapil_KK Saturday, June 29, 2013 10:39 AM
    Saturday, June 29, 2013 10:12 AM

Answers

  • This is a 'Gaps & Islands' problem. See this solution:

    ;WITH cte
    AS (
    	SELECT *
    		,CAST(SUBSTRING(VoucherNo, 2, 3) AS INT) - ROW_NUMBER() OVER (
    			ORDER BY VoucherNo
    			) AS Grp
    	FROM T1
    	)
    SELECT TransNo
    	,min(VoucherNo) AS FirstVoucherNo
    	,max(VoucherNo) AS LastVoucherNo
    	,count(*) AS Quantity
    FROM cte
    GROUP BY TransNo
    	,Grp


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, June 30, 2013 5:37 AM
    Moderator
  • I feel kind of bad to comment it as this already been mark as the answer and used as a base for WIKI, but actually Neomi's query does not work if the value number is less then 100 or more then 999 (In other words, if the number of characters is different from 3)

    Explanation and Working Query:

    /****************** DDL+DML */
    Create table T1
    (Id int identity primary key,
    VoucherNo varchar(10),
    TransNo varchar(10)
    )
     
    Insert into T1 values ('V100','Trns1'),('V101','Trns1'),('V102','Trns1'),('V103','Trns1'),('V104','Trns1'),('V106','Trns1')
    
    select * from T1
    
    
    /****************** Very Limited query: will work only if the vnumbers have exactly 3 characters */
    ;WITH cte
    AS (
        SELECT *
            ,CAST(SUBSTRING(VoucherNo, 2, 3) AS INT) - ROW_NUMBER() OVER (
                ORDER BY VoucherNo
                ) AS Grp
        FROM T1
        )
    SELECT TransNo
        ,MIN(VoucherNo) AS FirstVoucherNo
        ,MAX(VoucherNo) AS LastVoucherNo
        ,COUNT(*) AS Quantity
    FROM cte
    GROUP BY TransNo, Grp
    
    
    
    
    /* 
    there is a mistake in the desired output on the WIKI:
    TransNo 	FirstVoucher 	LastVoucher 	Quantity 
     trans1	V100	V104	5
     trans2	V106	V106	1
    
    last row should be
     trans1	V106	V106	1
    */
    
    
    /* 
    why the query is Very Limited and how to fix it:
    -------------------------------------------------
    1. using "SUBSTRING(VoucherNo, 2, 3)" is hardcoded for numbers with exactly 3 charators but what if we get:
    Insert into T1 values ('V1000','Trns1')
    
    Naomi's query output will be:
    Trns1	V1000	V104	5
    Trns1	V100	V106	2
    
    This is wrong!
    
    2. using "ORDER BY VoucherNo" inside the OVER clue is incurrect as this is using order by a String type and not number type.
    if we get the value v10 and the value v9 then the order by as string will say v9 > v10 but we want to order by as numbers and get v9 < v10. try to add:
    Insert into T1 values ('V9','NotWorkin'), ('V10','NotWorkin')
    
    Naomi's query output will be:
    NotWorkin	V9	V9	1
    NotWorkin	V10	V10	1
    Trns1	V1000	V104	5
    Trns1	V100	V106	2
    
    This is wrong!
    The currect output that we want is:
    NotWorkin	v9	v10	2
    Trns1	v100	v104	5
    Trns1	v106	v106	1
    Trns1	v1000	v1000	1
    
    the sulotion is to use numbers.
    
    */
    
    -- if the VoucherNo column always use the format of v[number] then we just need to clean the first char and get the number.
    -- if the format is more complex and it is combine with text and then number [text][number], then we can clean any char that is not a number
    -- and the format can be more complax... 
    
    -- let's deal with the format v[number] for now and we will fix the query a bit to become more flexible (to use numbers that do not have 3 char exacly as the original question was)
    
    ;WITH 
    cte01 AS (
        SELECT id, TransNo, CAST(SUBSTRING(VoucherNo, 2, LEN(VoucherNo) - 1) AS INT) AS NumberVoucherNo
        FROM T1
        ),
    cte02 as (
    	select id, TransNo, NumberVoucherNo, (NumberVoucherNo - ROW_NUMBER() over (order by NumberVoucherNo)) as Grp
    	from cte01
    )
    select 
    	TransNo 
    	, 'v' + CAST(MIN(NumberVoucherNo) as varchar(10)) AS FirstVoucherNo
    	,'v' + CAST(MAX(NumberVoucherNo) as varchar(10)) AS LastVoucherNo
        ,COUNT(*) AS Quantity
    	-- or the "Quantity" results we can use this
    	-- , (MAX(NumberVoucherNo) - MIN(NumberVoucherNo) + 1)
    from cte02
    GROUP BY TransNo, Grp
    
    
    /****************** Clean */
    drop table T1
    go
    
    * Note: I write the query in such a way so that it will be clear to understand. You can write the query in a better way without using two CTE's tables.

    I hope this is helpful


    signature

    Wednesday, July 17, 2013 9:59 AM
    Moderator
  • Hi Kapil,

    Can you try this:

    SELECT TempID,TransNo,MIN(VoucherNo) AS FirsvoucherNo,MAX(VoucherNo) AS LastVoucherNo,COUNT(TempID) AS Quantity
    FROM 
    (
    SELECT *
    ,CASE WHEN EXISTS (SELECT TOP 1 VoucherNo FROM T1 tmp2 where RIGHT(tmp2.VoucherNo,3) - 1 = RIGHT(tmp.VoucherNo,3) OR RIGHT(tmp2.VoucherNo,3) + 1 = RIGHT(tmp.VoucherNo,3))
       THEN 1 ELSE 0 END AS TempID
    FROM T1 tmp 
    ) TEST
    GROUP BY TempID,TransNo
    ORDER BY TempID DESC


    Regards Harsh

    Saturday, June 29, 2013 11:05 AM

All replies

  • Hi Kapil,

    As per your query you need to identify the FirsvoucherNo and LastVoucherNo, which not there. 

    You can try this if it works for you:

    SELECT TransNo,
           MIN(VoucherNo) AS FirsvoucherNo,
           MAX(VoucherNo) AS LastVoucherNo
    FROM T1 
    GROUP BY TransNo


    Regards Harsh

    Saturday, June 29, 2013 10:20 AM
  • Hi Kapil,

    As per your query you need to identify the FirsvoucherNo and LastVoucherNo, which not there. 

    You can try this if it works for you:

    SELECT TransNo,
           MIN(VoucherNo) AS FirsvoucherNo,
           MAX(VoucherNo) AS LastVoucherNo
    FROM T1 
    GROUP BY TransNo


    Regards Harsh

    I already tried with MIN  MAX function it will not working with that..

    it will give output as 

    Trns1 V100 V106


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Saturday, June 29, 2013 10:23 AM
  • Hi Kapil,

    You will not get trans2 because you do not have a value "Trans2" for TransNo in the table T1.

    I have tired this query which give exact result what you want by changing the last insert value from trans1 to trans2.

    Create table T1
    (Id int identity primary key,
    VoucherNo varchar(10),
    TransNo varchar(10)
    )
    
    Insert into T1 values ('V100','Trns1'),('V101','Trns1'),('V102','Trns1'),('V103','Trns1'),('V104','Trns1'),('V106','Trns2')
    
    SELECT TransNo,
           MIN(VoucherNo) AS FirsvoucherNo,
           MAX(VoucherNo) AS LastVoucherNo,
           COUNT(VoucherNo) AS Quantity
    FROM T1 
    GROUP BY TransNo


    Regards Harsh

    Saturday, June 29, 2013 10:36 AM
  • Hi Kapil,

    You will not get trans2 because you do not have a value "Trans2" for TransNo in the table T1.

    I have tired this query which give exact result what you want by changing the last insert value from trans1 to trans2.

    Create table T1
    (Id int identity primary key,
    VoucherNo varchar(10),
    TransNo varchar(10)
    )
    
    Insert into T1 values ('V100','Trns1'),('V101','Trns1'),('V102','Trns1'),('V103','Trns1'),('V104','Trns1'),('V106','Trns2')
    
    SELECT TransNo,
           MIN(VoucherNo) AS FirsvoucherNo,
           MAX(VoucherNo) AS LastVoucherNo,
           COUNT(VoucherNo) AS Quantity
    FROM T1 
    GROUP BY TransNo


    Regards Harsh

    Sorry, that was not trans2  I have changed that to Trans1...

    After running your query I am getting this output:

    Trns1 V100 V106 6

    which does not match with my desired output


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Saturday, June 29, 2013 10:41 AM
  • Or if you want work just with the number and if you have a costant V before the number you can use:

    SELECT max(RIGHT(VoucherNo,3)) AS maxNU, min(RIGHT(VoucherNo,3)) AS minNU, TransNo FROM T1 group by TransNo


    Saturday, June 29, 2013 10:45 AM
  • Hi Kapil,

    Can you try this:

    SELECT TempID,TransNo,MIN(VoucherNo) AS FirsvoucherNo,MAX(VoucherNo) AS LastVoucherNo,COUNT(TempID) AS Quantity
    FROM 
    (
    SELECT *
    ,CASE WHEN EXISTS (SELECT TOP 1 VoucherNo FROM T1 tmp2 where RIGHT(tmp2.VoucherNo,3) - 1 = RIGHT(tmp.VoucherNo,3) OR RIGHT(tmp2.VoucherNo,3) + 1 = RIGHT(tmp.VoucherNo,3))
       THEN 1 ELSE 0 END AS TempID
    FROM T1 tmp 
    ) TEST
    GROUP BY TempID,TransNo
    ORDER BY TempID DESC


    Regards Harsh

    Saturday, June 29, 2013 11:05 AM
  • This is a 'Gaps & Islands' problem. See this solution:

    ;WITH cte
    AS (
    	SELECT *
    		,CAST(SUBSTRING(VoucherNo, 2, 3) AS INT) - ROW_NUMBER() OVER (
    			ORDER BY VoucherNo
    			) AS Grp
    	FROM T1
    	)
    SELECT TransNo
    	,min(VoucherNo) AS FirstVoucherNo
    	,max(VoucherNo) AS LastVoucherNo
    	,count(*) AS Quantity
    FROM cte
    GROUP BY TransNo
    	,Grp


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, June 30, 2013 5:37 AM
    Moderator
  • I feel kind of bad to comment it as this already been mark as the answer and used as a base for WIKI, but actually Neomi's query does not work if the value number is less then 100 or more then 999 (In other words, if the number of characters is different from 3)

    Explanation and Working Query:

    /****************** DDL+DML */
    Create table T1
    (Id int identity primary key,
    VoucherNo varchar(10),
    TransNo varchar(10)
    )
     
    Insert into T1 values ('V100','Trns1'),('V101','Trns1'),('V102','Trns1'),('V103','Trns1'),('V104','Trns1'),('V106','Trns1')
    
    select * from T1
    
    
    /****************** Very Limited query: will work only if the vnumbers have exactly 3 characters */
    ;WITH cte
    AS (
        SELECT *
            ,CAST(SUBSTRING(VoucherNo, 2, 3) AS INT) - ROW_NUMBER() OVER (
                ORDER BY VoucherNo
                ) AS Grp
        FROM T1
        )
    SELECT TransNo
        ,MIN(VoucherNo) AS FirstVoucherNo
        ,MAX(VoucherNo) AS LastVoucherNo
        ,COUNT(*) AS Quantity
    FROM cte
    GROUP BY TransNo, Grp
    
    
    
    
    /* 
    there is a mistake in the desired output on the WIKI:
    TransNo 	FirstVoucher 	LastVoucher 	Quantity 
     trans1	V100	V104	5
     trans2	V106	V106	1
    
    last row should be
     trans1	V106	V106	1
    */
    
    
    /* 
    why the query is Very Limited and how to fix it:
    -------------------------------------------------
    1. using "SUBSTRING(VoucherNo, 2, 3)" is hardcoded for numbers with exactly 3 charators but what if we get:
    Insert into T1 values ('V1000','Trns1')
    
    Naomi's query output will be:
    Trns1	V1000	V104	5
    Trns1	V100	V106	2
    
    This is wrong!
    
    2. using "ORDER BY VoucherNo" inside the OVER clue is incurrect as this is using order by a String type and not number type.
    if we get the value v10 and the value v9 then the order by as string will say v9 > v10 but we want to order by as numbers and get v9 < v10. try to add:
    Insert into T1 values ('V9','NotWorkin'), ('V10','NotWorkin')
    
    Naomi's query output will be:
    NotWorkin	V9	V9	1
    NotWorkin	V10	V10	1
    Trns1	V1000	V104	5
    Trns1	V100	V106	2
    
    This is wrong!
    The currect output that we want is:
    NotWorkin	v9	v10	2
    Trns1	v100	v104	5
    Trns1	v106	v106	1
    Trns1	v1000	v1000	1
    
    the sulotion is to use numbers.
    
    */
    
    -- if the VoucherNo column always use the format of v[number] then we just need to clean the first char and get the number.
    -- if the format is more complex and it is combine with text and then number [text][number], then we can clean any char that is not a number
    -- and the format can be more complax... 
    
    -- let's deal with the format v[number] for now and we will fix the query a bit to become more flexible (to use numbers that do not have 3 char exacly as the original question was)
    
    ;WITH 
    cte01 AS (
        SELECT id, TransNo, CAST(SUBSTRING(VoucherNo, 2, LEN(VoucherNo) - 1) AS INT) AS NumberVoucherNo
        FROM T1
        ),
    cte02 as (
    	select id, TransNo, NumberVoucherNo, (NumberVoucherNo - ROW_NUMBER() over (order by NumberVoucherNo)) as Grp
    	from cte01
    )
    select 
    	TransNo 
    	, 'v' + CAST(MIN(NumberVoucherNo) as varchar(10)) AS FirstVoucherNo
    	,'v' + CAST(MAX(NumberVoucherNo) as varchar(10)) AS LastVoucherNo
        ,COUNT(*) AS Quantity
    	-- or the "Quantity" results we can use this
    	-- , (MAX(NumberVoucherNo) - MIN(NumberVoucherNo) + 1)
    from cte02
    GROUP BY TransNo, Grp
    
    
    /****************** Clean */
    drop table T1
    go
    
    * Note: I write the query in such a way so that it will be clear to understand. You can write the query in a better way without using two CTE's tables.

    I hope this is helpful


    signature

    Wednesday, July 17, 2013 9:59 AM
    Moderator
  • Here are a few more solutions and explanations for the Gaps & Islands problem:

    http://blogs.msdn.com/b/samlester/archive/2012/09/04/tsql-solve-it-your-way-gaps-and-islands-with-a-twist.aspx

    Thanks,
    Sam Lester (MSFT)


    http://blogs.msdn.com/b/samlester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Friday, August 02, 2013 9:01 AM
    Moderator