Counting Consecutive Months in Access

[alsamren]

I am trying to write a query to count consecutive months in access. For example, In the following list, I would like to have a value returned as "4" for the number of consecutive months that john doe has appeared on the list.

john doe 10/1/2008
john doe 9/1/2008
john doe 8/1/2008
john doe 7/1/2008
john doe 5/1/2008
john doe 4/1/2008
john doe 3/1/2008

I do not write SQL.

Thanks

 

[raskew]

Hi -

Are you looking for the most recent consecutive months or the greatest in count (e.g. if your earlier example included 2/1/2008 and 1/1/2008, it would return a count of 5)?

Bob

 

[alsamren]

I am looking for the most recent consecutive months.

Thanks!

 

[alsamren]

Anyone??? Please Help!

 

[ByteMyzer]

Hello, alsamren,

You did not specify the following items:
* the table name
* the field names
* whether the Date field is always the beginning of the month.


However, let's examine a scenario like the following:

Table: MyTable
------------------------------------------
fName | Name
fDate | Date at the beginning of the month

The following query should give you what you are requesting (substitute the highlighted table and field names as appropriate):

SELECT T1.[b][i]fName[/i][/b], COUNT(T1.[b][i]fDate[/i][/b]) AS CountOffDate
FROM [b][i]MyTable[/i][/b] T1
WHERE T1.[b][i]fDate[/i][/b]>=
 (SELECT MAX(T2.[b][i]fDate[/i][/b])
  FROM [b][i]MyTable[/i][/b] T2
  WHERE T2.[b][i]fName[/i][/b]=T1.[b][i]fName[/i][/b]
  AND T2.[b][i]fDate[/i][/b]-31>
   (SELECT MAX(T3.[b][i]fDate[/i][/b])
    FROM [b][i]MyTable[/i][/b] T3
    WHERE T3.[b][i]fName[/i][/b]=T1.[b][i]fName[/i][/b]
    AND T3.[b][i]fDate[/i][/b]<T2.[b][i]fDate[/i][/b]
   )
 )
GROUP BY T1.[b][i]fName[/i][/b];

 

[WayPay]

For a few moments I thought it could be done in one statement. However, I couldn't do it (not saying it can't be done ).

See Module1 in attached example. Sorry about all the generic naming, but I have no idea of your context .

I hope you don't actually have a table with that data in it, BTW. Looks like a spreadsheet .

UPDATE: ByteMizer's solution is better. Use that .

 

[alsamren]

Thanks SO much. I just tried your code ByteMyzer and it worked perfectly!

 

[ByteMyzer]

You're welcome, I'm glad I could help

 

[raskew]

ByteMyzer -

This is indeed a hairy problem! Have searched this, and several other, forums on 'consecutive months'. They are numerous posts, but none that seem to have got it right.

Sounds like you've finally got it. It would seem that if you can identify both the beginning and end month of a consecutive series, it would be a simple matter to arrive at a count.

Could you/would you translate your code to Northwind's Orders table, where fName is EmployeeID and fDate = OrderDate.

Have played with this for several hours and have yet to get it right.

Appreciate you!

Best wishes - Bob

 

[ByteMyzer]

Certainly. Here you go:

SELECT O1.EmployeeID, COUNT(O1.OrderDate) AS CountOfOrderDate
FROM (SELECT DISTINCT O.EmployeeID,
      O.OrderDate-Day(O.OrderDate)+1 AS OrderDate
      FROM Orders O) O1
WHERE O1.OrderDate>=
 (SELECT MAX(O2.OrderDate)
  FROM (SELECT DISTINCT O.EmployeeID,
        O.OrderDate-Day(O.OrderDate)+1 AS OrderDate
        FROM Orders O) O2
  WHERE O2.EmployeeID=O1.EmployeeID
  AND O2.OrderDate-31>
   (SELECT MAX(O3.OrderDate-Day(O3.OrderDate)+1)
    FROM Orders O3
    WHERE O3.EmployeeID=O1.EmployeeID
    AND O3.OrderDate-Day(O3.OrderDate)+1<O2.OrderDate
   )
 )
GROUP BY O1.EmployeeID;

 

[raskew]

Thanks so much -

I'm getting an 'Syntax Error in FROM Clause' but I'll sort that out. If it works for you, should get it to work for me.

Thanks Again - Bob

 

[ByteMyzer]

It should work for A2000 and up. For A97 it would be:

SELECT O1.EmployeeID, COUNT(O1.OrderDate) AS CountOfOrderDate
FROM [SELECT DISTINCT O.EmployeeID,
      O.OrderDate-Day(O.OrderDate)+1 AS OrderDate
      FROM Orders O;]. O1
