SQL needed to count number of Households per alphabet per campus that are members

[Sandy Mann]

Has anyone written SQL to count the number of Member Households per letter of the alphabet per campus?

I was able to get what I needed by running two FCR3101 reports (one for each campus) and using the Output wizard to get one record per household using the household name. The report filters I used are Status Equals Member and Campus/Location Equals ____ - running one for each of our 2 campus.

I then had to add a Campus field to the results, combine the files and use COUNTIFS to count the number of households that start with "A" and Campus is "First Colony, (created a CountIFS for each letter of the alphabet and each Campus.)

This is doable on a regular basis but it does take time to combine the reports and put them into the spreadsheet that does the calculations. it would really be great to have an SQL that would spit out the matrix quickly

This is the end result:

	First Colony	Foster Creek
A	25	2
B	69	7
C	67	2
D	39	2
E	19	1
F	30	0
G	39	5
H	70	2
I	0	0
J	30	0
K	25	1
L	24	1
M	66	3
N	21	1
O	20	0
P	40	6
Q	1	0
R's	37	3
S's	86	4
T's	36	1
U's	2	1
V's	6	0
W's	56	4
X's	0	0
Y's	1	0
Z's	3	0
Totals	812	46	858

[Robert Jones]

Out of curiosity. What do you use a report like that for?

[Robert Jones]

Cleaned up some code, can't edit old posts so deleted it to remove confusion.

First, we need to get the codes for the Statuses.

--use this to lookup the CodeIDs for the desired Status

select *

from tblCodes

where codetypeid = '5'

The query to get the list:

with CampusCte as (

select c.contactid, LEFT(c.lastname,1) as [Letter], co.Description

from tblContacts c

join tblCodes co on c.Service = co.CodeID

--change the number to the CodeID for the desired Status. 56 is default Elexio Member - 100 code.

where c.Status in ('56')

)

select cc_pivot.letter,

-- change inside the [] to the exact campus name as listed in elexio

[Main Campus], [2nd Campus], [<Unknown>]

from CampusCte cc

pivot (count(cc.contactid) for cc.description in (

--make these the same as the campus list above. copy and paste from above should work

[Main Campus], [2nd Campus], [<Unknown>]

)) as cc_pivot

Few things to note:

If you have more than 1 status code you consider a Member. (by default Elexio has Member - 100, Member - 200 and so on). That can also be expanded by adding more of the status numbers inside of the ( ) making sure to separate each number by a comma.

I would leave the <Unknown> tag in there, that is a builtin option for a campus in Elexio. When I ran this on my database I found that I had 2 people unknown, so I was able to go fix their records.

If there are more than 2 campuses, it can be expanded by adding more to the list. Make sure to put the name inside of brackets [ ] and separate each with a comma.

[Sandy Mann]

Hi Robert,
I finally got some time to test this.
This code works but it is counting # of Adult people that are members not # of households that have Adult members.

[Robert Jones]

not # of households that have Adult members.

To clarify, you have some households where kids are members but the head of household/spouse of head is not?

You want count of households where either Head of House or Spouse of Head is a member?

[Robert Jones]

This should fix it to counting households now instead of individuals. (Totally missed that part when reading the request initially.)

with CampusCte as (

select distinct a.addressid, LEFT(a.AddressName,1) as [Letter], co.Description

from tblContacts c

join tblAddresses a on c.AddressID = a.AddressID

join tblCodes co on c.Service = co.CodeID

where c.familyposition in ('91','92') and

--change the number to the CodeID for the Status. 56 is default Elexio Member code.

c.Status in ('56')

)

select cc_pivot.letter,

-- change inside the [] to the exact campus name as listed in elexio

[Main Campus], [2nd Campus], [<Unknown>]

from CampusCte cc

pivot (count(cc.addressid) for cc.description in (

--make these the same as the campus list above. copy and paste from above should work

[Main Campus], [2nd Campus], [<Unknown>]

)) as cc_pivot

In testing, there is one thing to note (which should be rare). 

If the head and spouse have different campuses, then that household will be counted in each location. 

[Sandy Mann]

Robert,

Thank you so much! This works perfectly!

You have been so helpful.

Sandy