FGdb Reports - Revision history

Ryan52: /* SQL samples for use in creating reports */ added

2009-06-27T18:10:01Z

SQL samples for use in creating reports: added

Ryan52: /* Volunteer Related */ implemented

2009-06-17T19:42:42Z

Volunteer Related: implemented

Ryan52: /* Atypical trend reports */ since they are implemented, remove them

2009-06-17T18:38:11Z

Atypical trend reports: since they are implemented, remove them

Ryan52: /* Atypical trend reports */

2009-05-31T00:43:38Z

Atypical trend reports

Rfs: /* Atypical trend reports */

2009-05-09T21:53:02Z

Atypical trend reports

Rfs: /* Volunteer Realted */

2009-05-09T21:13:02Z

Volunteer Realted

Rfs: /* SQL samples for use in creating reports */

2009-05-09T21:07:34Z

SQL samples for use in creating reports

Rfs at 17:36, 9 May 2009

2009-05-09T17:36:33Z

Rfs: /* Atypical trend reports */

2009-05-09T17:34:05Z

Atypical trend reports

Rfs: /* Other */

2009-05-09T17:28:30Z

Other

← Older revision		Revision as of 18:10, 27 June 2009
Line 76:		Line 76:
	== SQL samples for use in creating reports ==		== SQL samples for use in creating reports ==

−	~~=== Gizmo type sales and counts ===~~
−
−	(Note by changing "WHERE sale_id IS NOT NULL" to "WHERE donation_id IS NOT NULL", we could turn the count of gizmos '''sold''' report into a count of gizmos '''received''' report quite easily. Same for disbursements and recyclings, obviously.)
−	~~-- 6 is a sample gizmo_type (laptops)~~
−	~~-- 2009-04-01 is a sample beginning date~~
−	~~-- 2009-04-30 is a sample end date~~
−
−	~~-- sales amount by type example:~~
−	~~SELECT~~
−	~~SUM( unit_price_cents )/100.0 AS due~~
−	~~FROM gizmo_events~~
−	~~WHERE sale_id IS NOT NULL~~
−	~~AND gizmo_type_id = 6~~
−	~~AND gizmo_events.created_at BETWEEN '2009-04-01' AND '2009-04-30'~~
−	;
−
−	~~-- sales count by type example:~~
−	~~SELECT~~
−	~~SUM( gizmo_count ) AS count~~
−	~~FROM gizmo_events~~
−	~~WHERE sale_id IS NOT NULL~~
−	~~AND gizmo_type_id = 6~~
−	~~AND gizmo_events.created_at BETWEEN '2009-04-01' AND '2009-04-30'~~
−	;
−
−	~~=== Gizmo category sales and counts ===~~
−	~~Similar to above, but based on broad category instead of specific gizmo type:~~
−
−	(Note again, by changing "WHERE sale_id IS NOT NULL" to "WHERE donation_id IS NOT NULL", we could turn the count of gizmos '''sold''' report into a count of gizmos '''received''' report quite easily. Same for disbursements and recyclings, obviously.)
−
−	~~-- 2 is a sample gizmo_category (monitors)~~
−	~~-- 2009-04-01 is a sample beginning date~~
−	~~-- 2009-04-30 is a sample end date~~
−
−	~~-- sales amount by category example:~~
−	~~SELECT~~
−	~~SUM( unit_price_cents )/100.0 AS due~~
−	~~FROM gizmo_events~~
−	~~LEFT JOIN gizmo_types ON gizmo_events.gizmo_type_id = gizmo_types.id~~
−	~~WHERE sale_id IS NOT NULL~~
−	~~AND gizmo_types.gizmo_category_id = 2~~
−	~~AND gizmo_events.created_at BETWEEN '2009-04-01' AND '2009-04-30'~~
−	;
−
−	~~-- sales count by category example:~~
−	~~SELECT~~
−	~~SUM( gizmo_count ) AS count~~
−	~~FROM gizmo_events~~
−	~~LEFT JOIN gizmo_types ON gizmo_events.gizmo_type_id = gizmo_types.id~~
−	~~WHERE sale_id IS NOT NULL~~
−	~~AND gizmo_types.gizmo_category_id = 2~~
−	~~AND gizmo_events.created_at BETWEEN '2009-04-01' AND '2009-04-30'~~
−	;
−
	Since there are so few categories, we could show multiple lines on the same graph if we want. This might help do that:		Since there are so few categories, we could show multiple lines on the same graph if we want. This might help do that:

@@ Line 198: / Line 198: @@
     AND (volunteer_task_types.parent_id = 27 OR volunteer_task_types.id = 27)
+    ;
 [[Category: FGdb]]

← Older revision		Revision as of 18:38, 17 June 2009
Line 36:		Line 36:
	* Same as above, but limit to discount type -- that is I want to see trends on systems that were sold at a volunteer discount.		* Same as above, but limit to discount type -- that is I want to see trends on systems that were sold at a volunteer discount.

−	~~=== Atypical trend reports ===~~

