Solved

Maximum Count Of Consecutive Dates

Posted on 2008-06-09
4
554 Views
Last Modified: 2010-04-21
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
Comment
Question by:paulcervinmitch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
rstomar earned 500 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

by:paulcervinmitch
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

by:paulcervinmitch
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

by:rstomar
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question