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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1X51@.55@.57X58@.60X64@.66X68@.71@.
73X74@.76X80@.85X87@.89X90@.92@.95X
96@.
100@.103
Any advice, thanks!
AngiDo you mean you want the result to look like this?
1X51@.55@.57-58@.60X64@.66X68@.71@.
73-74@.76X80@.85X87@.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.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1X51@.55@.57X58@.60X64@.66X68@.71@.
> 73X74@.76X80@.85X87@.89X90@.92@.95X
96@.
> 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.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1X51@.55@.57X58@.60X64@.66X68@.71@.
> 73X74@.76X80@.85X87@.89X90@.92@.95X
96@.
> 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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~
000001000051@.000055@.000057000058@.
000060000064@.
000066000068@.000071@.000073000074@.
000076000080@.
000085000087@.000089000090 ....
"wei xiao" <weix@.online.microsoft.com> glsD
:#$v$cSjHFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Do you mean you want the result to look like this?
> 1X51@.55@.57-58@.60X64@.66X68@.71@.
> 73-74@.76X80@.85X87@.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...
>