May 23, 2018 at 12:57 am
Hi I am trying to work out a script to return the average number of days between the most recent date and the second most recent date for each client id. I have come up with the below I think it's pretty close but not sure about the where row number.
SELECT cust_id, AVG(DATEDiff(days,Max(visit_date),tmp.visit_date))
FROM
Customer_table c
JOIN
( SELECT cust_id,visit_date
, ROW_NUMBER() OVER(ORDER BY Visit_Date DESC) AS RowNumber
FROM customer_table
) AS tmp
ON tmp.cust_id = c.cust_id
WHERE RowNumber = 2
Group By cust_id
May 23, 2018 at 2:29 am
Have you run it to test it as looks ok to me apart from the fact that cust_id will be ambiguous in the first select and the group by. Oh, and so will visit_date, prefix them and you should be good to go, unless you have more than one identical row at position 2.
May 23, 2018 at 2:57 am
ringovski - Wednesday, May 23, 2018 12:57 AMHi I am trying to work out a script to return the average number of days between the most recent date and the second most recent date for each client id. I have come up with the below I think it's pretty close but not sure about the where row number.
SELECT cust_id, AVG(DATEDiff(days,Max(visit_date),tmp.visit_date))
FROM
Customer_table c
JOIN
( SELECT cust_id,visit_date
, ROW_NUMBER() OVER(ORDER BY Visit_Date DESC) AS RowNumber
FROM customer_table
) AS tmp
ON tmp.cust_id = c.cust_id
WHERE RowNumber = 2
Group By cust_id
Is this what your looking for?
create table customers
(
custid int,
visit_date date
);
insert into customers values(1,'01-13-2017');
insert into customers values (1,'02-25-2017');
select
custid,
avg(datediff(day,visit_date,mx))
from
(select
custid,
visit_date,
max(visit_date) over (partition by custid order by visit_date desc) as mx,
row_number ()over(partition by custid order by visit_date desc) as scmx
from customers)avg_visit
where scmx=2
group by custid
Saravanan
May 23, 2018 at 6:45 am
I don't have access to the tables as it was a test this morning and I wanted to work it out. You have put the max inside the inner query is the only difference I see.
Thanks for the reply.
May 23, 2018 at 10:11 am
I don't think "AVG" really has any meaning for the diff between only 2 dates?!
SELECT cust_id, DATEDiff(day,Min(visit_date),Max(visit_date))
FROM (
SELECT cust_id, visit_date
, ROW_NUMBER() OVER(ORDER BY Visit_Date DESC) AS RowNumber
FROM customer_table
) AS tmp
WHERE RowNumber <= 2
Group By cust_id
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 23, 2018 at 11:18 am
ScottPletcher - Wednesday, May 23, 2018 10:11 AMI don't think "AVG" really has any meaning for the diff between only 2 dates?!
SELECT cust_id, DATEDiff(day,Min(visit_date),Max(visit_date))
FROM (
SELECT cust_id, visit_date
, ROW_NUMBER() OVER(ORDER BY Visit_Date DESC) AS RowNumber
FROM customer_table
) AS tmp
WHERE RowNumber <= 2
Group By cust_id
Nice answer. I also thought avg doesn't mean anything here. Since OP want avg and didn't post sample data to test. I included avg in my query
Saravanan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply