Solved

Maximum Count Of Consecutive Dates

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Optimization 14 44
transaction in asp.net, sql server 6 33
Query to capture 5 and 9 digit zip code? 4 21
Need help constructing a conditional update query 16 47
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

810 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