May 9, 2013 at 3:55 am
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/
May 9, 2013 at 4:15 am
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/61537May 9, 2013 at 4:26 am
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/
May 9, 2013 at 4:44 am
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?
____________________________________________________
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/61537Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply