Query Help

  • Hi,I required help in creating this type query

    Create table #temp (ID int,VoucherNo varchar(10), Status varchar(10), VoucherType int)

    Insert into #temp

    Values (1,'VVB00001','New',1),(2,'VVB00002','New',1),(3,'VVB00003','Active',1),(4,'VVB00004','Active',1),

    (5,'VVB00005','New',1),(6,'VVB00006','New',1)

    Required output:

    FirstVoucherNoLastVoucherStatusVoucherType

    VVB00001VVB00002New1

    VVB00003VVB00004Active1

    VVB00005VVB00006New1

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • WITH CTE AS (

    SELECT ID,VoucherNo,Status,VoucherType,

    ROW_NUMBER() OVER(ORDER BY ID) AS rn1,

    ROW_NUMBER() OVER(PARTITION BY Status ORDER BY ID) AS rn2

    FROM #temp)

    SELECT MIN(VoucherNo) AS FirstVoucherNo,

    MAX(VoucherNo) AS LastVoucher,

    Status,

    MIN(VoucherType) AS VoucherType

    FROM CTE

    GROUP BY Status,rn1-rn2

    ORDER BY MIN(rn1);

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Mark-101232 (5/9/2013)


    WITH CTE AS (

    SELECT ID,VoucherNo,Status,VoucherType,

    ROW_NUMBER() OVER(ORDER BY ID) AS rn1,

    ROW_NUMBER() OVER(PARTITION BY Status ORDER BY ID) AS rn2

    FROM #temp)

    SELECT MIN(VoucherNo) AS FirstVoucherNo,

    MAX(VoucherNo) AS LastVoucher,

    Status,

    MIN(VoucherType) AS VoucherType

    FROM CTE

    GROUP BY Status,rn1-rn2

    ORDER BY MIN(rn1);

    Can you plz explain about this rn1-rn2?

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kapil_kk (5/9/2013)


    Mark-101232 (5/9/2013)


    WITH CTE AS (

    SELECT ID,VoucherNo,Status,VoucherType,

    ROW_NUMBER() OVER(ORDER BY ID) AS rn1,

    ROW_NUMBER() OVER(PARTITION BY Status ORDER BY ID) AS rn2

    FROM #temp)

    SELECT MIN(VoucherNo) AS FirstVoucherNo,

    MAX(VoucherNo) AS LastVoucher,

    Status,

    MIN(VoucherType) AS VoucherType

    FROM CTE

    GROUP BY Status,rn1-rn2

    ORDER BY MIN(rn1);

    Can you plz explain about this rn1-rn2?

    Have a look here[/url]

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply