I need help writing a query based off comparing dates in multiple rows of a table.

  • I apologize in advance if this is not the correct forum. I am trying to write a query that will return a start date more than 30 past the last end date field in my table. Basically I have a subscription table that has many account numbers with unique subscription IDs that have both a start date and end date associated with each row. For each account ID (PhoneID) I need to count the number of new starts (unique subscription IDs) based on the 30 day criteria mentioned above. Here is some sample data from my table -

    PhoneID SubscriptionID StartDate EndDate

    1001000 01/01/201002/01/2010

    1001040 03/01/201008/01/2010

    2001500 01/01/201002/01/2010

    2001540 02/02/201010/01/2010

    So you see, I would need to count subscription ID 1040 as one new start but none of the others because there was not a lapse of 30 days. Can someone please help me figure out how to do this? Thank you.

  • Search this site for islands. There are several great articles and tons of forum posts on the topic.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • please describe date format mentioned above?

    DD/MM/YYYY or MM/DD/YYYY

  • I use the CONVERT(VarChar(10), StartDate, 101) function to format the dates in MM/DD/YYYY format.

  • The next script should do the trick:

    SELECT COUNT(DISTINCT SubscriptionID) AS NewStarts

    FROM Table1

    WHERE DATEDIFF(dd, StartDate, EndDate) > 30

    If you don't have repeated values for the [SubscriptionID] field, you can remove the [DISTINCT] clause.

    I hope it would be useful for you.

  • Won't this query just compare on a single line basis? What I mean is, I need to compare the end date of each account number with the start date of the next independent subscription id end date. It is a type of recursive query. Thank you for your input though 🙂

  • Oh man! My apologies for my misunderstanding. And you're right, this require some kind of recursion, wich could be implemented with a subquery.

    SELECT t1.PhoneID, COUNT(t1.SubscriptionID) AS NewStarts

    FROM Table1 t1

    WHERE DATEDIFF(dd, t1.StartDate, (SELECT MAX(t2.EndDate) FROM Table1 t2 WHERE t1.PhoneID = t2.PhoneID) ) > 30

    GROUP BY t1.PhoneID

    Greetings!

  • Andy, just curious. Wouldn't you want to get any start dates that are 30 days or less than the current date (getdate()) rather than the latest date in the table? Or even the start dates between a set of dates so that you know the new starts across a selected period of time?

    DG

Viewing 8 posts - 1 through 7 (of 7 total)

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