hi , thanks for it. here is the details . table design, stored procedure that should be called to do the update based on composite primary key to get a unique combination

CREATE TABLE [dbo].[Monthlyaccr](

[AccrualDate] [date] NOT NULL,

[CostName] [varchar](100) NOT NULL,

[IncomeExpense] [varchar](50) NOT NULL,

[Counterparty] [varchar](65) NOT NULL,

[Memo] [varchar](100) NOT NULL,

[AccrualMonth] [varchar](3) NOT NULL,

[Year] [varchar](4) NOT NULL,

[AccrualAmount] [decimal](10, 2) NOT NULL,

[AccrualCurrency] [varchar](15) NOT NULL,

[AccrualFXtoBase] [decimal](10, 4) NOT NULL,

[ProfitCenter] [varchar](30) NOT NULL,

[Strategy] [varchar](30) NOT NULL,

[__not_for_extract_Corporate] [varchar](6) NOT NULL,

CONSTRAINT [PK_MonthlyAccrs] PRIMARY KEY CLUSTERED

(

[AccrualDate] ASC,

[CostName] ASC,

[IncomeExpense] ASC,

[Counterparty] ASC,

[AccrualMonth] ASC,

[Year] ASC,

[AccrualCurrency] ASC,

[ProfitCenter] ASC,

[Strategy] ASC,

[__not_for_extract_Corporate] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[MonthlyAccrs] WITH CHECK ADD CONSTRAINT [FK_MonthlyAccrs_CostComponents] FOREIGN KEY([CostName])

REFERENCES [dbo].[CostComponents] ([CostComponent])

GO

ALTER TABLE [dbo].[MonthlyAccrs] CHECK CONSTRAINT [FK_MonthlyAccrs_CostComponents]

GO

ALTER TABLE [dbo].[MonthlyAccrs] WITH CHECK ADD CONSTRAINT [FK_MonthlyAccrs_Counterparty] FOREIGN KEY([Counterparty])

REFERENCES [dbo].[BusinessPartnerSetup] ([BusinessPartnerShortName])

GO

ALTER TABLE [dbo].[MonthlyAccrs] CHECK CONSTRAINT [FK_MonthlyAccrs_Counterparty]

GO

ALTER TABLE [dbo].[MonthlyAccrs] WITH CHECK ADD CONSTRAINT [FK_MonthlyAccrs_Currencies] FOREIGN KEY([AccrualCurrency])

REFERENCES [dbo].[Currencies] ([CurrencyCode])

GO

ALTER TABLE [dbo].[MonthlyAccrs] CHECK CONSTRAINT [FK_MonthlyAccrs_Currencies]

GO

ALTER TABLE [dbo].[MonthlyAccrs] WITH CHECK ADD CONSTRAINT [FK_MonthlyAccrs_Strategies] FOREIGN KEY([__not_for_extract_Corporate], [Strategy])

REFERENCES [dbo].[Strategies] ([Corporate], [Strategy])

GO

ALTER TABLE [dbo].[MonthlyAccrs] CHECK CONSTRAINT [FK_MonthlyAccrs_Strategies]

GO

ALTER TABLE [dbo].[MonthlyAccrs] WITH CHECK ADD CONSTRAINT [CK_MonthlyAccrs_AccrualMonth] CHECK (([AccrualMonth]='Dec' OR [AccrualMonth]='Nov' OR [AccrualMonth]='Oct' OR [AccrualMonth]='Sep' OR [AccrualMonth]='Aug' OR [AccrualMonth]='Jul' OR [AccrualMonth]='Jun' OR [AccrualMonth]='May' OR [AccrualMonth]='Apr' OR [AccrualMonth]='Mar' OR [AccrualMonth]='Feb' OR [AccrualMonth]='Jan'))

GO

ALTER TABLE [dbo].[MonthlyAccrs] CHECK CONSTRAINT [CK_MonthlyAccrs_AccrualMonth]

GO

ALTER TABLE [dbo].[MonthlyAccrs] WITH CHECK ADD CONSTRAINT [CK_MonthlyAccrs_IncomeExpense] CHECK (([IncomeExpense]='Expense' OR [IncomeExpense]='Income'))

GO

ALTER TABLE [dbo].[MonthlyAccrs] CHECK CONSTRAINT [CK_MonthlyAccrs_IncomeExpense]

GO

ALTER TABLE [dbo].[MonthlyAccrs] WITH CHECK ADD CONSTRAINT [CK_MonthlyAccrs_Year] CHECK (([Year] like '19__' OR [Year] like '20__' OR [Year] like '21__'))

GO

ALTER TABLE [dbo].[MonthlyAccrs] CHECK CONSTRAINT [CK_MonthlyAccrs_Year]

GO

=======================

stored procedure to update the records in the the table using cursor and temp table.

CREATE PROCEDURE [trn].[test]

-- Add the parameters for the stored procedure here

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @tblMonthlyAccruals sysname

DECLARE @tblMonthlyAccrualsReport sysname

DECLARE @sqlText varchar(2048)

--DECLARE @tempTablename sysname

SET@tblMonthlyAccruals = '[trn].[temp_tblMonthlyAccr]'

--SET@tblMonthlyAccrualsReport = '[trn].[tblMonthlyAccr]'

SET@sqlText = 'DROP TABLE ' + @tblMonthlyAccruals

IFEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tblMonthlyAccr) AND type in (N'U'))

