SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need guidance on how to Insert a new record between existing records


Need guidance on how to Insert a new record between existing records

Author
Message
Nilssond
Nilssond
SSC Eights!
SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)

Group: General Forum Members
Points: 809 Visits: 556
Here is a the table structure:

MemberNbr Varchar(11)
MemberCardNumber Varchar(10)
EffectiveDate Int
TermDate Int


Sample data is:

Membernbr MemberCardNumber EffectiveDate TermDate
12345678909 A020129091 20120101 20120430
12345678909 A020129091 20120501 20120630
12345678909 A020129091 20120701 20120831
12345678909 A020129091 20120901 0

I receive an incoming file which indicates that this member updated his CardNumber to
B020129091 on 20120516 and I need to update the table to reflect this change in all his records
going forward from 20120516, overwriting, if necessary records which exist in the table and which
have effective dates > than 20120516 while also maintaining the TermDates.

Does anyone have some guidance on the SQL to do this?



Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41026 Visits: 38567
Would help if we know what the income record looked like for one. Also, if you could provide the DDL (CREATE TABLE) statement for the table, the sample data as a series of INSERT INTO statements, and what the expected results should look like when the code completes that would be helpful.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27030 Visits: 17557
This is nothing more than updating the table where date is greater than the date being passed in. The reason you are having a problem is because you have dates stored as an integer. You first have to painstakingly split that int into a usable datetime and this becomes trivial.


Update YourTable
set CardNumber = NewCardNumber
where MemberNbr = PassedInMemberNbr
and EffectiveDate >= PassedInDate



If there is ANY chance you can change this table to use the correct datatype for dates it will go a long way to making your life easier.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8278 Visits: 7163
Hmm, seems to me that in this case the query is the same either way, with exactly the same WHERE clause structure.

The only difference is that the WHERE comparison value, the value compared to the column value, is also an int instead of a date.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search