November 24, 2015 at 9:12 am
I have a somewhat unique situation and need to figure out a way to get datediffs from multiple rows of data. I already have a row number value per customer,
the problem is not every customer has multiple dates, but some can have up to 6 dates. So I need to be dynamic enough to only calculate datediffs where
customers have multiple records. Here's a sample of my dataset:
CustomerID, CustomerName, AuditNum, AuditDate
11111, Cust1, 1, 2015-10-01
11112, Cust2, 1, 2015-10-02
11113, Cust3, 1, 2015-10-01
11113, Cust3, 2, 2015-09-15
11113, Cust3, 3, 2015-08-30
11114, Cust4, 1, 2015-10-10
11115, Cust5, 1, 2015-09-30
11115, Cust5, 2, 2015-09-10
11115, Cust5, 3, 2015-09-01
11115, Cust5, 4, 2015-08-15
11116, Cust6, 1, 2015-11-01
11117, Cust7, 1, 2015-10-31
11118, Cust8, 1, 2015-09-28
11118, Cust8, 2, 2015-09-08
I'm not sure what the best solution is. Any help is greatly appreciated.
November 24, 2015 at 9:15 am
woody_rd (11/24/2015)
I have a somewhat unique situation and need to figure out a way to get datediffs from multiple rows of data. I already have a row number value per customer,the problem is not every customer has multiple dates, but some can have up to 6 dates. So I need to be dynamic enough to only calculate datediffs where
customers have multiple records. Here's a sample of my dataset:
CustomerID, CustomerName, AuditNum, AuditDate
11111, Cust1, 1, 2015-10-01
11112, Cust2, 1, 2015-10-02
11113, Cust3, 1, 2015-10-01
11113, Cust3, 2, 2015-09-15
11113, Cust3, 3, 2015-08-30
11114, Cust4, 1, 2015-10-10
11115, Cust5, 1, 2015-09-30
11115, Cust5, 2, 2015-09-10
11115, Cust5, 3, 2015-09-01
11115, Cust5, 4, 2015-08-15
11116, Cust6, 1, 2015-11-01
11117, Cust7, 1, 2015-10-31
11118, Cust8, 1, 2015-09-28
11118, Cust8, 2, 2015-09-08
I'm not sure what the best solution is. Any help is greatly appreciated.
?
So what are you expecting as a final result?
November 24, 2015 at 9:27 am
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
November 24, 2015 at 5:45 pm
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)
November 24, 2015 at 7:38 pm
Lynn,
Understand about the protocol. Thanks for the clarification! I'll be sure to adhere to the process in the future if needed.
The sql you provided was exactly what I needed! Thanks so much!
November 24, 2015 at 8:43 pm
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
;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply