>>What is a good sql method of getting member months at a hmo.?
>>I use vfp5.0 ;have member tables with effective and expiration dates.
>>Is medicaid so there are some members who go in and out maybe as much
>>as 3 times a year.
>>
>>Calculating the RPM's of a revolving door in St. Louis........
>
>I assume your table has these fields: person_id, effdate, expdate and also that there may be more than one of each person_id (maybe three per year of data) in this table.
>
>

>SELECT person_id, SUM(monthdiff(effdate,expdate)) AS months ;
> FROM yourtable INTO CURSOR whatever WHERE NOT EMPTY(effdate) ;
> AND NOT EMPTY(expdate) GROUP BY person_id
>
>FUNCTION monthdiff
>PARAMETERS dStartDate, dStopDate
>LOCAL nStartYear, nStopYear, nStartMonth, nStopMonth
>nStartYear = YEAR(dStartDate)
>nStopYear = YEAR(dStopDate)
>nStartMonth = MONTH(dStartDate)
>nStopMonth = MONTH(dStopDate)
>nMonths = (12*(nStopYear-nStartYear))+(nStopMonth-nStartMonth)
>RETURN nMonths
>

>If you wish to round off months differently, you can tweak the monthdiff function, using other native VFP date functions.


>>>thank you for the reply; I'll get back with you with results after I give it
a go.