If you're actually on SQL 2012, you should be able to get better performance by using the LEAD windowed function rather than a self join.

Both versions produce the same results but note the difference in the execution plans...

IF OBJECT_ID('tempdb..#Cust', 'U') IS NOT NULL

DROP TABLE #Cust;

CREATE TABLE #Cust (

CustomerID INT,

CustomerName varchar(16),

AuditNum int,

AuditDate date,

ExpectedDateDifference INT,

PRIMARY KEY CLUSTERED (CustomerID, AuditNum)

);

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;

-- Self join approach --

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)

;

-- LEAD function approach --

select

bd1.CustomerID,

bd1.CustomerName,

bd1.AuditNum,

bd1.AuditDate,

bd1.ExpectedDateDifference,

isnull(datediff(day,LEAD(bd1.AuditDate, 1) OVER (PARTITION BY bd1.CustomerID ORDER BY bd1.AuditNum) ,bd1.Auditdate),0) DateDifference

from

#Cust bd1

;