given this dataset:

CustomerNum        VisitNum        DateofVisit       
1            1        12-Jan-2005
1            2        23-Jan-2005       
1            3        28-Jan-2005       
2            1        01-Feb-2005
2            2        08-Feb-2005       
3            1        03-Feb-2005
3            2        08-Feb-2005   

how do i calculate the # of days elapsed between each visit, to produce a
resultset like this:

CustomerNum        VisitNum        DateofVisit        NumofDaysSinceLastVisit
1            1        12-Jan-2005
1            2        23-Jan-2005        11
1            3        28-Jan-2005        5
2            1        01-Feb-2005
2            2        08-Feb-2005        7
3            1        03-Feb-2005
3            2        08-Feb-2005        5

Try:

select
   t1.CustomerNum,
   t1.VisitNum,
   t1.DateofVisit,
   datediff(day, t2.DateofVisit, t1.DateofVisit) as NumofDaysSinceLastVisit
from
   dbo.t1 as t1
   left join
   dbo.t1 as t2
   on t1.CustomerNum = t2.CustomerNum
   and t2.VisitNum = t1.VisitNum - 1
go


AMB

Show quoteHide quote

Here's one way of achieving this:

CREATE TABLE dbo.CustomerVisits
(
CustID  int,
VisitNum int,
VisitTime datetime,
CONSTRAINT PK_CustomerVisits PRIMARY KEY (CustID, VisitNum)
)
GO

SET NOCOUNT ON
GO

INSERT INTO dbo.CustomerVisits (CustID, VisitNum, VisitTime) VALUES (1, 1,
'20050112')
INSERT INTO dbo.CustomerVisits (CustID, VisitNum, VisitTime) VALUES (1, 2,
'20050123')
INSERT INTO dbo.CustomerVisits (CustID, VisitNum, VisitTime) VALUES (1, 3,
'20050128')
INSERT INTO dbo.CustomerVisits (CustID, VisitNum, VisitTime) VALUES (2, 1,
'20050201')
INSERT INTO dbo.CustomerVisits (CustID, VisitNum, VisitTime) VALUES (2, 2,
'20050208')
INSERT INTO dbo.CustomerVisits (CustID, VisitNum, VisitTime) VALUES (3, 1,
'20050203')
INSERT INTO dbo.CustomerVisits (CustID, VisitNum, VisitTime) VALUES (3, 2,
'20050208')
GO

SELECT a.CustID,
a.VisitNum,
a.VisitTime,
DATEDIFF(dd, (SELECT MAX(VisitTime) FROM dbo.CustomerVisits AS b WHERE
a.CustID = b.CustID AND b.VisitTime < a.VisitTime), a.VisitTime)
FROM dbo.CustomerVisits AS a


Show quoteHide quote

Adding a space in a field
Can't use Shared Memory protocol to connect
merge two date ranges with overlap from two tables
dynamic sql in UDF
Timeout sql+asp