Query Help (next sequence 0001...0030...)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help (next sequence 0001...0030...)

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-01-20 : 11:55:25

[code]I need to update/insert sequence number into one of the column. Please see the output below and how
to construct the correct query to produce the desire output. SQL 2008

Thank you in advance for your help.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test]') AND type in (N'U'))
DROP TABLE [dbo].[Test]
GO

CREATE TABLE [dbo].[Test](
[JobId] [int] IDENTITY(1,1) NOT NULL,
[OrderId] [int] NOT NULL,
[JobTypeCode] [varchar](10) NOT NULL,
[JobSeq] [varchar](4) NOT NULL
) ON [PRIMARY]

GO

BEGIN TRANSACTION;
INSERT INTO [dbo].[Test]([JobId], [OrderId], [JobTypeCode], [JobSeq])
SELECT 1, 7, N'Review', N'0001' UNION ALL
SELECT 2, 7, N'Cure', N'0002' UNION ALL
SELECT 3, 7, N'Cure', N'0003' UNION ALL
SELECT 5, 8, N'Review', N'0001' UNION ALL
SELECT 6, 8, N'Cure', N'0002' UNION ALL
SELECT 7, 8, N'Cure', N'0003' UNION ALL
SELECT 8, 8, N'Cure', N'0004' UNION ALL
SELECT 9, 8, N'Cure', N'0005' UNION ALL
SELECT 11, 1, N'Review', N'' UNION ALL
SELECT 12, 1, N'Cure', N'' UNION ALL
SELECT 13, 1, N'Cure', N'' UNION ALL
SELECT 14, 1, N'Cure', N'' UNION ALL
SELECT 15, 1, N'Cure', N'' UNION ALL
SELECT 16, 1, N'Cure', N'' UNION ALL
SELECT 17, 1, N'Cure', N'' UNION ALL
SELECT 18, 1, N'Cure', N'' UNION ALL
SELECT 19, 1, N'Cure', N'' UNION ALL
SELECT 20, 1, N'Cure', N'' UNION ALL
SELECT 21, 1, N'Cure', N''
COMMIT;
RAISERROR (N'[dbo].[Test]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO

SELECT JobId,
OrderId,
JobTypeCode,
'000' + CAST(ROW_NUMBER() OVER(PARTITION BY OrderId ORDER BY OrderId ) AS VARCHAR(4)) AS 'JobSeq'
--,JobSeq
FROM Test
GO

JobId OrderId JobTypeCode JobSeq
----------- ----------- ----------- -------
11 1 Review 0001
12 1 Cure 0002
13 1 Cure 0003
14 1 Cure 0004
15 1 Cure 0005
16 1 Cure 0006
17 1 Cure 0007
18 1 Cure 0008
19 1 Cure 0009
20 1 Cure 00010 -- Wrong should be 0010
21 1 Cure 00011 -- same should be 0011

1 7 Review 0001
2 7 Cure 0002
3 7 Cure 0003

5 8 Review 0001
6 8 Cure 0002
7 8 Cure 0003
8 8 Cure 0004
9 8 Cure 0005

Desired output:

JobId OrderId JobTypeCode JobSeq
----------- ----------- ----------- -------
11 1 Review 0001
12 1 Cure 0002
13 1 Cure 0003
14 1 Cure 0004
15 1 Cure 0005
16 1 Cure 0006
17 1 Cure 0007
18 1 Cure 0008
19 1 Cure 0009
20 1 Cure 0010 <------- Need to replacing only 4 digits.
21 1 Cure 0011 <------- same.22 1 Cure 0230 <------- just a sample....

1 7 Review 0001
2 7 Cure 0002
3 7 Cure 0003

5 8 Review 0001
6 8 Cure 0002
7 8 Cure 0003
8 8 Cure 0004
9 8 Cure 0005[/code]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-20 : 12:10:05

SELECT JobId,
OrderId,
JobTypeCode,
RIGHT('000' + CAST(ROW_NUMBER() OVER(PARTITION BY OrderId ORDER BY OrderId ) AS VARCHAR(4))
,4) AS 'JobSeq'
--,JobSeq
FROM #Test

Jim

Everyday I learn something that somebody else already knew

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-01-20 : 12:28:04

Thanks Jim. Couldn't think that's simple.

quote:
Originally posted by jimf

SELECT JobId,
OrderId,
JobTypeCode,
RIGHT('000' + CAST(ROW_NUMBER() OVER(PARTITION BY OrderId ORDER BY OrderId ) AS VARCHAR(4))
,4) AS 'JobSeq'
--,JobSeq
FROM #Test

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources