Difference between revisions of "Talk:Big Hardware Donations"

From FreekiWiki
Jump to navigation Jump to search
(compiled data into table)
(OK, I'm brain dead)
Line 122: Line 122:
 
</TR>
 
</TR>
 
</TABLE>
 
</TABLE>
 +
 +
== OK, I'm brain dead ==
 +
 +
Many front desk folks use the word "computer" to describe the donation, rather than system. Here are some more accurate results (with updated SQL):
 +
 +
fgdb=# SELECT donationid, MAX( donation.created ) AS created, MAX( organization ) AS org, SUM(quantity) FROM donationline LEFT JOIN donation ON donationline.donationid = donation.id LEFT JOIN contact ON donation.contactid = contact.id WHERE description ILIKE '%system%' OR description ILIKE '%computer%' GROUP BY 1 HAVING SUM(quantity) >= 25 ORDER BY 4 DESC;
 +
 +
donationid |            created            |                    org                    | sum
 +
------------+-------------------------------+---------------------------------------------+-----
 +
      27492 | 2005-12-28 13:23:30.24604-08  | OnPoint Community Credit Union              | 117
 +
      27701 | 2006-01-03 13:09:29.702188-08 | OnPoint Community Credit Union              |  89
 +
      13332 | 2004-06-16 15:16:38-07        | Viablelinks, Inc.                          |  73
 +
      5876 | 2003-02-25 00:00:00-08        | OTAK                                        |  71
 +
      4297 | 2002-09-27 00:00:00-07        | Hoffman Construction                        |  70
 +
      6178 | 2003-03-21 00:00:00-08        | Powell's Books                              |  68
 +
      18489 | 2005-01-28 12:23:29.985689-08 | Fourth Judicial Court                      |  65
 +
      19878 | 2005-03-22 12:47:00.032323-08 | Cascadia Behavioral Healthcare, Inc        |  63
 +
      17629 | 2004-12-30 14:02:20.017766-08 | Industrial Design Corp.                    |  63
 +
      3427 | 2002-07-02 00:00:00-07        | St. Helens Computer Center                  |  60
 +
        723 | 2001-05-09 00:00:00-07        | Oregon Episcopal School                    |  58
 +
      22567 | 2005-06-29 12:49:43.728154-07 | Youth Employment Institute                  |  57
 +
      13409 | 2004-06-19 13:43:45-07        |                                            |  56
 +
      17215 | 2004-12-15 11:35:46.506987-08 | Industrial Design Corp.                    |  55
 +
      15751 | 2004-10-07 11:53:32.597446-07 | Estacada School District                    |  54
 +
      26505 | 2005-11-17 15:15:05.11239-08  | Warn Industries, Inc.                      |  54
 +
      14559 | 2004-08-11 11:17:19.901352-07 | Precision Interconnect                      |  51
 +
        749 | 2001-05-22 00:00:00-07        |                                            |  51
 +
        859 | 2001-05-10 00:00:00-07        |                                            |  50
 +
        812 | 2001-05-10 00:00:00-07        | Banfield Pet Hospitals                      |  50
 +
      10645 | 2004-02-11 00:00:00-08        | ODS Company                                |  48
 +
      6585 | 2003-04-24 00:00:00-07        | Sheerwater Support Services                |  46
 +
      14255 | 2004-07-28 13:37:10.390426-07 | St. Mary\'s Academy                        |  46
 +
      1008 | 2001-10-03 00:00:00-07        | Grocers Insurance Co.                      |  46
 +
      22350 | 2005-06-22 11:28:31.60112-07  | Housing Authority of Portland              |  45
 +
        815 | 2001-05-10 00:00:00-07        |                                            |  45
 +
      10595 | 2004-02-07 00:00:00-08        | Sauer-Danfoss                              |  44
 +
      1290 | 2001-11-15 00:00:00-08        | Planned Parenthood                          |  43
 +
        551 | 2001-03-29 00:00:00-08        | Bright Wood Corp.                          |  42
 +
      14152 | 2004-07-21 19:02:56.111239-07 | SRG Partnership Inc.                        |  41
 +
      10123 | 2004-01-14 00:00:00-08        | Willamette Week                            |  41
 +
      13952 | 2004-07-13 16:43:04-07        | US Probation Attn: Jack Codrou              |  40
 +
      22041 | 2005-06-10 16:56:20.264872-07 | Avamere Health Services                    |  40
 +
      24912 | 2005-09-17 17:18:06.299302-07 |                                            |  39
 +
      21585 | 2005-05-25 14:43:24.772943-07 | Powell's Books                              |  38
 +
      16063 | 2004-10-23 13:11:20.367096-07 | Stoel Rives LLP                            |  37
 +
      15974 | 2004-10-19 18:56:51.04655-07  |                                            |  37
 +
      16190 | 2004-10-29 14:18:56.975467-07 | Central City Concern                        |  36
 +
      15951 | 2004-10-19 11:32:43.544972-07 | Nickel Ads                                  |  36
 +
      22820 | 2005-07-08 11:53:41.94278-07  | MEI                                        |  35
 +
      20907 | 2005-04-29 16:43:23.581844-07 | Extensis Inc.                              |  35
 +
      9362 | 2003-11-19 00:00:00-08        | K. Robert Allen, Inc. / Docupro Services    |  35
 +
      13049 | 2004-06-03 13:23:41-07        | Lewis & Clark College, IT Dept MSC97        |  34
 +
      16416 | 2004-11-06 16:23:38.875921-08 |                                            |  34
 +
      16415 | 2004-11-06 16:22:00.526544-08 |                                            |  34
 +
      14187 | 2004-07-23 13:29:41.584573-07 |                                            |  33
 +
      25576 | 2005-10-14 16:00:18.878065-07 | PAC Trust                                  |  33
 +
      8896 | 2003-10-21 00:00:00-07        | Janus Youth Services                        |  33
 +
      12639 | 2004-05-11 17:06:22-07        | Legal Aid                                  |  33
 +
      12903 | 2004-05-25 14:24:44-07        |                                            |  32
 +
      21442 | 2005-05-20 14:14:36.884423-07 | Smith CFI                                  |  31
 +
      11276 | 2004-03-10 00:00:00-08        | Electra Cental Credit Union                |  31
 +
      9844 | 2003-12-23 00:00:00-08        |                                            |  31
 +
      15050 | 2004-09-07 12:34:33.990779-07 | Christenson Velagio                        |  30
 +
      22222 | 2005-06-17 14:52:28.75602-07  |                                            |  30
 +
      22221 | 2005-06-17 14:50:46.089537-07 |                                            |  30
 +
      13203 | 2004-06-10 13:45:18-07        | The ArtInstitute of Portland                |  30
 +
      21236 | 2005-05-12 16:58:44.545532-07 | Riverdale Grade School                      |  30
 +
      3396 | 2002-06-28 00:00:00-07        |                                            |  30
 +
      27734 | 2006-01-03 16:47:14.663946-08 |                                            |  30
 +
      11161 | 2004-03-05 00:00:00-08        | Housing Authority of Portland              |  30
 +
      26841 | 2005-12-02 13:48:56.610682-08 | City of Gresham                            |  30
 +
      7974 | 2003-08-19 00:00:00-07        | Precision Interconnect                      |  29
 +
      25545 | 2005-10-13 17:06:47.790585-07 | Fix My Dead PC                              |  29
 +
      16775 | 2004-11-23 15:45:48.954956-08 | More for the Buck                          |  29
 +
      20821 | 2005-04-27 13:17:48.458858-07 | Titan Corp                                  |  29
 +
      16442 | 2004-11-09 14:10:10.563814-08 | Starseed Foundation                        |  29
 +
      18883 | 2005-02-11 11:20:13.016315-08 | World Computer Exchange                    |  29
 +
      18673 | 2005-02-03 14:40:47.237201-08 | Ann Sacks                                  |  29
 +
      23981 | 2005-08-17 15:21:35.393166-07 |                                            |  28
 +
      15704 | 2004-10-05 15:39:16.00947-07  | St Anthony School                          |  28
 +
      19603 | 2005-03-11 15:18:23.475702-08 | Clackamas County WES                        |  28
 +
      1588 | 2001-08-08 00:00:00-07        | Fiducial Columbia Turel Bookeping & Tax    |  28
 +
      24093 | 2005-08-20 15:25:30.319612-07 | Comcast Spotlight                          |  27
 +
      7130 | 2003-06-11 00:00:00-07        | Grady Britton                              |  27
 +
      20150 | 2005-04-01 14:11:09.167632-08 | Columbia Housing Partners                  |  27
 +
      23904 | 2005-08-16 11:36:15.138263-07 | CareOregon                                  |  26
 +
      14705 | 2004-08-19 11:39:19.85555-07  | Precision Interconnect                      |  26
 +
      5974 | 2003-03-04 00:00:00-08        |                                            |  26
 +
      12836 | 2004-05-21 13:38:17-07        | Northwest Primary Care Group                |  26
 +
      11932 | 2004-04-07 00:00:00-07        | Fix My Dead PC                              |  26
 +
      2316 | 2002-02-14 00:00:00-08        | Pacific Northwest Academy                  |  26
 +
      2123 | 2002-02-01 00:00:00-08        | Friendly House                              |  26
 +
      6958 | 2003-05-27 00:00:00-07        |                                            |  25
 +
      17298 | 2004-12-17 14:55:49.247632-08 | Hercules, Inc                              |  25
 +
      19349 | 2005-03-02 16:34:09.381884-08 | Wieden + Kennedy                            |  25
 +
      18990 | 2005-02-15 15:15:34.607678-08 | Columbia River Inter-Tribal Fish Commission |  25
 +
(87 rows)

Revision as of 13:53, 4 January 2006

I can probably pull some stats from the database if given some criteria, like a minimum dollar amount to look for or a minimum number of lines in a receipt. RfS

That would be helpful. I'd start with 2005 only. (I'm interested in the long haul as well, but it's less important.) Maybe try over $100, but increase that number if it pulls up too much. At this stage I'd like to see maybe 15 company names. I like your line number idea as well, so use whichever method you think it's better. Many thanks, --Pete 10:18, 3 Jan 2006 (PST)
This is great. Gives me a good idea of the scope of things. Contact info would be very helpful (even just a person's name.) I'd like to contact a few of these for testimonials. Also, just to be sure I got these right- the last column is the number of systems? And OnPoint Credit Union = Portland Teachers Credit Union? --Pete 11:18, 4 Jan 2006 (PST)


donations of 25+ systems, with org name and date of donation.

(note the top two came in from the same donor in a one week period)

after you get what you want, we should remove the raw data, but the SQL might be nice to re-create this again.

RfS 17:06, 3 Jan 2006 (PST)

SELECT donationid, MAX( donation.created ) AS created, MAX( organization ) AS org, SUM(quantity) FROM donationline LEFT JOIN donation ON donationline.donationid = donation.id LEFT JOIN contact ON donation.contactid = contact.id WHERE description ILIKE '%system%' GROUP BY 1 HAVING SUM(quantity) >= 25 ORDER BY 4 DESC; 

donationid |            created            |                     org                     | sum
-----------+-------------------------------+---------------------------------------------+-----

     13332 | 2004-06-16 15:16:38-07        | Viablelinks, Inc.                           |  73
      5876 | 2003-02-25 00:00:00-08        | OTAK                                        |  71
      4297 | 2002-09-27 00:00:00-07        | Hoffman Construction                        |  70
      6178 | 2003-03-21 00:00:00-08        | Powell's Books                              |  68
     17629 | 2004-12-30 14:02:20.017766-08 | Industrial Design Corp.                     |  63
      3427 | 2002-07-02 00:00:00-07        | St. Helens Computer Center                  |  60
       723 | 2001-05-09 00:00:00-07        | Oregon Episcopal School                     |  58
     15751 | 2004-10-07 11:53:32.597446-07 | Estacada School District                    |  54
     14559 | 2004-08-11 11:17:19.901352-07 | Precision Interconnect                      |  51
     17215 | 2004-12-15 11:35:46.506987-08 | Industrial Design Corp.                     |  50
     10645 | 2004-02-11 00:00:00-08        | ODS Company                                 |  48
      6585 | 2003-04-24 00:00:00-07        | Sheerwater Support Services                 |  46
     14255 | 2004-07-28 13:37:10.390426-07 | St. Mary\'s Academy                         |  46
     10595 | 2004-02-07 00:00:00-08        | Sauer-Danfoss                               |  44
       551 | 2001-03-29 00:00:00-08        | Bright Wood Corp.                           |  42
     10123 | 2004-01-14 00:00:00-08        | Willamette Week                             |  41
     14152 | 2004-07-21 19:02:56.111239-07 | SRG Partnership Inc.                        |  41
     13952 | 2004-07-13 16:43:04-07        | US Probation Attn: Jack Codrou              |  40
     16063 | 2004-10-23 13:11:20.367096-07 | Stoel Rives LLP                             |  37
     15951 | 2004-10-19 11:32:43.544972-07 | Nickel Ads                                  |  36
      9362 | 2003-11-19 00:00:00-08        | K. Robert Allen, Inc. / Docupro Services    |  35
     13049 | 2004-06-03 13:23:41-07        | Lewis & Clark College, IT Dept MSC97        |  34
      8896 | 2003-10-21 00:00:00-07        | Janus Youth Services                        |  33
     12639 | 2004-05-11 17:06:22-07        | Legal Aid                                   |  33
     11276 | 2004-03-10 00:00:00-08        | Electra Cental Credit Union                 |  31
     16190 | 2004-10-29 14:18:56.975467-07 | Central City Concern                        |  30
     15050 | 2004-09-07 12:34:33.990779-07 | Christenson Velagio                         |  30
     13203 | 2004-06-10 13:45:18-07        | The ArtInstitute of Portland                |  30
     11161 | 2004-03-05 00:00:00-08        | Housing Authority of Portland               |  30
     16775 | 2004-11-23 15:45:48.954956-08 | More for the Buck                           |  29
     16442 | 2004-11-09 14:10:10.563814-08 | Starseed Foundation                         |  29
     15704 | 2004-10-05 15:39:16.00947-07  | St Anthony School                           |  28
      1588 | 2001-08-08 00:00:00-07        | Fiducial Columbia Turel Bookeping & Tax     |  28
      7130 | 2003-06-11 00:00:00-07        | Grady Britton                               |  27
     14705 | 2004-08-19 11:39:19.85555-07  | Precision Interconnect                      |  26
     12836 | 2004-05-21 13:38:17-07        | Northwest Primary Care Group                |  26
      2316 | 2002-02-14 00:00:00-08        | Pacific Northwest Academy                   |  26
     17298 | 2004-12-17 14:55:49.247632-08 | Hercules, Inc                               |  25

other results

Here are some other numbers. 11 donors > 500 in 2005. 14 above 400, etc. Past years included. Tell me what you want. RfS

First two queries (of five) below, followed by a table compiling all 5 years' data for 5 categories.

fgdb=# SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM donation WHERE cashdonation + monitorfee >= 500 GROUP BY 1 ORDER BY 1;

fgdb=# SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM donation WHERE cashdonation + monitorfee >= 400 GROUP BY 1 ORDER BY 1;


$500 & up 400 & up 300 & up 200 & up 100 & up
2001 1 2 2 5 17
2002 1 2 4 11 51
2003 3 5 12 22 98
2004 19 24 33 63 193
2005 11 14 28 68 253

OK, I'm brain dead

Many front desk folks use the word "computer" to describe the donation, rather than system. Here are some more accurate results (with updated SQL):

fgdb=# SELECT donationid, MAX( donation.created ) AS created, MAX( organization ) AS org, SUM(quantity) FROM donationline LEFT JOIN donation ON donationline.donationid = donation.id LEFT JOIN contact ON donation.contactid = contact.id WHERE description ILIKE '%system%' OR description ILIKE '%computer%' GROUP BY 1 HAVING SUM(quantity) >= 25 ORDER BY 4 DESC; 

donationid |            created            |                     org                     | sum

+-------------------------------+---------------------------------------------+----- 

     27492 | 2005-12-28 13:23:30.24604-08  | OnPoint Community Credit Union              | 117
     27701 | 2006-01-03 13:09:29.702188-08 | OnPoint Community Credit Union              |  89
     13332 | 2004-06-16 15:16:38-07        | Viablelinks, Inc.                           |  73
      5876 | 2003-02-25 00:00:00-08        | OTAK                                        |  71
      4297 | 2002-09-27 00:00:00-07        | Hoffman Construction                        |  70
      6178 | 2003-03-21 00:00:00-08        | Powell's Books                              |  68
     18489 | 2005-01-28 12:23:29.985689-08 | Fourth Judicial Court                       |  65
     19878 | 2005-03-22 12:47:00.032323-08 | Cascadia Behavioral Healthcare, Inc         |  63
     17629 | 2004-12-30 14:02:20.017766-08 | Industrial Design Corp.                     |  63
      3427 | 2002-07-02 00:00:00-07        | St. Helens Computer Center                  |  60
       723 | 2001-05-09 00:00:00-07        | Oregon Episcopal School                     |  58
     22567 | 2005-06-29 12:49:43.728154-07 | Youth Employment Institute                  |  57
     13409 | 2004-06-19 13:43:45-07        |                                             |  56
     17215 | 2004-12-15 11:35:46.506987-08 | Industrial Design Corp.                     |  55
     15751 | 2004-10-07 11:53:32.597446-07 | Estacada School District                    |  54
     26505 | 2005-11-17 15:15:05.11239-08  | Warn Industries, Inc.                       |  54
     14559 | 2004-08-11 11:17:19.901352-07 | Precision Interconnect                      |  51
       749 | 2001-05-22 00:00:00-07        |                                             |  51
       859 | 2001-05-10 00:00:00-07        |                                             |  50
       812 | 2001-05-10 00:00:00-07        | Banfield Pet Hospitals                      |  50
     10645 | 2004-02-11 00:00:00-08        | ODS Company                                 |  48
      6585 | 2003-04-24 00:00:00-07        | Sheerwater Support Services                 |  46
     14255 | 2004-07-28 13:37:10.390426-07 | St. Mary\'s Academy                         |  46
      1008 | 2001-10-03 00:00:00-07        | Grocers Insurance Co.                       |  46
     22350 | 2005-06-22 11:28:31.60112-07  | Housing Authority of Portland               |  45
       815 | 2001-05-10 00:00:00-07        |                                             |  45
     10595 | 2004-02-07 00:00:00-08        | Sauer-Danfoss                               |  44
      1290 | 2001-11-15 00:00:00-08        | Planned Parenthood                          |  43
       551 | 2001-03-29 00:00:00-08        | Bright Wood Corp.                           |  42
     14152 | 2004-07-21 19:02:56.111239-07 | SRG Partnership Inc.                        |  41
     10123 | 2004-01-14 00:00:00-08        | Willamette Week                             |  41
     13952 | 2004-07-13 16:43:04-07        | US Probation Attn: Jack Codrou              |  40
     22041 | 2005-06-10 16:56:20.264872-07 | Avamere Health Services                     |  40
     24912 | 2005-09-17 17:18:06.299302-07 |                                             |  39
     21585 | 2005-05-25 14:43:24.772943-07 | Powell's Books                              |  38
     16063 | 2004-10-23 13:11:20.367096-07 | Stoel Rives LLP                             |  37
     15974 | 2004-10-19 18:56:51.04655-07  |                                             |  37
     16190 | 2004-10-29 14:18:56.975467-07 | Central City Concern                        |  36
     15951 | 2004-10-19 11:32:43.544972-07 | Nickel Ads                                  |  36
     22820 | 2005-07-08 11:53:41.94278-07  | MEI                                         |  35
     20907 | 2005-04-29 16:43:23.581844-07 | Extensis Inc.                               |  35
      9362 | 2003-11-19 00:00:00-08        | K. Robert Allen, Inc. / Docupro Services    |  35
     13049 | 2004-06-03 13:23:41-07        | Lewis & Clark College, IT Dept MSC97        |  34
     16416 | 2004-11-06 16:23:38.875921-08 |                                             |  34
     16415 | 2004-11-06 16:22:00.526544-08 |                                             |  34
     14187 | 2004-07-23 13:29:41.584573-07 |                                             |  33
     25576 | 2005-10-14 16:00:18.878065-07 | PAC Trust                                   |  33
      8896 | 2003-10-21 00:00:00-07        | Janus Youth Services                        |  33
     12639 | 2004-05-11 17:06:22-07        | Legal Aid                                   |  33
     12903 | 2004-05-25 14:24:44-07        |                                             |  32
     21442 | 2005-05-20 14:14:36.884423-07 | Smith CFI                                   |  31
     11276 | 2004-03-10 00:00:00-08        | Electra Cental Credit Union                 |  31
      9844 | 2003-12-23 00:00:00-08        |                                             |  31
     15050 | 2004-09-07 12:34:33.990779-07 | Christenson Velagio                         |  30
     22222 | 2005-06-17 14:52:28.75602-07  |                                             |  30
     22221 | 2005-06-17 14:50:46.089537-07 |                                             |  30
     13203 | 2004-06-10 13:45:18-07        | The ArtInstitute of Portland                |  30
     21236 | 2005-05-12 16:58:44.545532-07 | Riverdale Grade School                      |  30
      3396 | 2002-06-28 00:00:00-07        |                                             |  30
     27734 | 2006-01-03 16:47:14.663946-08 |                                             |  30
     11161 | 2004-03-05 00:00:00-08        | Housing Authority of Portland               |  30
     26841 | 2005-12-02 13:48:56.610682-08 | City of Gresham                             |  30
      7974 | 2003-08-19 00:00:00-07        | Precision Interconnect                      |  29
     25545 | 2005-10-13 17:06:47.790585-07 | Fix My Dead PC                              |  29
     16775 | 2004-11-23 15:45:48.954956-08 | More for the Buck                           |  29
     20821 | 2005-04-27 13:17:48.458858-07 | Titan Corp                                  |  29
     16442 | 2004-11-09 14:10:10.563814-08 | Starseed Foundation                         |  29
     18883 | 2005-02-11 11:20:13.016315-08 | World Computer Exchange                     |  29
     18673 | 2005-02-03 14:40:47.237201-08 | Ann Sacks                                   |  29
     23981 | 2005-08-17 15:21:35.393166-07 |                                             |  28
     15704 | 2004-10-05 15:39:16.00947-07  | St Anthony School                           |  28
     19603 | 2005-03-11 15:18:23.475702-08 | Clackamas County WES                        |  28
      1588 | 2001-08-08 00:00:00-07        | Fiducial Columbia Turel Bookeping & Tax     |  28
     24093 | 2005-08-20 15:25:30.319612-07 | Comcast Spotlight                           |  27
      7130 | 2003-06-11 00:00:00-07        | Grady Britton                               |  27
     20150 | 2005-04-01 14:11:09.167632-08 | Columbia Housing Partners                   |  27
     23904 | 2005-08-16 11:36:15.138263-07 | CareOregon                                  |  26
     14705 | 2004-08-19 11:39:19.85555-07  | Precision Interconnect                      |  26
      5974 | 2003-03-04 00:00:00-08        |                                             |  26
     12836 | 2004-05-21 13:38:17-07        | Northwest Primary Care Group                |  26
     11932 | 2004-04-07 00:00:00-07        | Fix My Dead PC                              |  26
      2316 | 2002-02-14 00:00:00-08        | Pacific Northwest Academy                   |  26
      2123 | 2002-02-01 00:00:00-08        | Friendly House                              |  26
      6958 | 2003-05-27 00:00:00-07        |                                             |  25
     17298 | 2004-12-17 14:55:49.247632-08 | Hercules, Inc                               |  25
     19349 | 2005-03-02 16:34:09.381884-08 | Wieden + Kennedy                            |  25
     18990 | 2005-02-15 15:15:34.607678-08 | Columbia River Inter-Tribal Fish Commission |  25

(87 rows)

Retrieved from "http://wiki.freegeek.org/index.php?title=Talk:Big_Hardware_Donations&oldid=11224"