Insert sequence number

Question

Insert sequence number

adonetok

SSCrazy Eights

Points: 8395
More actions
July 21, 2014 at 2:17 pm

#288050

I need help to code to add sequence number.
One claim number may have various max sequence number.
For example,
1) Claim number: 111 max sequence: 17
2) Claim number: 222 max sequence: 38
For 1) I need code to insert 18,19,20
For 2) I need code to insert 39,40
That is, based on max sequence number, inserting will up to 20,40,60...

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Why? How do you know you should get to 20, 40, etc? What are your expected results? Could you post sample data in a consumable format?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

adonetok SSCrazy Eights Points: 8395 More actions · Answer 2

I use

select max(sequence)

to find out max sequence number

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

adonetok (7/21/2014)
I need help to code to add sequence number.
One claim number may have various max sequence number.
For example,
1) Claim number: 111 max sequence: 17
2) Claim number: 222 max sequence: 38
For 1) I need code to insert 18,19,20
For 2) I need code to insert 39,40
That is, based on max sequence number, inserting will up to 20,40,60...

Insert what where?

Really need DDL, sample data, and expected results if you want any.

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 4

adonetok (7/21/2014)
I use
select max(sequence)
to find out max sequence number

I have to admit, I cringe a little every time I see a recommendation like that. There's just too much of a chance of either getting deadlocks if you do it right or getting duplicate values if you do it wrong.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wBob Right there with Babe Points: 715 More actions · Answer 5

Here's an example of how to do something like that using Common Table Expressions ( CTEs ):

USE tempdb

GO

IF OBJECT_ID('#claims') IS NOT NULL DROP TABLE #claims

CREATE TABLE #claims

(

claimNumberINT,

maxSequenceINT,

PRIMARY KEY ( claimNumber, maxSequence )

)

GO

INSERT INTO #claims VALUES

( 111, 17 ), ( 222, 38 ), ( 333, 0 )-- won't rollup for maxSequence = 0

GO

;WITH cte AS

(

SELECT claimNumber, MAX(maxSequence) maxSequence

FROM #claims

WHERE maxSequence > 0

GROUP BY claimNumber

), cte2 AS

(

SELECT 0 x, 0 y

UNION ALL

SELECT x + 1, y + CASE WHEN ( x % 20 ) = 0 THEN 20 ELSE 0 END

FROM cte2

WHERE x < 1000-- Assumes MAX maxSequence is less than 1000

)

--INSERT INTO #claims ( claimNumber, maxSequence )

SELECT c.claimNumber, z.x AS newSequenceNumber

FROM cte c

CROSS APPLY ( SELECT MIN(y) y FROM cte2 WHERE y > c.maxSequence ) a-- Work out the nearest multiple of 20

CROSS JOIN cte2 z-- Roll it up to the nearest multiple of 20

WHERE z.x > c.maxSequence

AND a.y = z.y

ORDER BY c.claimNumber, c.maxSequence

OPTION ( MAXRECURSION 1000 )

Uncomment the INSERT statement if you actually want to add the records into the #claims temp table.

I think a numbers table would probably scale better if you have high volumes, but this example would easily convert.

HTH

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

-- Make some sample data

DROP TABLE #SampleTable

SELECT *

INTO #SampleTable

FROM (

SELECT TOP 17 [Claim number] = 111, [sequence number] = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM SYSCOLUMNS

UNION ALL

SELECT TOP 38 [Claim number] = 222, [sequence number] = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM SYSCOLUMNS

UNION ALL

SELECT TOP 41 [Claim number] = 333, [sequence number] = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM SYSCOLUMNS

UNION ALL

SELECT TOP 79 [Claim number] = 444, [sequence number] = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM SYSCOLUMNS

) d

-- Demonstrate a solution using the sample data

SELECT

d.[Claim number],

[sequence number] = d.LastSequenceNumber + x.n

FROM (

SELECT

[Claim number],

RowsToConstruct = 20-(MAX([sequence number])%20),

LastSequenceNumber = MAX([sequence number])

FROM #SampleTable

GROUP BY [Claim number]

) d

CROSS APPLY (SELECT TOP (RowsToConstruct) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM SYSCOLUMNS) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Hey Bob,

I suggest that you stop using rCTEs to count as they're a form of hidden RBAR and will cause problems. And low row counts don't justify the use of bad code.

Check the following article: http://www.sqlservercentral.com/articles/T-SQL/74118/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Luis Cazares (7/22/2014)
Hey Bob,
I suggest that you stop using rCTEs to count as they're a form of hidden RBAR and will cause problems. And low row counts don't justify the use of bad code.
Check the following article: http://www.sqlservercentral.com/articles/T-SQL/74118/

