March 8, 2013 at 3:09 pm
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?
March 8, 2013 at 3:14 pm
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.
March 8, 2013 at 3:21 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2013 at 5:19 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply