count consecutive months 1

[hellohello1]

I have a query with 3 fields:
ProjectID (Number)
AsOfDate (Date)
OverallRating(Number)

The AsOfDate is always the last day of the month (ex: 2/28/09, 1/31/09, 12/31/08, etc).

OverallRating is a number from 1 to 7.

I am trying to have a column called ConsecutiveMonths1 that, for the latest month, shows how many consecutive months each project has an OverallRating of 1.

For example:
ProjectID...AsOfDate....OverallRating
2345.........9/30/08.......2
2345.........10/31/08......1
2345.........11/30/08......2
2345.........12/31/08......1
2345.........1/31/08.......1
2345.........2/28/09.......1

Since we are currently in March, the latest month is 2/28/09. So I would want my results to show:

ProjectID...AsOfDate....OverallRating....ConsecutiveMonths1
2345.........2/28/09......1......................3

The ConsecutiveMonths1=3 because the OverallRating was 1 for 3 consecutive months counting backwards from 2/28/09.

Any idea how I could do this?

Thanks,

 

[MajP]

I have no idea how to do this in SQL so I wrote a function. It is not very efficient but works.
1) For each record return all records prior to it that have the desired rating.
2) Loop through these records until you find a more than 31 day gap from the previous record

Public Function getConseqRating(projID As Long, asOf As Date, rating As Integer) As Integer
  Const qryName = "tblRating"
  Dim strSql As String
  Dim countMonths As Integer
  Dim currentRating As Integer
  Dim currentDate As Date
  Dim rs As DAO.Recordset
  strSql = "Select asOfDate from " & qryName & " where projectID = " & projID & " AND overAllRating = " & rating & " AND asOfDate <= #" & asOf & "# ORDER BY asOfDate DESC"
  'Debug.Print strSql
  Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  If rs.EOF And rs.EOF Then Exit Function
  If rs!asOfDate = asOf Then
     countMonths = 1
     currentDate = asOf
     If Not rs.EOF Then rs.MoveNext
     Do While Not rs.EOF
       Debug.Print rs!asOfDate
       Debug.Print currentDate - rs!asOfDate
       If currentDate - rs!asOfDate < 32 Then
         countMonths = countMonths + 1
         currentDate = rs!asOfDate
       End If
       rs.MoveNext
      Loop
  End If
  getConseqRating = countMonths
End Function

sql

SELECT tblRating.projectID, tblRating.asOfDate, tblRating.overAllRating, getConseqRating([projectID],[asOfDate],1) AS conseq1, getConseqRating([projectID],[asOfDate],2) AS conseq2
FROM tblRating
ORDER BY tblRating.asOfDate;

output

projectID asOfDate overAllRating conseq1 conseq2
2345	9/30/2008	2	0	1
2345	10/31/2008	1	1	0
2345	11/30/2008	2	0	1
2345	12/31/2008	1	1	0
2345	1/30/2009	1	2	0
2345	2/28/2009	1	3	0
2345	3/30/2009	1	4	0
2345	4/30/2009	2	0	1

I designed the code so you can ask for consequtive of any value. The second to last columns is consequtive 1s and the last is consequtive 2s

 

[hellohello1]

this is perfect!!! thank you so much. You are very smart.

it works great.

thankyouthankyouthankyouthankyouthankyouthankyou!!!!!!!!!!!!!!!!!!

 

[hellohello1]

Is there a way to mix them up? Now we want to treat 6s (sixes) the same as 1s (ones).

Ex:
If the table showed 4 months of records: ex: 2, 6, 6, 6. the Conseq1 would be 3.

2, 6, 6, 6: the Conseq1 would be 3.
2, 1, 1, 1: the Conseq1 would be 3.
2, 6, 1, 6: the Conseq1 would be 3.
2, 1, 6, 1: the Conseq1 would be 3.
2, 1, 6, 6: the Conseq1 would be 3.
etc.

Thanks,

 

[MajP]

you could change the function, but one way would be to sum the function twice.

...tblRating.overAllRating, getConseqRating([projectID],[asOfDate],1) + getConseqRating([projectID],[asOfDate],6) as Conseq1, from tblRating...

 

[MajP]

I tried the above and it does not work so I had to change the function

I used a parameter array so you can add as many numbers as you want to represent a one.

the trick is to make a select statement look something like

Select asOfDate from tblRating
where projectID = 2345
AND (overAllRating = 1 OR overAllRating = 6)
AND asOfDate <= #4/30/2009# ORDER BY asOfDate DESC

Public Function getConseqRating(projID As Long, asOf As Date, ParamArray rating() As Variant) As Integer
  Dim qryName As String
  Dim strSql As String
  Dim countMonths As Integer
  Dim currentRating As Integer
  Dim currentDate As Date
  Dim rs As DAO.Recordset
  Dim strOverallRating
  Dim counter As Integer
  qryName = "tblRating"
  
 For counter = 0 To UBound(rating())
    If counter = 0 Then
      strOverallRating = "(overAllRating = " & rating(0)
    Else
      strOverallRating = strOverallRating & " OR overAllRating = " & rating(counter)
    End If
    
  Next counter
    strOverallRating = strOverallRating & ")"
  strSql = "Select asOfDate from " & qryName & " where projectID = " & projID & " AND " & strOverallRating & " AND asOfDate <= #" & asOf & "# ORDER BY asOfDate DESC"
  Debug.Print strSql
  Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  If rs.BOF And rs.EOF Then
    Exit Function
  Else
    rs.MoveFirst
  End If
  If rs!asOfDate = asOf Then
     countMonths = 1
     currentDate = asOf
     If Not rs.EOF Then rs.MoveNext
     Do While Not rs.EOF
      If currentDate - rs!asOfDate < 32 Then
         countMonths = countMonths + 1
         currentDate = rs!asOfDate
       End If
       rs.MoveNext
      Loop
  End If
  getConseqRating = countMonths
End Function

Sql

SELECT tblRating.projectID, tblRating.asOfDate, tblRating.overAllRating, 
getConseqRating([projectID],[asOfDate],1,6) AS conseq1
FROM tblRating
ORDER BY tblRating.asOfDate;

output

projectID	asOfDate	overAllRating	conseq1
2345	9/30/2008	6	1
2345	10/31/2008	1	2
2345	11/30/2008	2	0
2345	12/31/2008	1	1
2345	1/30/2009	6	2
2345	2/28/2009	1	3
2345	3/30/2009	1	4
2345	4/30/2009	2	0

This gives the correct answer. I am sure there is a way easier approach but, I am not seeing it.

 

[MajP]

I have no idea what it means to treat 6s as 1s, but this would allow you to treat any amount of numbers equal to the desired number. With a parameter array you can just keep seperating with a comma
ex.
getConseqRating([projectID],[asOfDate],1,2,4,6)

 

[hellohello1]

You are absolutly amazing!! it works perfectly. You are very smart. Wow, arrays are pretty advanced.

I'm so impressed!!

I can't thank you enough!

 

[MajP]

Thanks, but if I was smart I could figure out to do this in a sql query. That would be more efficient. I think this can be done, but requires nested queries. I am glad this works, but not a very eloquent solution.