How Do I Select The Maximum Date For Each Record Having Duplicate ID

Hi All,
Here is my story, how to change a column called Flag_Status based on the maximum Updated date. i.e. i want to make Flag_Status be 1 for the records which have maximum Updated_date (current record) and the rest to make it 0. for example accountID 1 has three records updated, but only one is current the rest are historical, thus i want the history record to be Falg_status 0 and the current record be 1. Note that Inserted_Date and Updated_Date are created using SSIS Derived column During loading the source table, it helps me when each record is inserted into the Data warehouse.

Here is My source table Name: Source_table,


CREATE TABLE dbo.Source_table

(

AccountID INT PRIMARY KEY,

Price int,

Address varchar(30),

added DATETIME DEFAULT GETDATE(),

edited DATETIME DEFAULT GETDATE(),

Flag_Status Int Default 1

)

GO

Here is the Fact_table


CREATE TABLE dbo.Fact_table

(

Fact_table_Key Int Identity (1, 1) NOT NULL,

AccountID INT,

Price INT,

Address varchar(30),

added DATETIME DEFAULT GETDATE(),

edited DATETIME DEFAULT GETDATE(),

editor VARCHAR(64),

Flag_Status Int Default 1,

Inserted_Date DATETIME DEFAULT GETDATE(),

Updated_Date DATETIME DEFAULT GETDATE()

)

GO

Source_table:

AccountID Price Address added edited Flag_Status
---------------- --------- ------------- ----------- ------------- ------------------
1 10 xyz 01-2006 01-2006 1
2 14 abc 02-2006 02-2006 1
3 13 mno 03-2006 03-2006 1

Here is the fact table, table name= Fact_table



Fact_table_Key AccountID Price Address added edited Flag_Status Inserted_Date Updated_Date
-------------------- ------------- --------- ------------ ------- --------- ---------------- ------------------ -------------------
1 1 10 xyz 01-2006 01-2006 1 05-2006 NULL
2 2 14 abc 02-2006 02-2006 1 05-2006 NULL
3 3 13 mno 03-2006 03-2006 1 05-2006 NULL
4 1 17 ght 01-2006 06-2006 1 NULL 08-2006
5 2 18 dmc 02-2006 07-2006 1 NULL 08-2006
6 3 20 kmc 03-2006 09-2006 1 NULL 10-2006
7 1 19 xyz 01-2006 11-2006 1 NULL 12-2006
8 2 19 klm 02-2006 01-2007 1 NULL 02-2007
9 3 21 pqr 03-2006 03-2007 1 NULL 04-2007

Here is what i am thinking: But it gives me Wrong Flag_Status.


UPDATE Fact_table

SET Flag_Status =


CASE


WHEN (SELECT Max(Updated_Date) From Fact_table

WHERE AccountID IN (SELECT AccountID FROM Fact_table

Group By AccountID Having Count(AccountID)>1)) >

(SELECT Max(edited) From Source_table

WHERE Flag_Status = 1)

THEN 1


WHEN (SELECT AccountID From Source_table

Group By AccountID

Having Count(AccountID) =1) =

(SELECT AccountID From Fact_table

WHERE Updated_Date IS NULL)

THEN 1

ELSE 0

END