SQL query, getting number of days from first record

How would you write a query to get Customer Id, VisitDate, NumberOfDaysFromFirstVisit from the tables below?

Customer
Id      Name
101      Name1
102      Name2
103      Name3
…      …



CustomerVisit
Id      VisitDate      VisitedCity
101      1/1/2017      City1
101      1/5/2016      City2
101      1/3/2016      City3
102      1/1/2016      City1
102      1/5/2016      City2
102      1/3/2016      City1
102      1/1/2016      City2
103      1/1/2017      City1
104      1/1/2017      City2
105      1/1/2017      City4
...     ...                     ...



Thank you in advance.
quasar_eeAsked:
Who is Participating?
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
Is it what you are looking for?

DECLARE @CustomerVisit TABLE (
	CustomerID	INT ,
	VisitDate	DATE,
	VisitedCity	VARCHAR(20)
)

INSERT INTO @CustomerVisit ( CustomerID, VisitDate, VisitedCity )
VALUES 
(101 , '1/1/2017', 'City1'),
(101 , '1/5/2016', 'City2'),
(101 , '1/3/2016', 'City3'),
(102 , '1/1/2016', 'City1'),
(102 , '1/5/2016', 'City2'),
(102 , '1/3/2016', 'City1'),
(102 , '1/1/2016', 'City2'),
(103 , '1/1/2017', 'City1'),
(104 , '1/1/2017', 'City2'),
(105 , '1/1/2017', 'City4')

; WITH ctetest
AS (
	SELECT CustomerID, VisitDate, VisitedCity, RANK() OVER(PARTITION BY CustomerID, VisitedCity ORDER BY VisitDate) AS R
	FROM @CustomerVisit
)


SELECT V1.CustomerID, V1.VisitedCity, V1.VisitDate, V2.VisitDate, DATEDIFF(DAY, V1.VisitDate, V2.VisitDate)
FROM ctetest AS V1
INNER JOIN ctetest AS V2
ON V2.CustomerID = V1.CustomerID
AND V2.VisitedCity = V1.VisitedCity
AND V2.R = 1

Open in new window

0
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
took a slightly different approach, used an aggregate windowed function (ie the OVER clause).

First assumption is Customers must exist in Customer table - yu have a couple of Visits not matching your Customer

Second Assumption is "First Visit" refers to the same City
;with CTE_Customers as
(
  select C.id,C.name,V.visitDate
         ,min(V.VisitDate) over (Partition by V.ID,V.VisitedCity Order by V.Visitdate asc) as MinDate
  from  Customer C
  inner join CustomerVisit V on C.id = V.id
) Select ID as CustomerID, VisitDate, datediff(d,MinDate,VisitDate) as NumberOfDaysFromFirstVisit
  from CTE_Customers

Open in new window

Third assumption is "First Visit" being the first city visited by that Customer
;with CTE_Customers as
(
  select C.id,C.name,V.visitDate
         ,min(V.VisitDate) over (Partition by V.ID Order by V.Visitdate asc) as MinDate
  from  Customer C
  inner join CustomerVisit V on C.id = V.id
) Select ID as CustomerID, VisitDate, datediff(d,MinDate,VisitDate) as NumberOfDaysFromFirstVisit
  from CTE_Customers

Open in new window


Now, you dont mention the Version of SQL Server and need to provide a bit more description and/or sample result of what you expect.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.