BEGIN

print @sqlText

exec( @sqlText)

END

ELSE

PRINT@sqlText + ' - has not been executed as it does not exist.'

SET@sqlText = 'DROP TABLE ' + @tblMonthlyAccrualsReport

IFEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tblMonthlyAccrualsReport) AND type in (N'U'))

BEGIN

print @sqlText

exec( @sqlText)

END

ELSE

PRINT@sqlText + ' - has not been executed as it does not exist.'

/*Copying data from Source table to table*/

SELECTIDENTITY (INT,1,1) AS VirtualID,--temp id

COALESCE((CONVERT(date,CostAccrual.[Transaction Date],106)),CONVERT(date,'1900-01-01',102)) as 'AccrualDate',

case

WHEN CostAccrual.[Cost Category] = 'Freight' and LTRIM(RTRIM(CostAccrual.[Vendor Full Name])) in ('ADM Clearing','test Operations - tRail')

THEN

'Rail Freight-EOM'

WHEN CostAccrual.[Cost Category] = 'Ft' and LTRIM(RTRIM(CostAccrual.[Vendor Full Name])) = 'Road Freight Trader'

THEN

'Road Ft-EOM'

ELSE

COALESCE(CostComponentsMapping.[EKACostComponentName],'<ErrorInCostName>')

end as 'CostName' ,

'Expense' as 'IncomeExpense',

COALESCE(BusinessPartnerRefTypes.BusinessPartnerShortName,'<ErrorInCounterparty>') as 'Counterparty',

case

when CostAccrual.[Cost Category] = 'Ft' Then COALESCE(CostAccrual.[Vehicle Id],'<ErrorInCostCategory>')

else COALESCE(cast(cast(CostAccrual.[Transaction No]as bigint)as varchar(255)),'<ErrorInCostCategory>')

end as 'Memo',

COALESCE(CONVERT(varchar(3),CostAccrual.[Transaction Date],107) ,'<ErrorInAccrualMonth>')as 'AccrualMonth',

COALESCE(CONVERT(varchar(4),CostAccrual.[Transaction Date],120),'<ErrorInYear>') as 'Year',

COALESCE(CONVERT(decimal(10,4),CostAccrual.[Total]),0) as 'AccrualAmount',

COALESCE(CostAccrual.[Currency],'<ErrorInAccrualCurrency>') as 'AccrualCurrency',

end as 'AccrualFXtoBase',

--COALESCE(CONVERT(decimal(10,4),[Rate]),0) as 'AccrualFXtoBase',

COALESCE(ProfitCenterMapping.[ProfitCenterName],'<ErrorInProfitCenter>') as 'ProfitCenter',

'abc' as 'Strategy',

'GCO' as '__not_for_extract_Corporate'

INTO[trn].[temp_tblMonthlyAccruals]

FROM[source].[CostAccrualReport] CostAccrual

LEFT OUTER JOIN[lookup].[ProfitCenterMapping] ProfitCenterMapping0

ONCostAccrual.[ID Centre]=ProfitCenterMapping.[ProfitCenter]

