Hi folks,
We are doing a bit of data clean up and I'm stuck on a few queries I'm hoping someone can help with.
1.How many active Tessitura accounts have no email address?
2. How many active Tessitura accounts have no mailing address?
3. How many active individual records are tied to household accounts that have giving history?
4. How many active individual records are tied to household accounts that have NO giving history?
I think this is because I can't seem to wrap my head around excluding a sub query.
Any help on this would be greatly appreciated.
Thanks,
Tiffany
Tiffany Elliott,
It sounds like you might also do some JOIN or LEFT JOIN work to get you what you need, depending on what criteria you are wanting. If it is literally as simple as active accounts with no e-mail of any kind on the account, something like this would work: (literally, give me all the accounts that are not active or merged that do not have an e-mail address attached to them)
USE impresario SELECT * FROM T_CUSTOMER c LEFT JOIN T_EADDRESS e ON c.customer_no = e.customer_no WHERE c.inactive = 1 AND e.eaddress_no IS NULL
There are other considerations to be had. Specific types, valid address, etc... Also, what about household accounts that do not have an e-mail address but have attached individuals that do? Those queries are going to be a little more complex. For your addresses, do your account creations require address? If so, you may need to set an exception for some form of "INVALID ADDRESS" or whatever you use. With regards to giving history, you may need to consider accounts that have a backed out contribution or have not yet paid for a pledge.
Hopefully that will give you at least a bit of a start, though.
John A. Moskal II
This is certianly helpful, thank you.
In terms of the address types we don't require accounts to have an address, so i think this same query you provided should work with the T_ADDRESS table.
Thank you for the thoughtful responses- lots to consider.