December 28, 2011 at 9:09 am
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.
December 28, 2011 at 9:42 am
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/
December 29, 2011 at 6:24 am
please describe date format mentioned above?
DD/MM/YYYY or MM/DD/YYYY
December 29, 2011 at 8:18 am
I use the CONVERT(VarChar(10), StartDate, 101) function to format the dates in MM/DD/YYYY format.
December 29, 2011 at 9:40 am
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.
December 29, 2011 at 7:46 pm
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 🙂
December 29, 2011 at 9:06 pm
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!
December 30, 2011 at 5:15 am
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