LEFT OUTER JOIN[lookup].[CostComponentsMapping] CostComponentsMapping

ON(CostAccrual.[Cost Category]=CostComponentsMapping.[GSCostComponentName]

and (CostAccrual.[Cost Category] <> 'Freight'

and CostAccrual.[Vendor Full Name] not in ('test Operations - Rail','Road Freight Trader')))

LEFT OUTER JOIN [dbo].[BusinessPartnerRefTypes] BusinessPartnerRefTypes

ONBusinessPartnerRefTypes.BusinessPartnerReferenceValue = CostAccrual.[Vendor Id]

WHERE

CostAccrual.Amount >= 100-- As per email from Don

DECLARE @Counter INT = 0

DECLARE @VirtualIDINT

DECLARE @VirtualID_curCURSOR

SET @VirtualID_cur= CURSOR FOR

SELECT VirtualID FROM [trn].[temp_tblMonthlyAccruals]

OPEN @VirtualID_cur

FETCH NEXT

FROM @VirtualID_cur INTO @VirtualID

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @VirtualID

UPDATE[trn].[temp_tblMonthlyAccruals]

SET[AccrualDate] = DATEADD(dd, @Counter, '31-May-2013')

WHEREVirtualID = @VirtualID

SET @Counter = @Counter - 1

FETCH NEXT FROM @VirtualID_cur INTO @VirtualID

END

CLOSE @VirtualID_cur

DEALLOCATE @VirtualID_cur

WHILE EXISTS(SELECT[AccrualDate],

[Counterparty],

[ProfitCenter],

[CostName],

[AccrualCurrency]

FROM[trn].[temp_tblMonthlyAccruals]

GROUP BY [AccrualDate],

[Counterparty],

[ProfitCenter],

[CostName],

[AccrualCurrency]

HAVING count(*) > 1)

BEGIN

SET ROWCOUNT 1

UPDATE[trn].[temp_tblMonthlyAccruals]

SET[AccrualDate] = DATEADD(D, -1, [AccrualDate]) --changed day to d

FROM[trn].[temp_tblMonthlyAccruals] ma1

WHERE[AccrualDate] = ma1.[AccrualDate]

ANDEXISTS(SELECT[AccrualDate],

[Counterparty],

[ProfitCenter],

[CostName],

[AccrualCurrency]

FROM[trn].[temp_tblMonthlyAccruals] ma2

WHEREma1.[AccrualDate]=ma2.[AccrualDate]

andma1.[Counterparty]=ma2.[Counterparty]

andma1.[ProfitCenter]=ma2.[ProfitCenter]

andma1.[CostName]=ma2.[CostName]

andma1.[AccrualCurrency]=ma2.[AccrualCurrency]

GROUP BY ma2.[AccrualDate],

ma2.[Counterparty],

ma2.[ProfitCenter],

ma2.[CostName],

ma2.[AccrualCurrency]

HAVING count(*) > 1

)

SET ROWCOUNT 0

END

/*Select statement to create the monthly accruals report*/

SELECTConvert(char(2),MonthlyAccruals.[Accrualdate],106)+'-'+CONVERT(char(3),MonthlyAccruals.[Accrualdate],107)+'-'+CONVERT(char(4),MonthlyAccruals.[Accrualdate],120) as 'Accrual date',

MonthlyAccruals.[CostName] as 'Cost Name' ,

MonthlyAccruals.[IncomeExpense] as 'Income/Expense',

MonthlyAccruals.[Counterparty] as 'Counterparty',

MonthlyAccruals.[Memo] as 'Memo',

MonthlyAccruals.[AccrualMonth] as 'Accrual Month',

MonthlyAccruals.[Year] as 'Year',

MonthlyAccruals.[AccrualAmount] as 'Accrual Amount',

MonthlyAccruals.[AccrualCurrency] as 'Accrual Currency',

MonthlyAccruals.[AccrualFXtoBase] as 'Accrual to Base',

MonthlyAccruals.[ProfitCenter] as 'Profit Center',

MonthlyAccruals.[Strategy] as 'Strategy'

INTOdbo.testmonthlyacrr

FROM[trn].[temp_tblMonthlyAccrr] MonthlyAccruals

END

GO