how to calculate dates difference in a same table

  • 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

  • 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) "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".

  • 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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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

  • 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.

  • 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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply