We are a group of users of Blackbaud products and are not affiliated with Blackbaud. We'd love to have you join our community to help and be helped in getting the most from your Blackbaud software.
Register now to join us to get independant advice on your system, connect with 3rd party consultants to help you maximize your database and have a real alternative to the official Blackbaud website.
I'm not sure if this should be in here, or where... maybe we need a 'how do i do this report?' forum.
Do any of you have any ideas on how to report on a member's consecutive years in his current category? I know how to pull consecutive years as a member, and how to export a membership history; I'm just kind of stumped on how to report on someone's years at a certain level...
I exported, say, 15 years of membership history to Access, and started to query on it, with things like this:
history-cat1 = category AND type <> dropped
AND history-cat2 = category AND type <> dropped
AND history-cat3=category AND type <> dropped
AND history-cat4 <> category
and so on. barring a lot of midcycle upgrading and downgrading, that should give a reasonable number, right? I guess I could do something similar, but make the type for each 'renewal'?
Basically they want to see of the people in this category, how many have been there for 1-5 years, how many 5-7, etc.
It can be done, but it gets complicated. I know this can be done in SQL. I'm guessing that it could also be done in MS Access.
You need to start with a table that contains the constituent ID, the (fiscal) year, and the membership level for that year. If you have multiple membership categories, you would also need to include the membership category. Then you need to join this table to itself to find the beginnings of runs.
Code:
SELECT a.ConsID, a.Level, a.[Year]
FROM Members AS a
LEFT OUTER JOIN Members AS b
ON a.ConsID = b.ConsID
AND a.Level = b.Level
AND a.[Year] = b.[Year] + 1
WHERE b.[Year] Is Null
You'll also need a similar code for the ends of runs. You'll probably want to save the results of both of these queries to temporary tables, because that makes it easier to work with the results. Once you have these tables, then you can either do a sub-query or a join to get the final results. I'll show you the join, because that's slight easier to understand.
Code:
SELECT e.ConsID, Minimum(e.[Year] - b.[Year]), e.Level
FROM RunBegin AS b
INNER JOIN RunEnd AS e
ON b.ConsID = e.ConsID
AND b.Level = e.Level
AND b.[Year] <= e.[Year]
WHERE e.[Year] = 2007
GROUP BY e.ConsID
This algorithm basically requires two unusual join conditions. The first is used in finding the beginnings/ends of runs. Instead of joining one field to another, you are joining a field to an expression "a.[Year] = b.[Year] + 1". In the other case, you are not using an equal operator, but a less than or equal operator to join the two tables.
Drew
__________________ J. Drew Allen
Children's Hospital of Philadelphia
Crystal Reports and SQL Server Consultant
It is better to live your destiny imperfectly than to live an imitation of somebody else's life with perfection.
Linear Mode