Didn't even catch that, but then I stopped reading his code as soon as I saw the semicolon (;) being used as a statement begininator instead of as a statement terminator. Sorry, pet peeve. Bothers the heck out of me.

wBob Right there with Babe Points: 715 More actions · Answer 9

Nice and neat, but will roll up maxSequence of 0 to 20. Not to say that the OP has any of those, maybe they do; maybe they don't : )

pietlinden SSC Guru Points: 63488 More actions · Answer 10

What's totally odd is that MS does that with their examples of CTEs - all of them begin with a semi-colon, which was really weird to me. Gotta love the "authority" promoting bad practices!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

pietlinden (7/22/2014)
What's totally odd is that MS does that with their examples of CTEs - all of them begin with a semi-colon, which was really weird to me. Gotta love the "authority" promoting bad practices!

Who says I pay attention to their examples? :w00t:

wBob Right there with Babe Points: 715 More actions · Answer 12

Having taken all your comments into consideration, and with a similar approach to Chris, this option was best for me at scale ( 1 million ):

USE tempdb

GO

IF OBJECT_ID('#claims') IS NOT NULL DROP TABLE #claims

CREATE TABLE #claims

(

claimNumberINT,

maxSequenceINT,

PRIMARY KEY ( claimNumber, maxSequence )

)

GO

INSERT INTO #claims VALUES

( 111, 17 ), ( 222, 38 ), ( 333, 0 )

GO

IF OBJECT_ID('dbo.numbers') IS NULL

BEGIN

CREATE TABLE dbo.numbers ( x INT, CONSTRAINT PK_numbers PRIMARY KEY ( x ) )

;INSERT INTO dbo.numbers ( x )

SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) x

FROM master.sys.columns c1

CROSS JOIN master.sys.columns c2

CROSS JOIN master.sys.columns c3

ORDER BY 1

END

-- This was good at scale

SELECT claimNumber, maxSequence + x.x AS maxSequence

FROM (

SELECT

claimNumber,

MAX(maxSequence) maxSequence,

( ( MAX(maxSequence) / 20 ) + 1 ) * 20 [rollupTo]

FROM #claims

WHERE maxSequence > 0

GROUP BY claimNumber

) c

CROSS JOIN dbo.numbers x

WHERE x.x <= [rollupTo] - maxSequence

I prefer numbers table to syscolumns, eg you have to be careful over on sqlfiddle where that view only has 48 rows.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 13

@wBob, you might find a TOP() -limited IBG-style inline tally even more efficient.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

wBob (7/22/2014)
Having taken all your comments into consideration, and with a similar approach to Chris, this option was best for me at scale ( 1 million ):
USE tempdb
GO
IF OBJECT_ID('#claims') IS NOT NULL DROP TABLE #claims
CREATE TABLE #claims
(
claimNumberINT,
maxSequenceINT,
PRIMARY KEY ( claimNumber, maxSequence )
)
GO
INSERT INTO #claims VALUES
( 111, 17 ), ( 222, 38 ), ( 333, 0 )
GO
IF OBJECT_ID('dbo.numbers') IS NULL
BEGIN
CREATE TABLE dbo.numbers ( x INT, CONSTRAINT PK_numbers PRIMARY KEY ( x ) )
;INSERT INTO dbo.numbers ( x )
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) x
FROM master.sys.columns c1
CROSS JOIN master.sys.columns c2
CROSS JOIN master.sys.columns c3
ORDER BY 1
END
-- This was good at scale
SELECT claimNumber, maxSequence + x.x AS maxSequence
FROM (
SELECT
claimNumber,
MAX(maxSequence) maxSequence,
( ( MAX(maxSequence) / 20 ) + 1 ) * 20 [rollupTo]
FROM #claims
WHERE maxSequence > 0
GROUP BY claimNumber
) c
CROSS JOIN dbo.numbers x
WHERE x.x <= [rollupTo] - maxSequence
I prefer numbers table to syscolumns, eg you have to be careful over on sqlfiddle where that view only has 48 rows.

Pet peeve time again. Now there is a semicolon in front the INSERT statement.

Issue 1, ORDER BY 1. IIRC ORDER BY ordinal has been deprecated and may be removed from future versions of SQL Server. Also, it obfuscates what you are sorting on and, if the column list changes can easily break. You really should explicitly specify the column name(s) you are sorting on.

Using a CTE your new code would look like this:

WITH c as (

SELECT

claimNumber,

MAX(maxSequence) maxSequence,

( ( MAX(maxSequence) / 20 ) + 1 ) * 20 [rollupTo]

FROM #claims

WHERE maxSequence > 0

GROUP BY claimNumber

)

SELECT

claimNumber,

maxSequence + x.x AS maxSequence

FROM

c

CROSS JOIN dbo.numbers x

WHERE

x.x <= [rollupTo] - maxSequence;

There is more but I really should do some work right now.