September 4, 2015 at 3:16 pm
I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.
Each customer is uniquely identified by a customer#
I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.
How can i do that in a query?
Thanks,
Blyzzard
September 4, 2015 at 3:30 pm
SELECT first_appointment.*, next_appointment.*
FROM (
SELECT customer#, MIN(appoinment_date) AS appointment_date
FROM table_name
GROUP BY customer#
HAVING MIN(appointment_date) >= '20140101' AND MIN(appointment_date) < '20140901'
) AS first_appointment
CROSS APPLY (
SELECT TOP (1) next.*
FROM table_name next
WHERE next.customer# = first_appointment.customer# AND
next.appointment_date > DATEADD(DAY, DATEDIFF(DAY, 0, first_appointment.appointment_date) + 30, 0)
ORDER BY next.appointment
) AS next_appointment
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
September 4, 2015 at 8:52 pm
amar_kaur16 (9/4/2015)
I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.Each customer is uniquely identified by a customer#
I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.
How can i do that in a query?
Thanks,
Blyzzard
Since you are using SQL 2012, let's use some of the new features:
First put some test data into a table to run from. You really should have provided some test data for us in readily consumable format, like I did below. Please see the first link in my signature for how to do this.
DECLARE @Customers TABLE (
CustomerID INTEGER,
ApptDt DATE,
PRIMARY KEY (CustomerID, ApptDt)
);
INSERT INTO @Customers (CustomerID, ApptDt)
VALUES (1, '2014-05-01'),
(1, '2014-06-15'), -- 45 days between appts
(2, '2014-06-01'),
(2, '2014-06-28'),
(3, '2014-03-20'),
(3, '2014-10-25');
-- create some variables
DECLARE @StartDate DATE = '2014-01-01',
@EndDate DATE = '2014-08-31';
WITH cte AS
(
-- Get the customers and their first appointment within the specified date range.
SELECT CustomerID, MIN(ApptDt) AS FirstApptDt
FROM @Customers
WHERE ApptDt >= @StartDate AND ApptDt <= @EndDate
GROUP BY CustomerID
), cte2 AS
(
SELECT t2.CustomerID,
t2.FirstApptDt,
-- get the next appointment for this customer
LEAD(ApptDt, 1, NULL) OVER (PARTITION BY t1.CustomerID ORDER BY t1.ApptDt) AS NextApptDate,
-- get the row number for this row
ROW_NUMBER() OVER (PARTITION BY t1.CustomerID ORDER BY t1.ApptDt) AS RN
-- join the subquery to the table
FROM @Customers t1
JOIN cte t2
ON t1.CustomerID = t2.CustomerID
)
-- now add the number of days between the two dates,
-- and filter for just the first row and for appointments > 30 days.
SELECT cte2.CustomerID,
cte2.FirstApptDt,
cte2.NextApptDate,
ca.DaysBetweenAppts
FROM cte2
CROSS APPLY (VALUES (DATEDIFF(DAY, cte2.FirstApptDt, cte2.NextApptDate))) ca(DaysBetweenAppts)
WHERE RN = 1
AND ca.DaysBetweenAppts >= 30
Does this handle what you're looking for?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 8, 2015 at 1:15 pm
hello Wayne,
Looks like I am missing records because I am interested in getting first follow up date but I am not getting next follow up date right after the appointment.
for the cases where follow up appointment is less than 30 days, I should not get any data but I am getting data for the the next follow up date.
Thanks,
Blyzzard
September 9, 2015 at 7:13 am
Hello,
I think you should try to explain the requirements in more detail.
"I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment" - so the table with appdt column has several rows for the same customer (if there were several appointments), right? And the first appointment is simply the one with lowest date? Or is there a special column for "first appointment date" in the table? Can there be several parallel chains of appointments for the same customer (like with different people, to different projects etc), each with its own first appointment date?
"I need to find out if the customer came back after 200 days or more" - does it mean that the customer didn't come sooner than after 200 days from first appt, or that they (regardless of whether there were any appts in between) came also after more than 200 days from the first appt?
"for the cases where follow up appointment is less than 30 days, I should not get any data" - that means if the followup appt after the first was in less than 30 days, then this customer does not enter into the result at all, regardless of other appointments? Or do you check the 30 days between all appointments (not only between the first and second) and any case where the time between 2 consecutive appointments is over 30 days is interesting for you?
Please try to explain what you are doing and why... so far it seems to me that you are in the phase of looking for question, not for answer.
September 9, 2015 at 9:19 am
amar_kaur16 (9/8/2015)
hello Wayne,Looks like I am missing records because I am interested in getting first follow up date but I am not getting next follow up date right after the appointment.
for the cases where follow up appointment is less than 30 days, I should not get any data but I am getting data for the the next follow up date.
Thanks,
Blyzzard
Well, I've misplaced the crystal ball, and I can't read your mind.
How about reading the first link in my signature (For better assistance in answering your questions), and then posting:
1. Sample data that has the issues
2. Expected results based upon the sample data.
Until we are working from the same page, I just can't help you.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply