/*
rm7_dup_events.sql  Jerry Bryan  28 Mar 2023

Identifies rows in the EventTable which are identical except for the
EventID itself. The extra rows need to be deleted. There were few
enough of them that it was sufficient to identify them with this
script and then to delete them by hand.

This script arose because I was developing a script to identify differences
between RM7 and RM9 databases which I expected to be identical. In the
process, I discovered that my script was being confused by two different
facts for the same person that were identical in every respect in RM7.
The same identical facts for the same person had obviously been imported
into RM9 where they were also identical, causing further confustion.

Identical means identical. For example, it's acceptable to have multiple
Census facts for the same person for two different years or even to have
two Census facts for the same person for the same year as long as something
is different about the facts because the person was enumerated twice in the
same year. So I developed this script to identify and correct situations
in my RM7 database where there were identical duplicate facts prior to
checking for differences between my RM7 database and my RM9 database.

This script does not attempt to identify cases where the facts are identical
except for citations and media files. There were few enough identical facts
that was easier to identify such situations by hand.

For Family facts that are identical, this script only provides EventTable.OwnerID
which will be FamilyID's. I had planned to identify the people in such families by
hand, but none of my identical facts were family facts. So EventTable.OwnerID
was a PersonID in all cases.

The logic is simply to do a GROUP BY on every column except the EventID itself which
must be different and to do a COUNT on EventID. When there are rows that are identical
except for the EventID, the COUNT of EventID will not be equal to 1.

*/

SELECT E.OwnerID, COUNT(E.EventID) AS ecount, 
       E.EventType, FT.Name, E.OwnerType, E.OwnerID, E.FamilyID, E.PlaceID,
       E.SiteID, E.Date, E.SortDate, E.IsPrimary, E.IsPrivate, E.Proof,
       E.Status, E.EditDate, E.Sentence, E.Details, E.Note
FROM EventTable AS E
JOIN FactTypeTable AS FT ON FT.FactTypeID = E.EventType
GROUP BY E.EventType, E.OwnerType, E.OwnerID, E.FamilyID, E.PlaceID,
         E.SiteID, E.Date, E.SortDate, E.IsPrimary, E.IsPrivate, E.Proof,
         E.Status, E.EditDate, E.Sentence, E.Details, E.Note
HAVING ecount != 1