Can anybody help me with the below issue?

--===== Create the test table with

CREATE TABLE [dbo].[mytable](

[date] [datetime] NULL,

[Area] [nchar](10) NULL,

[Amount] [nchar](10) NULL,

[AOD] [datetime] NULL

) ON [PRIMARY]

--===== Insert the test data into the test table

Insert into mytable

SELECT 'Jan 1 2008 12:00AM','BA ','0 ','Jan 1 2008 12:00AM' UNION ALL

SELECT 'Feb 1 2008 12:00AM','BA ','50 ','Jan 1 2008 12:00AM' UNION ALL

SELECT 'Mar 1 2008 12:00AM','BA ','40 ','Jan 1 2008 12:00AM' UNION ALL

SELECT 'Apr 1 2008 12:00AM','BA ','30 ','Jan 1 2008 12:00AM' UNION ALL

SELECT 'May 1 2008 12:00AM','BA ','20 ','Jan 1 2008 12:00AM' UNION ALL

SELECT 'Jan 1 2008 12:00AM','BA ','0 ','Feb 1 2008 12:00AM' UNION ALL

SELECT 'Feb 1 2008 12:00AM','BA ','0 ','Feb 1 2008 12:00AM' UNION ALL

SELECT 'Mar 1 2008 12:00AM','BA ','30 ','Feb 1 2008 12:00AM' UNION ALL

SELECT 'Apr 1 2008 12:00AM','BA ','40 ','Feb 1 2008 12:00AM' UNION ALL

SELECT 'May 1 2008 12:00AM','BA ','50 ','Feb 1 2008 12:00AM' UNION ALL

SELECT 'Jan 1 2008 12:00AM','BA ','0 ','Mar 1 2008 12:00AM' UNION ALL

SELECT 'Feb 1 2008 12:00AM','BA ','0 ','Mar 1 2008 12:00AM' UNION ALL

SELECT 'Jan 1 2008 12:00AM','BA ','0 ','Apr 1 2008 12:00AM' UNION ALL

SELECT 'Feb 1 2008 12:00AM','BA ','0 ','Apr 1 2008 12:00AM' UNION ALL

SELECT 'Mar 1 2008 12:00AM','BA ','0 ','Apr 1 2008 12:00AM' UNION ALL

SELECT 'Apr 1 2008 12:00AM','BA ','0 ','Apr 1 2008 12:00AM' UNION ALL

SELECT 'May 1 2008 12:00AM','BA ','20 ','Apr 1 2008 12:00AM' UNION ALL

SELECT 'Mar 1 2008 12:00AM','BA ','0 ','Mar 1 2008 12:00AM' UNION ALL

SELECT 'Apr 1 2008 12:00AM','BA ','20 ','Mar 1 2008 12:00AM' UNION ALL

SELECT 'May 1 2008 12:00AM','BA ','10 ','Mar 1 2008 12:00AM'

--Select the record using the below statement

Select * from MyTable Order by AOD,Date

I want to update the Amount column which has value zero with previous month's(AOD column) value (dont want to hardcode it)

For example

I want to Update

Select Amount from MyTable Where AOD='02/01/2008' and date='02/01/2008'

with

Select Amount from mytable where AOD='01/01/2008' and date='02/01/2008'

Final result should look like this

DateAreaAmountAOD

2008-01-01 00:00:00.000BA 0 2008-01-01 00:00:00.000

2008-02-01 00:00:00.000BA 50 2008-01-01 00:00:00.000

2008-03-01 00:00:00.000BA 40 2008-01-01 00:00:00.000

2008-04-01 00:00:00.000BA 30 2008-01-01 00:00:00.000

2008-05-01 00:00:00.000BA 20 2008-01-01 00:00:00.000

2008-01-01 00:00:00.000BA 0 2008-02-01 00:00:00.000

2008-02-01 00:00:00.000BA 50 2008-02-01 00:00:00.000

2008-03-01 00:00:00.000BA 30 2008-02-01 00:00:00.000

2008-04-01 00:00:00.000BA 40 2008-02-01 00:00:00.000

2008-05-01 00:00:00.000BA 50 2008-02-01 00:00:00.000

2008-01-01 00:00:00.000BA 0 2008-03-01 00:00:00.000

2008-02-01 00:00:00.000BA 50 2008-03-01 00:00:00.000

2008-03-01 00:00:00.000BA 30 2008-03-01 00:00:00.000

2008-04-01 00:00:00.000BA 20 2008-03-01 00:00:00.000

2008-05-01 00:00:00.000BA 10 2008-03-01 00:00:00.000

2008-01-01 00:00:00.000BA 0 2008-04-01 00:00:00.000

2008-02-01 00:00:00.000BA 50 2008-04-01 00:00:00.000

2008-03-01 00:00:00.000BA 30 2008-04-01 00:00:00.000

2008-04-01 00:00:00.000BA 20 2008-04-01 00:00:00.000

2008-05-01 00:00:00.000BA 20 2008-04-01 00:00:00.000

Thanks alot for the help

Neela