Hi I'm not sure if DTS is the correct way to do this or stored procedure.

Here is the code to produce and populate my test table

CREATE TABLE [tblEntitlement] (
[strLogonName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[intYear] [int] NOT NULL ,
[decPreviousBorrowed] [decimal](18, 0) NULL ,
[decNextBorrowed] [decimal](18, 0) NULL ,
PRIMARY KEY CLUSTERED
(
[strLogonName],
[intYear]
) ON [PRIMARY]
) ON [PRIMARY]
GO


INSERT INTO [tblEntitlement]([strLogonName], [intYear],
[decPreviousBorrowed], [decNextBorrowed])
VALUES('AlanT', 2004, 0, 1)
INSERT INTO [tblEntitlement]([strLogonName], [intYear],
[decPreviousBorrowed], [decNextBorrowed])
VALUES('AlanT', 2005, 0, 2)
INSERT INTO [tblEntitlement]([strLogonName], [intYear],
[decPreviousBorrowed], [decNextBorrowed])
VALUES('AlanT', 2006, 0, 3)
INSERT INTO [tblEntitlement]([strLogonName], [intYear],
[decPreviousBorrowed], [decNextBorrowed])
VALUES('BrianE', 2004, 0, 4)
INSERT INTO [tblEntitlement]([strLogonName], [intYear],
[decPreviousBorrowed], [decNextBorrowed])
VALUES('BrianE', 2005, 0, 5)


This will produce the following table.

strLogonName intYear decPreviousBorrowed decNextBorrowed
AlanT 2004 0 1
AlanT 2005 0 2
AlanT 2006 0 3
BrianE 2004 0 4
BrianE 2005 0 5


I’m trying to run a DTS or SP that will update the
decPreviousBorrowed field with the value of the decNextBorrowed field from
the year before. So this is what I should get.


strLogonName intYear decPreviousBorrowed decNextBorrowed
AlanT 2004 0 1
AlanT 2005 1 2
AlanT 2006 2 3
BrianE 2004 0 4
BrianE 2005 4 5

You can try simple SQL to do this. like:

update table [tblEntitlement]
set decPreviousBorrowed = T1.decNextBorrowed
from [tblEntitlement] T1 join [tblEntitlement] T2
ON T1.strLogonName = T2.strLogonName
and T1.intYear - 1 = t2.intYear
This can be a SP or a Execute SQL Task on a DTS package.

Hope to have helped


"jez123456" wrote:

In article <FEF972D1-0B20-40DE-89AF-96B70ABD67BB (AT) microsoft (DOT) com>, Jez123456
wrote:

If you must use DTS then use the ExecuteSQL task and an SQL statement.


--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know