-- Place_Frequency.sql
/*
2013-03-25 Tom Holden ve3meo
rev 2013-03-25 TotEvents error corrected
rev 2013-03-27 Place:Abbrev corrected to Place:short in comments.
    TotEvents extended to include family events. MaxEvents remains for Indiv only.
    Unused places also listed.
Returns frequency of use for each Place in the database
- total events for each place
- max number of events for any person and a person having that max number
 (family events not counted)
Useful for finding places in need of Abbreviations or Geocoding or Unused
*/
SELECT Places.PlaceID
	,EventsByPlace AS TotEvents -- total events for place
	,Events AS MaxEvents -- Max Events for a place by Person (Indiv facts)
	,PersonID -- Person having the max events for that place
	,NAME AS Place -- place name used by Place or Place:original in sentence template
	,Abbrev -- value used by Place:short in sentence template
	,Latitude / 10000000.0 AS Latitude -- in decimal degrees, North+
	,Longitude / 10000000.0 Longitude -- in decimal degrees, East+
	,Normalized AS Standardized -- the Standardized value in the Edit Place screen
FROM PlaceTable Places
LEFT JOIN (
	SELECT PlaceID
		,PersonID
		,Max(Events) AS Events
	FROM (
		-- table of Places for which Persons have events and the number of events for each combinatio
		SELECT PlaceID
			,OwnerID AS PersonID
			,COUNT() AS Events
		FROM EventTable
		WHERE OwnerType = 0 -- Individual, not Family, events
		GROUP BY PlaceID
			,OwnerID -- to aggregate number of events by Place-Person combo
		ORDER BY Events ASC -- to order so that the next GROUP BY PlaceID will extract the highest value of Events for a Place
		)
	GROUP BY PlaceID
	) AS PersonEvents ON Places.PlaceID = PersonEvents.PlaceID
LEFT JOIN (
	-- table of total events per place
	SELECT PlaceID
		,COUNT() AS EventsByPlace
	FROM EventTable
	GROUP BY PlaceID
	) AS AllEvents ON Places.PlaceID = AllEvents.PlaceID
WHERE PlaceType = 0 -- user defined Place; excludes Place Details and Temples
	--GROUP BY Places.PlaceID -- to aggregate TotEvents and extract highest value of MaxEvents for Place-Person combo
ORDER BY MaxEvents DESC -- initial view puts the highest max events first as priority for attention
	;