Solved

Maximum Count Of Consecutive Dates

Posted on 2008-06-09
4
549 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
  • 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now