Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Maximum Count Of Consecutive Dates

Posted on 2008-06-09
4
Medium Priority
?
558 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 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

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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

927 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