−	~~'''These are implemented'''~~
−
−	~~Most trend reports show numbers on a daily, weekly, monthly, quarterly, or yearly basis (for a given period of time).~~
−
−	We will also want some reports that show day of the week or hour of the day (for a given period of time). For example "How many donations came in on all Sundays, Monday, Tuesdays, Wednesdays, Thursdays, Fridays, and Saturdays (in a given period of time).
−
−	~~This SQL produces one row for each day of the week:~~
−
−	~~Sales in dollars for 2008 (by Day of Week):~~
−	~~-- 2008-01-01 is a sample beginning date~~
−	~~-- 2008-12-31 is a sample end date~~
−
−	~~-- sales amount by category example:~~
−	~~SELECT~~
−	~~EXTRACT( DOW FROM created_at ) AS dow,~~
−	~~SUM( reported_amount_due_cents )/100.0 AS amount~~
−	~~FROM sales~~
−	~~WHERE sales.created_at BETWEEN '2008-01-01' AND '2008-12-31'~~
−	~~GROUP BY 1~~
−	~~ORDER BY 1~~
−	;
−
−	~~Donation count for 2008 (by Day of Week):~~
−
−	~~-- 2008-01-01 is a sample beginning date~~
−	~~-- 2008-12-31 is a sample end date~~
−
−	~~-- donations count by DOW:~~
−	~~SELECT~~
−	~~EXTRACT( DOW FROM created_at ) AS dow,~~
−	~~COUNT(*) AS donation_count~~
−	~~FROM donations~~
−	~~WHERE donations.created_at BETWEEN '2008-01-01' AND '2008-12-31'~~
−	~~GROUP BY 1~~
−	~~ORDER BY 1~~
−	;
−
−
−	~~Depending on the coding requirements, it might be better to loop through the numbers (0-6 == Sun-Sat) and get each DOW separately, for example (here 2 means Tuesday):~~
−
−	~~SELECT~~
−	~~COUNT(*) AS donation_count~~
−	~~FROM donations~~
−	~~WHERE donations.created_at BETWEEN '2008-01-01' AND '2008-12-31'~~
−	~~AND EXTRACT( DOW FROM created_at ) = 2~~
−	;
−
−	~~Hours of the day work like this (12 will return anything that happened noon or after, but before 1 pm):~~
−
−	~~SELECT~~
−	~~COUNT(*) AS donation_count~~
−	~~FROM donations~~
−	~~WHERE donations.created_at BETWEEN '2008-01-01' AND '2008-12-31'~~
−	~~AND EXTRACT( HOUR FROM created_at ) = 12~~
−	;
−
−	~~Or hour by hour sales amounts:~~
−
−	~~SELECT~~
−	~~EXTRACT( HOUR FROM created_at ), SUM(reported_amount_due_cents) AS sales_amount~~
−	~~FROM sales~~
−	~~WHERE sales.created_at BETWEEN '2008-01-01' AND '2008-12-31'~~
−	~~GROUP BY 1~~
−	~~ORDER BY 1~~
−	;

	=== Other ===		=== Other ===

← Older revision		Revision as of 00:43, 31 May 2009
Line 37:		Line 37:

	=== Atypical trend reports ===		=== Atypical trend reports ===
		+
		+	'''These are implemented'''
		+
	Most trend reports show numbers on a daily, weekly, monthly, quarterly, or yearly basis (for a given period of time).		Most trend reports show numbers on a daily, weekly, monthly, quarterly, or yearly basis (for a given period of time).

@@ Line 89: / Line 89: @@
     WHERE donations.created_at BETWEEN '2008-01-01' AND '2008-12-31'
     AND EXTRACT( HOUR FROM created_at ) = 12
+    ;

@@ Line 210: / Line 210: @@
-=== Volunteer Realted ===
+=== Volunteer Related ===
 Hours worked by volunteer task:
@@ Line 221: / Line 221: @@
+    ;
-Hours worked by program:
+ -- to get number of volunteers that worked in volunteer_task_type = 28 (receiving) between Jan 1, 2008 and Dec 31, 2008:
   -- to get a list of programs do this:
@@ Line 232: / Line 240: @@
 * Program 24 == adoption.
 * Program 27 == build.
   -- to get number of hours worked in program = 27 (build) between Jan 1, 2008 and Dec 31, 2008:

@@ Line 208: / Line 208: @@
     GROUP BY 1
+    ;
 [[Category: FGdb]]

← Older revision		Revision as of 17:36, 9 May 2009
Line 1:		Line 1:
		+	This is related to ticket number [[FGRT:12475\|12475]].
		+
	== Trend Reports ==		== Trend Reports ==
	(These show trends over time)		(These show trends over time)

@@ Line 47: / Line 47: @@
   -- sales amount by category example:
   SELECT
-   EXTRACT( DOW FROM created_at ) AS dow,
+  EXTRACT( DOW FROM created_at ) AS dow,
     SUM( reported_amount_due_cents )/100.0 AS amount
     FROM sales
@@ Line 71: / Line 71: @@
 === Other ===

@@ Line 33: / Line 33: @@
 * Total sales ($) by gizmo_type (and gizmo_category) over a period of time. (This is a trends or "over time" type report.)
 * Same as above, but limit to discount type -- that is I want to see trends on systems that were sold at a volunteer discount.
 === Other ===