Difference between revisions of "Talk:Collins Foundation Final Report"
(database changes) |
|||
| (8 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| − | + | Note: | |
| + | I have placed an Open Office version of this (with minor spelling/grammar edits on ryukin at /usr/local/fgstaff/grantwriting) | ||
| − | |||
| − | + | '''this is the formula used for generating numbers relating to volunteers & their hours''' | |
| − | |||
| − | + | =="Build" volunteer count by year:== | |
| − | |||
| − | |||
| − | Build | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | ''' | + | fgdb=# SELECT EXTRACT( YEAR FROM created), COUNT( DISTINCT memberid ) |
| − | + | FROM memberhour WHERE jobtype IN ('AdvancedTesting', 'Build', | |
| − | + | 'Enterprise', 'Evaluation', 'Laptops', 'Quality', 'Repair', 'Sorting', | |
| − | + | 'Teaching') GROUP BY 1 ORDER BY 1; | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| + | =="Adoption" volunteer count by year:== | ||
| + | |||
| + | fgdb=# SELECT EXTRACT( YEAR FROM created), COUNT( DISTINCT memberid ) | ||
| + | FROM memberhour WHERE jobtype IN ('DataEntry', 'FrontDesk', 'Misc', | ||
| + | 'Printers', 'Receiving', 'Recycling', 'Testing', 'Adoption') GROUP BY 1 | ||
| + | ORDER BY 1; | ||
| + | |||
| + | ==Total hours by year:== | ||
| + | |||
| + | fgdb=# SELECT EXTRACT( YEAR FROM created), SUM( hours ) FROM memberhour | ||
| + | WHERE jobtype NOT IN ('Adoption') GROUP BY 1 ORDER BY 1; | ||
| − | |||
: Note: Teaching might be people teaching adoption classes. [[User:Rfs|RfS]] 18:23, 24 January 2007 (PST) | : Note: Teaching might be people teaching adoption classes. [[User:Rfs|RfS]] 18:23, 24 January 2007 (PST) | ||
| + | |||
| + | ==Income/Expenses Calculations== | ||
| + | note: | ||
| + | all expenses = total expenses for year | ||
| + | |||
| + | 2004 | ||
| + | *Operational Income = total income - fundraisers - grants - online | ||
| + | *Overall Income = total income | ||
| + | |||
| + | 2005 | ||
| + | *Operational Income = total income - c4k donations - online donations - fundraisers - grants - untracked adjustments | ||
| + | *Overall Income = total income | ||
| + | |||
| + | 2006 | ||
| + | *Operational Income = total income - c4k donations - online donations - grants - unsolicited grants - employee matching | ||
| + | *Overall Income = total income | ||
| + | |||
| + | ==Website Traffic== | ||
| + | http://www.tump.com/~lmoore/logs/freegeek/ | ||
| + | (only gathers for 1 year) | ||
| + | |||
| + | ==Donors & Store Transactions== | ||
| + | fgdb=# SELECT extract( year from created ) as year, count(*) FROM | ||
| + | donation GROUP BY 1 ORDER BY 1; | ||
| + | |||
| + | fgdb=# SELECT extract( year from created ) as year, count(*) FROM sales | ||
| + | GROUP BY 1 ORDER BY 1; | ||
| + | |||
| + | == database changes == | ||
| + | |||
| + | the database queries are from the old fgdb database. in the future they can be used as a guide to write new queries against the fgdb.rb database which has a different structure. most of these queries should be pretty easy to convert. | ||
| + | |||
| + | [[User:Rfs|RfS]] 13:29, 8 February 2007 (PST) | ||
Latest revision as of 14:29, 8 February 2007
Note: I have placed an Open Office version of this (with minor spelling/grammar edits on ryukin at /usr/local/fgstaff/grantwriting)
this is the formula used for generating numbers relating to volunteers & their hours
"Build" volunteer count by year:
fgdb=# SELECT EXTRACT( YEAR FROM created), COUNT( DISTINCT memberid ) FROM memberhour WHERE jobtype IN ('AdvancedTesting', 'Build', 'Enterprise', 'Evaluation', 'Laptops', 'Quality', 'Repair', 'Sorting', 'Teaching') GROUP BY 1 ORDER BY 1;
"Adoption" volunteer count by year:
fgdb=# SELECT EXTRACT( YEAR FROM created), COUNT( DISTINCT memberid ) FROM memberhour WHERE jobtype IN ('DataEntry', 'FrontDesk', 'Misc', 'Printers', 'Receiving', 'Recycling', 'Testing', 'Adoption') GROUP BY 1 ORDER BY 1;
Total hours by year:
fgdb=# SELECT EXTRACT( YEAR FROM created), SUM( hours ) FROM memberhour WHERE jobtype NOT IN ('Adoption') GROUP BY 1 ORDER BY 1;
- Note: Teaching might be people teaching adoption classes. RfS 18:23, 24 January 2007 (PST)
Income/Expenses Calculations
note: all expenses = total expenses for year
2004
- Operational Income = total income - fundraisers - grants - online
- Overall Income = total income
2005
- Operational Income = total income - c4k donations - online donations - fundraisers - grants - untracked adjustments
- Overall Income = total income
2006
- Operational Income = total income - c4k donations - online donations - grants - unsolicited grants - employee matching
- Overall Income = total income
Website Traffic
http://www.tump.com/~lmoore/logs/freegeek/ (only gathers for 1 year)
Donors & Store Transactions
fgdb=# SELECT extract( year from created ) as year, count(*) FROM donation GROUP BY 1 ORDER BY 1;
fgdb=# SELECT extract( year from created ) as year, count(*) FROM sales GROUP BY 1 ORDER BY 1;
database changes
the database queries are from the old fgdb database. in the future they can be used as a guide to write new queries against the fgdb.rb database which has a different structure. most of these queries should be pretty easy to convert.
RfS 13:29, 8 February 2007 (PST)