SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Merged/Renumbered Account Number
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sql_server_dba
Posting Yak Master

160 Posts

Posted - 03/18/2013 :  21:23:21  Show Profile  Reply with Quote
We have a system where many of our accountnumbers are getting modified. Below is a scenerio...

Source Table
AccountID AccountNumber AccountName
3 333 Test

The above table has only one record but it had two more records and they were deleted after renumbering.

Source table
OldAccountNumber NewAccountNumber NewAcountNumberTime
111 222 '2012-01-01 00:00:00.000'
222 333 '2012-05-06 00:00:00.000'

Target Table

SurrogateKey(ID) AccountID OldAccountNumber NewAccountNumber NewAcountNumberTime
1001 1 111 222 '2012-01-01 00:00:00.000'
1002 2 222 333 '2012-05-06 00:00:00.000'


AccountID AccountNumber AccountName Expired ExpireddateTime
1 111 Test 'Y' '2012-01-01 00:00:00.000'
2 222 Test 'Y' '2012-05-06 00:00:00.000'
3 333 Test 'N' NULL


AccountID for 1 and AccountID for 2 should belong to the same Account. What would be the easiest way to update AccountID of 1 and 2 to AccountID of 3? The above scenario is when the Acount Number changed once. It can actually change multiple times over a period of time.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/19/2013 :  00:58:24  Show Profile  Reply with Quote
I see two tables with name Source. I hope thats a typo.

You could use old new values in table to get ACcountIds remapped.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sql_server_dba
Posting Yak Master

160 Posts

Posted - 03/19/2013 :  02:06:53  Show Profile  Reply with Quote
Its actually not a Typo.

We have 2 source tables and 2 target tables. But in the target table, there should be only one AccountID(latest one). All the old ones should be updated to the latest one. Does that makes sense?
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
374 Posts

Posted - 03/20/2013 :  17:44:19  Show Profile  Reply with Quote
Something along these lines should do it:


--/* setup/initialization code
CREATE TABLE dbo.SourceTableHistory (
    OldAccountNumber int NOT NULL,
    NewAccountNumber int NOT NULL,
    NewAcountNumberTime datetime NULL
    )
SET NOCOUNT ON
TRUNCATE TABLE dbo.SourceTableHistory
INSERT INTO dbo.SourceTableHistory
--add some additional history to show that it is properly handled
SELECT 066, 077, '2011-06-01 00:00:00.000' UNION ALL
SELECT 077, 088, '2011-07-01 00:00:00.000' UNION ALL
SELECT 088, 099, '2011-08-01 00:00:00.000' UNION ALL
SELECT 099, 111, '2011-09-01 00:00:00.000' UNION ALL
SELECT 111, 222, '2012-01-01 00:00:00.000' UNION ALL
SELECT 222, 333, '2012-05-06 00:00:00.000'
SET NOCOUNT OFF

CREATE TABLE dbo.AccountNumberConversions (
    ident int IDENTITY(1, 1) NOT NULL,
    RecordType char(7) NOT NULL,
    OldAccountNumber int NOT NULL,
    NewAccountNumber int NOT NULL
    )
--*/

DECLARE @rowcount int

TRUNCATE TABLE dbo.AccountNumberConversions
    
INSERT INTO dbo.AccountNumberConversions
SELECT
    'Current', OldAccountNumber, NewAccountNumber
FROM dbo.SourceTableHistory sth1
WHERE
    NOT EXISTS(
        SELECT 1
        FROM dbo.SourceTableHistory sth2
        WHERE
            sth2.OldAccountNumber = sth1.NewAccountNumber
    )

SET @rowcount = 1
WHILE @rowcount > 0
BEGIN
    INSERT INTO dbo.AccountNumberConversions
    SELECT
        'History', sth_history.OldAccountNumber, anc_current.NewAccountNumber
    FROM dbo.SourceTableHistory sth_history
    INNER JOIN dbo.AccountNumberConversions anc_history ON
        anc_history.OldAccountNumber = sth_history.NewAccountNumber
    INNER JOIN dbo.AccountNumberConversions anc_current ON
        anc_current.NewAccountNumber = anc_history.NewAccountNumber AND
        anc_current.RecordType = 'Current'
    LEFT OUTER JOIN dbo.AccountNumberConversions anc_check_existing ON
        anc_check_existing.OldAccountNumber = sth_history.OldAccountNumber
    WHERE
        anc_check_existing.OldAccountNumber IS NULL
    SET @rowcount = @@ROWCOUNT
END --WHILE


--show list of all old account numbers and 
--the new number that should replace them
SELECT * FROM dbo.AccountNumberConversions

/*
UPDATE tt
SET
    tt.AccountNumber = anc.NewAccountNumber
FROM dbo.TargetTable tt
INNER JOIN dbo.AccountNumberConversions anc ON
    anc.OldAccountNumber = tt.AccountNumber
*/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/21/2013 :  02:03:32  Show Profile  Reply with Quote
so your target will have multiple records with same latest accountno (333) ? is that your requirement?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000