Difference between revisions of "Talk:Big Hardware Donations"

From FreekiWiki
Jump to navigation Jump to search
(added comment/question, removed some raw data)
(compiled data into table)
Line 15: Line 15:
  
 
  donationid |            created            |                    org                    | sum
 
  donationid |            created            |                    org                    | sum
−
------------+-------------------------------+---------------------------------------------+-----
+
-----------+-------------------------------+---------------------------------------------+-----
  
 
       13332 | 2004-06-16 15:16:38-07        | Viablelinks, Inc.                          |  73
 
       13332 | 2004-06-16 15:16:38-07        | Viablelinks, Inc.                          |  73
Line 36: Line 36:
 
       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
 
       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
 
 
       15951 | 2004-10-19 11:32:43.544972-07 | Nickel Ads                                  |  36
 
       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
 
       9362 | 2003-11-19 00:00:00-08        | K. Robert Allen, Inc. / Docupro Services    |  35
Line 59: Line 58:
 
== other results ==
 
== 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]]
 
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 >= 500 GROUP BY 1 ORDER BY 1;
−
date_part | count
 
−
-----------+-------
 
−
      2001 |    1
 
−
      2002 |    1
 
−
      2003 |    3
 
−
      2004 |    19
 
−
      2005 |    11
 
−
(5 rows)
 
  
 
fgdb=# SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM donation WHERE cashdonation + monitorfee >= 400 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;
−
date_part | count
+
----
−
-----------+-------
+
<TABLE FRAME=VOID CELLSPACING=0 COLS=6 RULES=GROUPS BORDER=1>
−
      2001 |    2
+
<TR>
−
      2002 |    2
+
<TD WIDTH=86 HEIGHT=17 ALIGN=LEFT><BR></TD>
−
      2003 |    5
+
<TD WIDTH=86 ALIGN=RIGHT SDNUM="9;0;[$$-9]#,##0.00;[RED]-[$$-9]#,##0.00">$500 &amp; up</TD>
−
      2004 |    24
+
 
−
      2005 |    14
+
<TD WIDTH=86 ALIGN=RIGHT>400 &amp; up</TD>
−
(5 rows)
+
<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>
  
−
fgdb=# SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM donation WHERE cashdonation + monitorfee >= 300 GROUP BY 1 ORDER BY 1;
+
<TD ALIGN=RIGHT SDVAL="12" SDNUM="9;">12</TD>
−
date_part | count
+
<TD ALIGN=RIGHT SDVAL="22" SDNUM="9;">22</TD>
−
-----------+-------
+
<TD ALIGN=RIGHT SDVAL="98" SDNUM="9;">98</TD>
−
      2001 |    2
+
</TR>
−
      2002 |    4
+
<TR>
−
      2003 |    12
+
<TD HEIGHT=17 ALIGN=RIGHT SDVAL="2004" SDNUM="9;">2004</TD>
−
      2004 |    33
+
<TD ALIGN=RIGHT SDVAL="19" SDNUM="9;">19</TD>
−
      2005 |    28
 
−
(5 rows)
 
  
−
fgdb=# SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM donation WHERE cashdonation + monitorfee >= 200 GROUP BY 1 ORDER BY 1;
+
<TD ALIGN=RIGHT SDVAL="24" SDNUM="9;">24</TD>
−
date_part | count
+
<TD ALIGN=RIGHT SDVAL="33" SDNUM="9;">33</TD>
−
-----------+-------
+
<TD ALIGN=RIGHT SDVAL="63" SDNUM="9;">63</TD>
−
      2001 |    5
+
<TD ALIGN=RIGHT SDVAL="193" SDNUM="9;">193</TD>
−
      2002 |    11
+
</TR>
−
      2003 |    22
+
<TR>
−
      2004 |    63
+
<TD HEIGHT=17 ALIGN=RIGHT SDVAL="2005" SDNUM="9;">2005</TD>
−
      2005 |    68
 
−
(5 rows)
 
  
−
fgdb=# SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM donation WHERE cashdonation + monitorfee >= 100 GROUP BY 1 ORDER BY 1;
+
<TD ALIGN=RIGHT SDVAL="11" SDNUM="9;">11</TD>
−
date_part | count
+
<TD ALIGN=RIGHT SDVAL="14" SDNUM="9;">14</TD>
−
-----------+-------
+
<TD ALIGN=RIGHT SDVAL="28" SDNUM="9;">28</TD>
−
      2001 |    17
+
<TD ALIGN=RIGHT SDVAL="68" SDNUM="9;">68</TD>
−
      2002 |    51
+
<TD ALIGN=RIGHT SDVAL="253" SDNUM="9;">253</TD>
−
      2003 |    98
+
</TR>
−
      2004 |  193
+
</TABLE>
−
      2005 |  253
 
−
(5 rows)
 

Revision as of 12:40, 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
Retrieved from "http://wiki.freegeek.org/index.php?title=Talk:Big_Hardware_Donations&oldid=11223"