WHERE O1.OrderDate>=
 (SELECT MAX(O2.OrderDate)
  FROM [SELECT DISTINCT O.EmployeeID,
        O.OrderDate-Day(O.OrderDate)+1 AS OrderDate
        FROM Orders O;]. O2
  WHERE O2.EmployeeID=O1.EmployeeID
  AND O2.OrderDate-31>
   (SELECT MAX(O3.OrderDate-Day(O3.OrderDate)+1)
    FROM Orders O3
    WHERE O3.EmployeeID=O1.EmployeeID
    AND O3.OrderDate-Day(O3.OrderDate)+1<O2.OrderDate
   )
 )
GROUP BY O1.EmployeeID;

 

[WayPay]

Hate to be a naysayer, but after a second look at ByteMizers small & beautiful query I spotted some problems with the results.

fName CountOffDate
-------- ------------
Eau Noes 2
John Doe 4

Johns results are OK, but his sister Jane, your most valued customer, doesn't show up:

Jane Doe    8/1/2008
Jane Doe    7/1/2008
Jane Doe    6/1/2008
Jane Doe    5/1/2008
Jane Doe    4/1/2008
Jane Doe    3/1/2008
Jane Doe    2/1/2008
Jane Doe    1/1/2008

while Eau Noes, who has yet to come in 2 months in a row, gets a score of two:

Eau Noes    3/1/2008
Eau Noes    1/30/2008
Eau Noes    12/1/2007

Updated example includes ByteMizer's query and above 'edgy' data.

Oh yeah, these are my largish & complicated results:

John Doe: 4 months
Jane Doe: 8 months
Eau Noes: 1 months

Expect to come across the unexpected when dealing with dates .

 

[ByteMyzer]

WayPay, I think you overlooked one detail in my scenario:

Table: MyTable
------------------------------------------
fName | Name
fDate | Date at the [COLOR="Red"][b][u][i]beginning[/i][/u][/b][/COLOR] of the month

However, you're right on one other point; my query does not account for the Null value in my last nested subquery that can result if a person has nothing BUT consecutive months.

Here is the revised query, which will work even if the date does NOT fall on the beginning of the month:

SELECT T1.fName, COUNT(T1.fDate) AS CountOffDate
FROM (SELECT DISTINCT T.fName,
      T.fDate-Day(T.fDate)+1 AS fDate
      FROM MyTable T) T1
WHERE T1.fDate>=
 (SELECT MAX(T2.fDate)
  FROM (SELECT DISTINCT T.fName,
        T.fDate-Day(T.fDate)+1 AS fDate
        FROM MyTable T) T2
  WHERE T2.fName=T1.fName
  AND T2.fDate-31>
   Nz((SELECT MAX(T3.fDate-Day(T3.fDate)+1)
       FROM MyTable T3
       WHERE T3.fName=T1.fName
       AND T3.fDate-Day(T3.fDate)+1<T2.fDate
      ), 0)
 )
GROUP BY T1.fName;

 

[WayPay]

ByteMyzer, I don't see anything in the thread about those dates all being at the start of the month (although all sample dates are at the start of the month). Maybe alsamren would care to comment on this.

I'll check out your latest work of beauty tonight; I do want it to work .

 

[alsamren]

Hi ByteMyzer,

So I spoke too soon it seems. It works for many of the people in my list, but it doesn't capture everyone. I have 18,000+ records going back about 72 months. For some reason it's only capturing 574 people out of a total of 1052 people. I'm actually using a unique key instead of the name to count them. Do you know what i'm doing wrong? This is the code I used:

Table= tbl_all_los_revenue
Date = Month
Name = fckey

SELECT T1.FCKEY, Count(T1.MONTH) AS CountOffDate
FROM tbl_all_los_revenue AS T1
WHERE (((T1.MONTH)>=(SELECT MAX(T2.month)
FROM tbl_all_los_revenue T2
WHERE T2.fckey=T1.fckey
AND T2.month-31>
(SELECT MAX(T3.month)
FROM tbl_all_los_revenue T3
WHERE T3.fckey=T1.fckey
AND T3.month<T2.month
)
)))
GROUP BY T1.FCKEY;

The results are accurate...just not complete.

Thanks

 

[alsamren]

Oh....and yes, the dates are all for the first of the month.

 

[alsamren]

I just tried your revised code and it worked for everyone this time. Thanks again!!

 

[ByteMyzer]

ByteMyzer, I don't see anything in the thread about those dates all being at the start of the month (although all sample dates are at the start of the month).

WayPay, that's the point. In my post to alsamren I said:

You did not specify the following items:
* the table name
* the field names
* whether the Date field is always the beginning of the month.

I then went on to provide a query that addressed a hypothetical scenario, that would work IF the dates were all at the beginning of the month.

It seems that the latest query works for alsamren. See if it works for you.

 

[WayPay]

That works like a charm, y'all. I'm keeping that one for future use .

Sorry about the late reply, had a bit of a breakdown . Much better now .