Difference between revisions of "Talk:Big Hardware Donations"

From FreekiWiki
Jump to navigation Jump to search
 
(6 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
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. [[User:Rfs|RfS]]
 
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. [[User:Rfs|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, --[[User:Halfasspete|Pete]] 10:18, 3 Jan 2006 (PST)
 
: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, --[[User:Halfasspete|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? --[[User:Halfasspete|Pete]] 11:18, 4 Jan 2006 (PST)
 +
  
 
==donations of 25+ systems, with org name and date of donation. ==
 
==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)
 
(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.
  
 
[[User:Rfs|RfS]] 17:06, 3 Jan 2006 (PST)
 
[[User:Rfs|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
 
  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. [[User:Rfs|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;
 +
----
 +
<TABLE FRAME=VOID CELLSPACING=0 COLS=6 RULES=GROUPS BORDER=1>
 +
<TR>
 +
<TD WIDTH=86 HEIGHT=17 ALIGN=LEFT><BR></TD>
 +
<TD WIDTH=86 ALIGN=RIGHT SDNUM="9;0;[$$-9]#,##0.00;[RED]-[$$-9]#,##0.00">$500 &amp; up</TD>
 +
 
 +
<TD WIDTH=86 ALIGN=RIGHT>400 &amp; up</TD>
 +
<TD WIDTH=86 ALIGN=RIGHT>300 &amp; up</TD>
 +
<TD WIDTH=86 ALIGN=RIGHT>200 &amp; up</TD>
 +
<TD WIDTH=86 ALIGN=RIGHT>100 &amp; up</TD>
 +
 
 +
</TR>
 +
<TR>
 +
<TD HEIGHT=17 ALIGN=RIGHT SDVAL="2001" SDNUM="9;">2001</TD>
 +
<TD ALIGN=RIGHT SDVAL="1" SDNUM="9;">1</TD>
 +
<TD ALIGN=RIGHT SDVAL="2" SDNUM="9;">2</TD>
 +
<TD ALIGN=RIGHT SDVAL="2" SDNUM="9;">2</TD>
 +
<TD ALIGN=RIGHT SDVAL="5" SDNUM="9;">5</TD>
 +
 
 +
<TD ALIGN=RIGHT SDVAL="17" SDNUM="9;">17</TD>
 +
</TR>
 +
<TR>
 +
<TD HEIGHT=17 ALIGN=RIGHT SDVAL="2002" SDNUM="9;">2002</TD>
 +
<TD ALIGN=RIGHT SDVAL="1" SDNUM="9;">1</TD>
 +
<TD ALIGN=RIGHT SDVAL="2" SDNUM="9;">2</TD>
 +
<TD ALIGN=RIGHT SDVAL="4" SDNUM="9;">4</TD>
 +
 
 +
<TD ALIGN=RIGHT SDVAL="11" SDNUM="9;">11</TD>
 +
<TD ALIGN=RIGHT SDVAL="51" SDNUM="9;">51</TD>
 +
</TR>
 +
<TR>
 +
<TD HEIGHT=17 ALIGN=RIGHT SDVAL="2003" SDNUM="9;">2003</TD>
 +
<TD ALIGN=RIGHT SDVAL="3" SDNUM="9;">3</TD>
 +
<TD ALIGN=RIGHT SDVAL="5" SDNUM="9;">5</TD>
 +
 
 +
<TD ALIGN=RIGHT SDVAL="12" SDNUM="9;">12</TD>
 +
<TD ALIGN=RIGHT SDVAL="22" SDNUM="9;">22</TD>
 +
<TD ALIGN=RIGHT SDVAL="98" SDNUM="9;">98</TD>
 +
</TR>
 +
<TR>
 +
<TD HEIGHT=17 ALIGN=RIGHT SDVAL="2004" SDNUM="9;">2004</TD>
 +
<TD ALIGN=RIGHT SDVAL="19" SDNUM="9;">19</TD>
 +
 
 +
<TD ALIGN=RIGHT SDVAL="24" SDNUM="9;">24</TD>
 +
<TD ALIGN=RIGHT SDVAL="33" SDNUM="9;">33</TD>
 +
<TD ALIGN=RIGHT SDVAL="63" SDNUM="9;">63</TD>
 +
<TD ALIGN=RIGHT SDVAL="193" SDNUM="9;">193</TD>
 +
</TR>
 +
<TR>
 +
<TD HEIGHT=17 ALIGN=RIGHT SDVAL="2005" SDNUM="9;">2005</TD>
 +
 
 +
<TD ALIGN=RIGHT SDVAL="11" SDNUM="9;">11</TD>
 +
<TD ALIGN=RIGHT SDVAL="14" SDNUM="9;">14</TD>
 +
<TD ALIGN=RIGHT SDVAL="28" SDNUM="9;">28</TD>
 +
<TD ALIGN=RIGHT SDVAL="68" SDNUM="9;">68</TD>
 +
<TD ALIGN=RIGHT SDVAL="253" SDNUM="9;">253</TD>
 +
</TR>
 +
</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
 
       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
 
       27701 | 2006-01-03 13:09:29.702188-08 | OnPoint Community Credit Union              |  89
Line 16: Line 136:
 
       4297 | 2002-09-27 00:00:00-07        | Hoffman Construction                        |  70
 
       4297 | 2002-09-27 00:00:00-07        | Hoffman Construction                        |  70
 
       6178 | 2003-03-21 00:00:00-08        | Powell's Books                              |  68
 
       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
 
       17629 | 2004-12-30 14:02:20.017766-08 | Industrial Design Corp.                    |  63
−
      19878 | 2005-03-22 12:47:00.032323-08 | Cascadia Behavioral Healthcare, Inc        |  63
 
−
      18489 | 2005-01-28 12:23:29.985689-08 | Fourth Judicial Court                      |  63
 
 
       3427 | 2002-07-02 00:00:00-07        | St. Helens Computer Center                  |  60
 
       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
 
         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
 
       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
 
       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
 
       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
+
        812 | 2001-05-10 00:00:00-07        | Banfield Pet Hospitals                      |  50
 
       10645 | 2004-02-11 00:00:00-08        | ODS Company                                |  48
 
       10645 | 2004-02-11 00:00:00-08        | ODS Company                                |  48
 
       6585 | 2003-04-24 00:00:00-07        | Sheerwater Support Services                |  46
 
       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
 
       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
 
       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
 
       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
 
         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
 
       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
 
       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
 
       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
 
       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
 
       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
 
       15951 | 2004-10-19 11:32:43.544972-07 | Nickel Ads                                  |  36
 
       22820 | 2005-07-08 11:53:41.94278-07  | MEI                                        |  35
 
       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
 
       9362 | 2003-11-19 00:00:00-08        | K. Robert Allen, Inc. / Docupro Services    |  35
−
      20907 | 2005-04-29 16:43:23.581844-07 | Extensis Inc.                              |  35
 
 
       13049 | 2004-06-03 13:23:41-07        | Lewis & Clark College, IT Dept MSC97        |  34
 
       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
 
       25576 | 2005-10-14 16:00:18.878065-07 | PAC Trust                                  |  33
 
       8896 | 2003-10-21 00:00:00-07        | Janus Youth Services                        |  33
 
       8896 | 2003-10-21 00:00:00-07        | Janus Youth Services                        |  33
 
       12639 | 2004-05-11 17:06:22-07        | Legal Aid                                  |  33
 
       12639 | 2004-05-11 17:06:22-07        | Legal Aid                                  |  33
−
      12903 | 2004-05-25 14:24:44-07        |                                            |  32
 
−
      9844 | 2003-12-23 00:00:00-08        |                                            |  31
 
 
       21442 | 2005-05-20 14:14:36.884423-07 | Smith CFI                                  |  31
 
       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
 
       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
 
       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
 
       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
 
       21236 | 2005-05-12 16:58:44.545532-07 | Riverdale Grade School                      |  30
−
      27734 | 2006-01-03 16:47:14.663946-08 |                                            |  30
 
 
       11161 | 2004-03-05 00:00:00-08        | Housing Authority of Portland              |  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
 
       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
 
       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
 
       16775 | 2004-11-23 15:45:48.954956-08 | More for the Buck                          |  29
Line 71: Line 184:
 
       18883 | 2005-02-11 11:20:13.016315-08 | World Computer Exchange                    |  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
 
       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
 
       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
 
       1588 | 2001-08-08 00:00:00-07        | Fiducial Columbia Turel Bookeping & Tax    |  28
−
      19603 | 2005-03-11 15:18:23.475702-08 | Clackamas County WES                        |  28
 
 
       24093 | 2005-08-20 15:25:30.319612-07 | Comcast Spotlight                          |  27
 
       24093 | 2005-08-20 15:25:30.319612-07 | Comcast Spotlight                          |  27
 
       7130 | 2003-06-11 00:00:00-07        | Grady Britton                              |  27
 
       7130 | 2003-06-11 00:00:00-07        | Grady Britton                              |  27
Line 80: Line 192:
 
       23904 | 2005-08-16 11:36:15.138263-07 | CareOregon                                  |  26
 
       23904 | 2005-08-16 11:36:15.138263-07 | CareOregon                                  |  26
 
       14705 | 2004-08-19 11:39:19.85555-07  | Precision Interconnect                      |  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
 
       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
 
       2316 | 2002-02-14 00:00:00-08        | Pacific Northwest Academy                  |  26
 +
      2123 | 2002-02-01 00:00:00-08        | Friendly House                              |  26
 
       17298 | 2004-12-17 14:55:49.247632-08 | Hercules, Inc                              |  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
 
       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
 
       18990 | 2005-02-15 15:15:34.607678-08 | Columbia River Inter-Tribal Fish Commission |  25
−
(76 rows)
+
(69 rows)
  
−
== other results ==
+
Same thing, but these are the ones without organizational names. Someone should hand doublecheck them to see if they really are organizations:
−
Here are some other numbers. 11 donors > 500 in 2005. 14 above 400, etc. Past years included. Tell me what you want. [[User:Rfs|RfS]]
 
  
−
fgdb=# SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM donation WHERE cashdonation + monitorfee >= 500 GROUP BY 1 ORDER BY 1;
+
      13409 | 2004-06-19 13:43:45-07        |                                            |  56
−
date_part | count
+
        749 | 2001-05-22 00:00:00-07        |                                            |  51
−
-----------+-------
+
        859 | 2001-05-10 00:00:00-07        |                                            |  50
−
      2001 |     1
+
        815 | 2001-05-10 00:00:00-07        |                                            |  45
−
      2002 |     1
+
      24912 | 2005-09-17 17:18:06.299302-07 |                                            |  39
−
       2003 |     3
+
      15974 | 2004-10-19 18:56:51.04655-07  |                                             | 37
−
      2004 |   19
+
       16416 | 2004-11-06 16:23:38.875921-08 |                                             |  34
−
       2005 |   11
+
       16415 | 2004-11-06 16:22:00.526544-08 |                                            |  34
−
(5 rows)
+
      14187 | 2004-07-23 13:29:41.584573-07 |                                             | 33
−
 
+
       12903 | 2004-05-25 14:24:44-07        |                                             | 32
−
fgdb=# SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM donation WHERE cashdonation + monitorfee >= 400 GROUP BY 1 ORDER BY 1;
+
      9844 | 2003-12-23 00:00:00-08        |                                             | 31
−
date_part | count
+
       22222 | 2005-06-17 14:52:28.75602-07 |                                             |  30
−
-----------+-------
+
      22221 | 2005-06-17 14:50:46.089537-07 |                                            |  30
−
      2001 |     2
+
      3396 | 2002-06-28 00:00:00-07        |                                            |  30
−
      2002 |     2
+
      27734 | 2006-01-03 16:47:14.663946-08 |                                             | 30
−
       2003 |     5
+
       23981 | 2005-08-17 15:21:35.393166-07 |                                             | 28
−
      2004 |   24
+
      5974 | 2003-03-04 00:00:00-08        |                                            | 26
−
      2005 |   14
+
      6958 | 2003-05-27 00:00:00-07        |                                             | 25
−
(5 rows)
+
(18 rows)
−
 
 
−
fgdb=# SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM donation WHERE cashdonation + monitorfee >= 300 GROUP BY 1 ORDER BY 1;
 
−
date_part | count
 
−
-----------+-------
 
−
      2001 |     2
 
−
      2002 |     4
 
−
       2003 |   12
 
−
      2004 |    33
 
−
      2005 |    28
 
−
(5 rows)
 
−
 
 
−
fgdb=# SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM donation WHERE cashdonation + monitorfee >= 200 GROUP BY 1 ORDER BY 1;
 
−
  date_part | count
 
−
-----------+-------
 
−
      2001 |     5
 
−
      2002 |   11
 
−
       2003 |   22
 
−
      2004 |   63
 
−
      2005 |   68
 
−
(5 rows)
 
−
 
 
−
fgdb=# SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM donation WHERE cashdonation + monitorfee >= 100 GROUP BY 1 ORDER BY 1;
 
−
date_part | count
 
−
-----------+-------
 
−
      2001 |   17
 
−
      2002 |   51
 
−
      2003 |   98
 
−
      2004 |   193
 
−
      2005 |  253
 
−
(5 rows)
 

Latest revision as of 14:02, 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
     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
       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
     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
     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
     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
     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
     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
     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
     21236 | 2005-05-12 16:58:44.545532-07 | Riverdale Grade School                      |  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
     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
     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
     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
(69 rows)

Same thing, but these are the ones without organizational names. Someone should hand doublecheck them to see if they really are organizations: 

     13409 | 2004-06-19 13:43:45-07        |                                             |  56
       749 | 2001-05-22 00:00:00-07        |                                             |  51
       859 | 2001-05-10 00:00:00-07        |                                             |  50
       815 | 2001-05-10 00:00:00-07        |                                             |  45
     24912 | 2005-09-17 17:18:06.299302-07 |                                             |  39
     15974 | 2004-10-19 18:56:51.04655-07  |                                             |  37
     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
     12903 | 2004-05-25 14:24:44-07        |                                             |  32
      9844 | 2003-12-23 00:00:00-08        |                                             |  31
     22222 | 2005-06-17 14:52:28.75602-07  |                                             |  30
     22221 | 2005-06-17 14:50:46.089537-07 |                                             |  30
      3396 | 2002-06-28 00:00:00-07        |                                             |  30
     27734 | 2006-01-03 16:47:14.663946-08 |                                             |  30
     23981 | 2005-08-17 15:21:35.393166-07 |                                             |  28
      5974 | 2003-03-04 00:00:00-08        |                                             |  26
      6958 | 2003-05-27 00:00:00-07        |                                             |  25
(18 rows)
Retrieved from "http://wiki.freegeek.org/index.php?title=Talk:Big_Hardware_Donations&oldid=18691"