woody_rd (11/24/2015)
Looking for a new column with the difference between most recent and it's predecessor. A dataset such as this:CustomerID, CustomerName, AuditNum, AuditDate, DateDiff
11111, Cust1, 1, 2015-10-01, 0
11112, Cust2, 1, 2015-10-02, 0
11113, Cust3, 1, 2015-10-01, 14
11113, Cust3, 2, 2015-09-15, 16
11113, Cust3, 3, 2015-08-30, 0
11114, Cust4, 1, 2015-10-10, 0
11115, Cust5, 1, 2015-09-30, 20
11115, Cust5, 2, 2015-09-10, 9
11115, Cust5, 3, 2015-09-01, 17
11115, Cust5, 4, 2015-08-15, 0
11116, Cust6, 1, 2015-11-01, 0
11117, Cust7, 1, 2015-10-31, 0
11118, Cust8, 1, 2015-09-28, 20
11118, Cust8, 2, 2015-09-08, 0
Here is my solution. Notice how I setup the temporary table, and then inserted the data. This is what you should do when asking questions like this so all we have to do is cut the T-SQL code from your post and paste it in SSMS and run it. Be sure you test your code in an empty database to be sure it runs clean prior to posting here.
Also, one of your expected values appears to be incorrect.
declare @Cust table(
CustomerID int,
CustomerName varchar(16),
AuditNum int,
AuditDate date,
ExpectedDateDifference int
);
insert into @Cust(
CustomerID,
CustomerName,
AuditNum,
AuditDate,
ExpectedDateDifference
)
values
(11111,'Cust1',1,'2015-10-01',0),
(11112,'Cust2',1,'2015-10-02',0),
(11113,'Cust3',1,'2015-10-01',14),
(11113,'Cust3',2,'2015-09-15',16),
(11113,'Cust3',3,'2015-08-30',0),
(11114,'Cust4',1,'2015-10-10',0),
(11115,'Cust5',1,'2015-09-30',20),
(11115,'Cust5',2,'2015-09-10',9),
(11115,'Cust5',3,'2015-09-01',17),
(11115,'Cust5',4,'2015-08-15',0),
(11116,'Cust6',1,'2015-11-01',0),
(11117,'Cust7',1,'2015-10-31',0),
(11118,'Cust8',1,'2015-09-28',20),
(11118,'Cust8',2,'2015-09-08',0);
--select * from @Cust;
select
bd1.CustomerID,
bd1.CustomerName,
bd1.AuditNum,
bd1.AuditDate,
bd1.ExpectedDateDifference,
isnull(datediff(day,bd2.AuditDate,bd1.Auditdate),0) DateDifference
from
@Cust bd1
left outer join @Cust bd2
on (bd1.CustomerID = bd2.CustomerID
and bd1.AuditNum = bd2.AuditNum -1)