About this store procedure..

Hi,
I have a serial numbers like follow and each of number is a record.
000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
000010
000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
000020
000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
000030
000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
000040
000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
000050
000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
000066
000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
000080
000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
000103 ...
Now, I use store procedure as follow and print the result
¡°SQL Store Procedure
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT srno
FROM all
WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
AND pqty IN ('Q','X') AND cate = 'IV'
ORDER BY srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
PRINT CAST(@.front AS VARCHAR(6))
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
PRINT CAST(@.frontsav AS VARCHAR(6))
END
ELSE
BEGIN
PRINT CAST(@.front AS VARCHAR(6))
PRINT CAST(@.frontsav AS VARCHAR(6))
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
¡°The Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
96¡@.100¡@.103
So, How could I make result like follow..
It's mean when numbers were continuous then use "DASH" to link.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
100¡@.103
Any advice, thanks!
AngiDo you mean you want the result to look like this?
1¡X51¡@.55¡@.57-58¡@.60¡X64¡@.66¡X68¡@.71¡@.
73-74¡@.76¡X80¡@.85¡X87¡@.89-90¡@.92¡@.95-96¡@.
100¡@.103
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
> 000010
> 000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
> 000020
> 000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
> 000030
> 000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
> 000040
> 000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
> 000050
> 000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
> 000066
> 000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
> 000080
> 000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> ¡°SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> ¡°The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
> 73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
> 96¡@.100¡@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> 73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
> 100¡@.103
> Any advice, thanks!
> Angi
>|||See if this helps:
-- Finding Range
declare @.tb table(i int)
insert @.tb select 1
union all select 2
union all select 3
union all select 5
union all select 6
union all select 7
union all select 8
union all select 21
union all select 22
union all select 50
select min(i) s, max(i) e
from (
select t1.i, count(t2.i) cnt
from @.tb t1 join @.tb t2 on t1.i >= t2.i
group by t1.i
)derived
group by cnt-i
order by s
--
-oj
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
> 000010
> 000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
> 000020
> 000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
> 000030
> 000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
> 000040
> 000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
> 000050
> 000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
> 000066
> 000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
> 000080
> 000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> ¡°SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> ¡°The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
> 73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
> 96¡@.100¡@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> 73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
> 100¡@.103
> Any advice, thanks!
> Angi
>|||Yes, and now I complete it, thanks!
as follow..
Store Procedure..
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
DECLARE @.test VARCHAR(7900)
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT cs_srno
FROM all_sc
WHERE cs_orgn = '2213' AND cs_ssym = '9401' AND cs_cemk = '1'
AND cs_pqty IN ('Q','X') AND cs_cate = 'IV'
ORDER BY cs_srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
SET @.test = RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
SET @.test = @.test + '¡@.' + RIGHT('000000' + CAST(@.frontsav AS
VARCHAR(6)), 6)
END
ELSE
BEGIN
SET @.test = @.test + '¡ã' + RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
+ '¡@.' + RIGHT('000000' + CAST(@.frontsav AS VARCHAR(6)), 6)
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
PRINT @.test
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
000001¡ã000051¡@.000055¡@.000057¡ã000058¡@.000060¡ã000064¡@.
000066¡ã000068¡@.000071¡@.000073¡ã000074¡@.000076¡ã000080¡@.
000085¡ã000087¡@.000089¡ã000090 ....
"wei xiao" <weix@.online.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:#$v$cSjHFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Do you mean you want the result to look like this?
> 1¡X51¡@.55¡@.57-58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> 73-74¡@.76¡X80¡@.85¡X87¡@.89-90¡@.92¡@.95-96¡@.
> 100¡@.103
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "angi" <enchiw@.sanrong.com.tw> wrote in message
> news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I have a serial numbers like follow and each of number is a record.
> >
> > 000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
> > 000010
> > 000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
> > 000020
> > 000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
> > 000030
> > 000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
> > 000040
> > 000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
> > 000050
> > 000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
> > 000066
> > 000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
> > 000080
> > 000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
> > 000103 ...
> >
> > Now, I use store procedure as follow and print the result
> >
> > ¡°SQL Store Procedure
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > DECLARE @.front INT
> > DECLARE @.frontsav INT
> > DECLARE @.frontbk INT
> > DECLARE @.key INT
> >
> > SET @.key = 0
> >
> > DECLARE spDashCursor CURSOR FOR
> > SELECT DISTINCT srno
> > FROM all
> > WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> > AND pqty IN ('Q','X') AND cate = 'IV'
> > ORDER BY srno
> > OPEN spDashCursor
> >
> > FETCH NEXT FROM spDashCursor INTO @.front
> > WHILE @.@.FETCH_STATUS = 0
> >
> > BEGIN
> >
> > IF @.key = 0
> > BEGIN
> > FETCH NEXT FROM spDashCursor INTO @.frontsav
> > PRINT CAST(@.front AS VARCHAR(6))
> > SET @.key = @.key + 1
> > END
> > ELSE
> > BEGIN
> > FETCH NEXT FROM spDashCursor INTO @.frontsav
> > END
> >
> > IF @.frontsav - @.front > 1
> > BEGIN
> > IF @.frontbk = @.front
> > BEGIN
> > PRINT CAST(@.frontsav AS VARCHAR(6))
> > END
> > ELSE
> > BEGIN
> > PRINT CAST(@.front AS VARCHAR(6))
> > PRINT CAST(@.frontsav AS VARCHAR(6))
> > END
> >
> > SET @.frontbk = @.frontsav
> > END
> >
> > SET @.front = @.frontsav
> > END
> >
> > CLOSE spDashCursor
> > DEALLOCATE spDashCursor
> > GO
> >
> > ¡°The Result
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > 1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
> > 73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
> > 96¡@.100¡@.103
> >
> > So, How could I make result like follow..
> > It's mean when numbers were continuous then use "DASH" to link.
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > 1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> > 73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
> > 100¡@.103
> >
> > Any advice, thanks!
> > Angi
> >
> >
>