Counting Consecutive Months in Access (1 Viewer)

[CoolDude2000]

Hi,
When I am running this query below...access running longer without any retrieving results? I ran this query in SQL Server Management studio but getting error msg below:
Msg 206, Level 16, State 2, Line 1
Operand type clash: date is incompatible with int

Please help.

SELECT T1.fName, COUNT(T1.fDate) AS CountOffDate
FROM (SELECT DISTINCT T.fName,
T.fDate-Day(T.fDate)+1 AS fDate
FROM MyTable T) AS 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>
(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
)
)
GROUP BY T1.fName;

 

[smig]

This is a 7 years old thread

 

[CoolDude2000]

I know it is 7 yr old posting but the solution provided did not work. Can you help?

 

[ByteMyzer]

Hello, CoolDude2000,

For a SQL Server database, there needs to be a change to the SQL:

[COLOR="Blue"]SELECT[/COLOR] T1.fName, [COLOR="Magenta"]COUNT[/COLOR](T1.fDate) [COLOR="Blue"]AS[/COLOR] CountOfDate
[COLOR="Blue"]FROM[/COLOR] ([COLOR="blue"]SELECT DISTINCT[/COLOR] T.fName,
      [COLOR="Magenta"]DATEADD[/COLOR](m, [COLOR="Magenta"]DATEDIFF[/COLOR](m, 0, T.fDate), 0) [COLOR="Blue"]AS[/COLOR] fDate
      [COLOR="blue"]FROM[/COLOR] MyTable T) T1
[COLOR="blue"][COLOR="Blue"]WHERE[/COLOR][/COLOR] T1.fDate >=
 ([COLOR="blue"]SELECT[/COLOR] [COLOR="Magenta"]MAX[/COLOR](T2.fDate)
  [COLOR="blue"]FROM[/COLOR] ([COLOR="blue"][COLOR="blue"]SELECT[/COLOR] DISTINCT[/COLOR] T.fName,
        [COLOR="Magenta"]DATEADD[/COLOR](m, [COLOR="Magenta"]DATEDIFF[/COLOR](m, 0, T.fDate), 0) [COLOR="blue"]AS[/COLOR] fDate
        [COLOR="blue"]FROM[/COLOR] MyTable T) T2
  [COLOR="Blue"]WHERE[/COLOR] T2.fName = T1.fName
  [COLOR="Gray"]AND[/COLOR] T2.fDate-31 >
   [COLOR="Magenta"]ISNULL[/COLOR](([COLOR="blue"]SELECT[/COLOR] [COLOR="Magenta"]MAX[/COLOR]([COLOR="Magenta"]DATEADD[/COLOR](m, [COLOR="Magenta"]DATEDIFF[/COLOR](m, 0, T3.fDate), 0))
       [COLOR="blue"]FROM[/COLOR] MyTable T3
       [COLOR="Blue"]WHERE[/COLOR] T3.fName = T1.fName
       [COLOR="Gray"]AND[/COLOR] [COLOR="Magenta"]DATEADD[/COLOR](m, [COLOR="Magenta"]DATEDIFF[/COLOR](m, 0, T3.fDate), 0) < T2.fDate
      ), 0)
 )
[COLOR="Blue"]GROUP BY[/COLOR] T1.fName;

 

[CoolDude2000]

Thanks alot ByteMyzer. I will try tomorrow.

 

[CoolDude2000]

Thanks very much ByteMyzer. Worked perfectly.