Count by Month

[stephm923]

I need to count the number of members that have an open auth by month. However, I'm having an issue because I only have an auth begin date and an auth end date. The data looks like this:

Member A
Eff_date = 20071201
End_Date = 20080531

Member B
Eff_date = 20080101
End_date = 20080331

Member A had an open auth every month from 12/07 to 5/08 and Member B had an open auth every month from 1/08 to 3/08. I need it to look like this:
Month Auth Count
12/07 1
1/08 2
2/08 2
3/08 2
4/08 1
5/08 1

I really appreciate your help. I'm at a loss on this one.
Thanks!!

 

[tran008]

something like this?

select dateadd(month,datediff(month,0,datefield),0) as [month],
count(groupmember) as [Auth Count]
from table
group by groupmember, dateadd(month,datediff(month,0,datefield),0)
order by dateadd(month,datediff(month,0,datefield),0)

 

[dGrouse]

Hi. You need to clarify what it means for you to have an "open auth for a month". Does it mean that the "auth" (whatever that is) was "open" for any portion of the month? If, for instance, member A had a record that looked like this:

eff_date = '5/23/2008'
end_date = '11/4/2008'

would you want to include member A in your counts for May and November? Once you clarify how you're defining "open" for a given month, I can tell you how to do your counts by month pretty easily.

Jeff Prenevost
BI Consultant
Ann Arbor, MI

 

[stephm923]

I need to count Member A in May, June, July, Aug, Sep, Oct and Nov. The auth is considered open until the end_date which in this example is 11/4/08.

 

[dGrouse]

Thanks for the clarification.

The following assumes you have, or can make, a table PIVOT_HELPER which contains one column, i, with integers from 0 to, say, 1000. A table like that comes in real handy, as in this case.

I take it that you have a table I'll call TT_AUTH that's something like this:

TT_AUTH
member_code varchar(50)
eff_date datetime
end_date datetime

I'll also assume that you don't have any overlapping records in this table, i.e., that there is no date for which more than one record for the same member spans the date.

First, find the earliest eff_date and the latest end_date. If this is a one-off, ad hoc situation, you can just go find those dates and hard-code them, as I've done in this code sample; if this needs to be automated for production, then declare a couple of variables and use them in place of the hard-coded values. In this example, the earliest eff_date is assumed to be no earlier than 3/1/2008, and the latest end_date no later than 2/1/2009.

The following will give you your counts. The CASE statement is there just to make the handling of months in which you may have no open auth's nice and clean:

select
  m_from
  ,sum(case when a.member_code is null then 0 else 1 end) ct
from
  (
  select
    dateadd(m,p.i,'3/1/2008') m_from
    ,dateadd(m,p.i + 1,'3/1/2008') m_to
  from
    pivot_helper p
  where
    dateadd(m,p.i,'3/1/2008') <= '2/1/2009'
  ) m left outer join tt_auth a 
  on a.eff_date < m.m_to
    and a.end_date >= m.m_from
group by
  m.m_from
order by
  m.m_from

Jeff Prenevost
BI Consultant
Ann Arbor, MI