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 Thursday, May 9, 2013 3:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
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:
FirstVoucherNo LastVoucher Status VoucherType
VVB00001 VVB00002 New 1
VVB00003 VVB00004 Active 1
VVB00005 VVB00006 New 1



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1450969
Posted Thursday, May 9, 2013 4:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550


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);



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1450988
Posted Thursday, May 9, 2013 4:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
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/
Post #1450996
Posted Thursday, May 9, 2013 4:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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


____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1451007
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse