Solved

# Maximum Count Of Consecutive Dates

Posted on 2008-06-09
Medium Priority
559 Views
Dear Experts:

Hi! I am supposed to do an MS SQL stored procedure that would return all the maximum count of
consecutive dates. As an illustration, I have provided here 3 tables namely TABLE 1, TABLE 2 and OUTPUT TABLE. The OUTPUT table is the required output of the stored procedure.

TABLE 1            TABLE 2                  OUTPUT TABLE
ACCOUNT            ACCOUNT      PAY PERIOD            ACCOUNT      COUNT
1            1      Sep-07            1      3
2            1      Oct-07            2      4
3            1      Dec-07            3      0
1      Jan-08
1      Feb-08
1      Jun-08
2      Jan-08
2      Feb-08
3      Apr-08
3      May-08
3      Jun-08

From TABLE 2, the missing dates for ACCOUNT 1 are

Nov 07
March 08
April 08
May 08

The OUTPUT TABLE gave a COUNT of 3 for ACCOUNT 1 since THE MAXIMUM COUNT OF CONSECUTIVE DATE NOT PRESENT in TABLE 2 is 3, namely, March 08 to May 08. The ceiling of the date is the current date (June 2008) and the dates are only available in MONTH and YEAR.

From TABLE 2, the missing dates for ACCOUNT 2 are

Mar 08
Apr 08
May 08
Jun 08

Again for ACCOUNT 2, the OUTPUT TABLE gave a COUNT of 4, from Mar 08-Jun 08. Lastly for ACCOUNT 3, it has a count of 0 since it has consecutive payments from Apr 08-June 08.

I find it hard to make a stored procedure since the total number of records for TABLE 1 is 900,000 and TABLE 2 is around 30 Million. The fields for both tables are already indexed.

I hope you could help me in my query.

Thanks!

0
Question by:paulcervinmitch
• 2
• 2

LVL 5

Accepted Solution

rstomar earned 2000 total points
ID: 21749105
Select AccountNo, Max(Diff)
from (
select
A.AccountNo,
DATEDIFF(m,Convert(DATETIME,'01-' + A.dateCol,108),
ISNULL((Select MIN(Convert(DATETIME,'01-' + C.dateCol,108)) from Table2 C where C.AccountNo = A.AccountNo and Convert(DATETIME,'01-' + C.dateCol,108) > Convert(DATETIME,'01-' + A.dateCol,108)),CONVERT(DATETIME,DATEADD(d,-(DATEPART(d,DATEADD(m,1,GETDATE())) - 1),DATEADD(m,1,GetDate())),108)) ) - 1 as Diff
from Table2 A
where not exists ( Select 1 from Table2 B where B.AccountNo = A.AccountNo and Convert(DATETIME,'01-' + B.dateCol,108) = DATEADD(m,1,Convert(DATETIME,'01-' + A.dateCol,108 )))
and Convert(DATETIME,'01-' + A.dateCol,108 ) < CONVERT(DATETIME,DATEADD(d,-(DATEPART(d,GETDATE()) - 1),GetDate()),108)
) D
group by AccountNo
0

LVL 2

Author Comment

ID: 21756414
rstomar!

what a very clever solution !

THE stored procedure yielded the correct output but I have not applied it to the actual tables. I have no idea how long will it take. I was pondering on the logic of the stored procedure but I find it hard to comprehend. Can you explain it to me by parts?

Thanks a lot genius!

0

LVL 2

Author Closing Comment

ID: 31465637
I was amazed at your solution.. Unfortunately, I find it hard to comprehend your solution. I hope you would add some explanations in it.

Thanks for the help..
0

LVL 5

Expert Comment

ID: 21757178
The following query is the basis of the above query. In this query I get all the rows from Table2 where there is a break( that means there is no row for the next month) :

select
A.AccountNo,
Convert(DATETIME,'01-' + A.dateCol,108) StartDate,
ISNULL((Select MIN(Convert(DATETIME,'01-' + C.dateCol,108)) from Table2 C where C.AccountNo = A.AccountNo and Convert(DATETIME,'01-' + C.dateCol,108) > Convert(DATETIME,'01-' + A.dateCol,108)),CONVERT(DATETIME,DATEADD(d,-(DATEPART(d,DATEADD(m,1,GETDATE())) - 1),DATEADD(m,1,GetDate())),108)) EndDate
from Table2 A
where not exists ( Select 1 from Table2 B where B.AccountNo = A.AccountNo and Convert(DATETIME,'01-' + B.dateCol,108) = DATEADD(m,1,Convert(DATETIME,'01-' + A.dateCol,108 )))
and Convert(DATETIME,'01-' + A.dateCol,108 ) < CONVERT(DATETIME,DATEADD(d,-(DATEPART(d,GETDATE()) - 1),GetDate()),108)

Now I will break down this query and try to explain :
-- converting mon-yyyy string to date as mm/01/yyyy to make it easy to use date functions thru out the query

select
A.AccountNo,
Convert(DATETIME,'01-' + A.dateCol,108) StartDate,    -- this gets the date mm/dd/yyyy from the string mon-yyyy so that it is easy to use date functions on the dates rather than strings
ISNULL((Select MIN(Convert(DATETIME,'01-' + C.dateCol,108)) from Table2 C where C.AccountNo = A.AccountNo and Convert(DATETIME,'01-' + C.dateCol,108) > Convert(DATETIME,'01-' + A.dateCol,108)),CONVERT(DATETIME,DATEADD(d,-(DATEPART(d,DATEADD(m,1,GETDATE())) - 1),DATEADD(m,1,GetDate())),108)) EndDate    -- this gets the next date after the break for the same account so that we can subtract the start date from this to get the difference in months
from Table2 A
where not exists ( Select 1 from Table2 B where B.AccountNo = A.AccountNo and Convert(DATETIME,'01-' + B.dateCol,108) = DATEADD(m,1,Convert(DATETIME,'01-' + A.dateCol,108 )))
-- not exists conditions checks for rows only where the next month is missing, so the query will return only those rows where there is a break
and Convert(DATETIME,'01-' + A.dateCol,108 ) < CONVERT(DATETIME,DATEADD(d,-(DATEPART(d,GETDATE()) - 1),GetDate()),108)
-- also filters the row for the current month bcos the records won't exist after the current month

Hope the explanation will benefit you.
Feel free to ask me any specific question (if not clear) about the queries.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recovâ€¦
###### Suggested Courses
Course of the Month3 days, 4 hours left to enroll