• 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)