I have to implement processing where, if a "backdated" record occurs, update a column for all records greater than or equal to the backdate.
Say I have this data in my target table:
Code: Select all
KEY DATE FLAG_VAR
1 20090701 T
1 20090702 T
1 20090703 T
1 20090704 T
2 20090701 F
2 20090702 F
2 20090703 F
2 20090704 F
Code: Select all
BACKDATED_FLAG KEY DATE FLAG_VAR
T 1 20090702 F
F 3 20090705 F
F 4 20090705 T
Code: Select all
KEY DATE FLAG_VAR
1 20090701 T
1 20090702 F
1 20090703 F
1 20090704 F
2 20090701 F
2 20090702 F
2 20090703 F
2 20090704 F
3 20090705 F
4 20090705 T
The target updates are split based on Backdated_Flag (i.e. two output links to the target table). Non-backdated data is Update or Insert (and updates many more columns than the dummy data above), and backdated data is Update Existing Row Only (any only updates a select number of columns).
That's the dummy data to illustrate the problem. My "real" generated SQL for the backdated link is:
Code: Select all
UPDATE Source.Account SET AccountOptedInOut=? WHERE SourceSystem=? AND EffectiveDate=? AND AccountNumber=?
I'm pretty sure I can't use the "?" syntax in user written SQL code.UPDATE Source.Account SET AccountOptedInOut=? WHERE SourceSystem=? AND EffectiveDate>=? AND AccountNumber=?
The target database is SQL Server.
So, any ideas on how to accomplish this?
Thanks,
Scott