Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Need guidance on how to Insert a new record between existing records Expand / Collapse
Posted Friday, March 8, 2013 3:09 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 708, Visits: 494
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?

Post #1428781
Posted Friday, March 8, 2013 3:14 PM



Group: General Forum Members
Last Login: Yesterday @ 4:31 PM
Points: 21,908, Visits: 34,659
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.

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)
Post #1428784
Posted Friday, March 8, 2013 3:21 PM



Group: General Forum Members
Last Login: Yesterday @ 3:45 PM
Points: 14,360, Visits: 14,098
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 for best practices on asking questions.

Need to split a string? Try Jeff Moden's 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)
Post #1428793
Posted Friday, March 8, 2013 5:19 PM


Group: General Forum Members
Last Login: Yesterday @ 4:02 PM
Points: 2,946, Visits: 4,495
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1428816
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse