-- SourcesByDetailLength.sql
-- adapted from SourceTemplatesUsageList.sql
-- Source Template List with Citation Details
-- created by romermb 14 Feb 2010
-- 2010-02-15 rev by ve3meo - optimised for speed
-- 2011-10-13 ve3meo - for finding long Source Details using !MyFreeForm template
--            to aid finding citations in need of a value in [ShortPage] for a 
--            shorter Short Footnote
 
-- Person Citations

SELECT SrcDetFields, * FROM
(
SELECT   SourceTemplateTable.Name COLLATE NOCASE AS 'Source Template Name',
         SourceTable.Name COLLATE NOCASE AS 'Source Name',
         LENGTH(CAST(CitationTable.Fields AS Text)) AS 'LenDet',
         'Person' AS 'Citation Type',
         NULL AS 'Fact Type',
         NULL AS MRIN,
         NameTable.OwnerID AS 'RIN 1',
         NameTable.Surname COLLATE NOCASE AS 'Surname 1',
         NameTable.Suffix COLLATE NOCASE AS 'Suffix 1',
         NameTable.Prefix COLLATE NOCASE AS 'Prefix 1',
         NameTable.Given COLLATE NOCASE AS 'Given 1',
         NameTable.Nickname COLLATE NOCASE AS 'Nickname 1',
         NULL AS 'RIN 2',
         NULL AS 'Surname 2',
         NULL AS 'Suffix 2',
         NULL AS 'Prefix 2',
         NULL AS 'Given 2',
         NULL AS 'Nickname 2',
         COUNT(1) AS Records,
         CAST(CitationTable.Fields AS TEXT) AS 'SrcDetFields'
FROM     SourceTemplateTable
         INNER JOIN SourceTable ON
         SourceTemplateTable.TemplateID = SourceTable.TemplateID
         INNER JOIN CitationTable ON
         SourceTable.SourceID = CitationTable.SourceID
         INNER JOIN NameTable ON
         CitationTable.OwnerID = NameTable.OwnerID
WHERE    CitationTable.OwnerType = 0 AND +NameTable.IsPrimary = 1
GROUP BY 1, 2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
 
UNION ALL
 
-- Family Citations
SELECT   SourceTemplateTable.Name COLLATE NOCASE AS 'Source Template Name',
         SourceTable.Name COLLATE NOCASE AS 'Source Name',
         LENGTH(CAST(CitationTable.Fields AS Text)) AS 'LenDet',
         'Family' AS 'Citation Type',
         NULL AS 'Fact Type',
         FamilyTable.FamilyID AS MRIN,
         NameTable1.OwnerID AS 'RIN 1',
         NameTable1.Surname COLLATE NOCASE AS 'Surname 1',
         NameTable1.Suffix COLLATE NOCASE AS 'Suffix 1',
         NameTable1.Prefix COLLATE NOCASE AS 'Prefix 1',
         NameTable1.Given COLLATE NOCASE AS 'Given 1',
         NameTable1.Nickname COLLATE NOCASE AS 'Nickname 1',
         NameTable2.OwnerID AS 'RIN 2',
         NameTable2.Surname COLLATE NOCASE AS 'Surname 2',
         NameTable2.Suffix COLLATE NOCASE AS 'Suffix 2',
         NameTable2.Prefix COLLATE NOCASE AS 'Prefix 2',
         NameTable2.Given COLLATE NOCASE AS 'Given 2',
         NameTable2.Nickname COLLATE NOCASE AS 'Nickname 2',
         COUNT(1) AS Records,
         CAST(CitationTable.Fields AS TEXT) AS 'SrcDetFields'
FROM     SourceTemplateTable
         INNER JOIN SourceTable ON
         SourceTemplateTable.TemplateID = SourceTable.TemplateID
         INNER JOIN CitationTable ON
         SourceTable.SourceID = CitationTable.SourceID
         INNER JOIN FamilyTable ON
         CitationTable.OwnerID = FamilyTable.FamilyID
         INNER JOIN NameTable AS NameTable1 ON
         FamilyTable.FatherID = NameTable1.OwnerID
         INNER JOIN NameTable AS NameTable2 ON
         FamilyTable.MotherID = NameTable2.OwnerID
WHERE    CitationTable.OwnerType = 1 AND +NameTable1.IsPrimary = 1 AND
         +NameTable2.IsPrimary = 1
GROUP BY 1, 2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
 
UNION ALL
 
-- Person Fact Citations
SELECT   SourceTemplateTable.Name COLLATE NOCASE AS 'Source Template Name',
         SourceTable.Name COLLATE NOCASE AS 'Source Name',
         LENGTH(CAST(CitationTable.Fields AS Text)) AS 'LenDet',
         'Fact - Person' AS 'Citation Type',
         FactTypeTable.Name COLLATE NOCASE AS 'Fact Type',
         NULL AS MRIN,
         NameTable.OwnerID AS 'RIN 1',
         NameTable.Surname COLLATE NOCASE AS 'Surname 1',
         NameTable.Suffix COLLATE NOCASE AS 'Suffix 1',
         NameTable.Prefix COLLATE NOCASE AS 'Prefix 1',
         NameTable.Given COLLATE NOCASE AS 'Given 1',
         NameTable.Nickname COLLATE NOCASE AS 'Nickname 1',
         NULL AS 'RIN 2',
         NULL AS 'Surname 2',
         NULL AS 'Suffix 2',
         NULL AS 'Prefix 2',
         NULL AS 'Given 2',
         NULL AS 'Nickname 2',
         COUNT(1) AS Records,
         CAST(CitationTable.Fields AS TEXT) AS 'SrcDetFields'
FROM     SourceTemplateTable
         INNER JOIN SourceTable ON
         SourceTemplateTable.TemplateID = SourceTable.TemplateID
         INNER JOIN CitationTable ON
         SourceTable.SourceID = CitationTable.SourceID
         INNER JOIN EventTable ON
         CitationTable.OwnerID = EventTable.EventID
         INNER JOIN FactTypeTable ON
         EventTable.EventType = FactTypeTable.FactTypeID
         INNER JOIN NameTable ON
         EventTable.OwnerID = NameTable.OwnerID
WHERE    CitationTable.OwnerType = 2 AND +NameTable.IsPrimary = 1 AND
         EventTable.OwnerType = 0
GROUP BY 1, 2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
 
UNION ALL
 
-- Family Fact Citations
SELECT   SourceTemplateTable.Name COLLATE NOCASE AS 'Source Template Name',
         SourceTable.Name COLLATE NOCASE AS 'Source Name',
         LENGTH(CAST(CitationTable.Fields AS Text)) AS 'LenDet',
         'Fact - Family' AS 'Citation Type',
         FactTypeTable.Name COLLATE NOCASE AS 'Fact Type',
         FamilyTable.FamilyID AS MRIN,
         NameTable1.OwnerID AS 'RIN 1',
         NameTable1.Surname COLLATE NOCASE AS 'Surname 1',
         NameTable1.Suffix COLLATE NOCASE AS 'Suffix 1',
         NameTable1.Prefix COLLATE NOCASE AS 'Prefix 1',
         NameTable1.Given COLLATE NOCASE AS 'Given 1',
         NameTable1.Nickname COLLATE NOCASE AS 'Nickname 1',
         NameTable2.OwnerID AS 'RIN 2',
         NameTable2.Surname COLLATE NOCASE AS 'Surname 2',
         NameTable2.Suffix COLLATE NOCASE AS 'Suffix 2',
         NameTable2.Prefix COLLATE NOCASE AS 'Prefix 2',
         NameTable2.Given COLLATE NOCASE AS 'Given 2',
         NameTable2.Nickname COLLATE NOCASE AS 'Nickname 2',
         COUNT(1) AS Records,
         CAST(CitationTable.Fields AS TEXT) AS 'SrcDetFields'
FROM     SourceTemplateTable
         INNER JOIN SourceTable ON
         SourceTemplateTable.TemplateID = SourceTable.TemplateID
         INNER JOIN CitationTable ON
         SourceTable.SourceID = CitationTable.SourceID
         INNER JOIN EventTable ON
         CitationTable.OwnerID = EventTable.EventID
         INNER JOIN FactTypeTable ON
         EventTable.EventType = FactTypeTable.FactTypeID
         INNER JOIN FamilyTable ON
         EventTable.OwnerID = FamilyTable.FamilyID
         INNER JOIN NameTable AS NameTable1 ON
         FamilyTable.FatherID = NameTable1.OwnerID
         INNER JOIN NameTable AS NameTable2 ON
         FamilyTable.MotherID = NameTable2.OwnerID
WHERE    CitationTable.OwnerType = 2 AND +NameTable1.IsPrimary = 1 AND
         +NameTable2.IsPrimary = 1 AND EventTable.OwnerType = 1
GROUP BY 1, 2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
 
UNION ALL
 
-- Alternate Name Fact Citations
SELECT   SourceTemplateTable.Name COLLATE NOCASE AS 'Source Template Name',
         SourceTable.Name COLLATE NOCASE AS 'Source Name',
         LENGTH(CAST(CitationTable.Fields AS Text)) AS 'LenDet',
         'Fact - Alt Name' AS 'Citation Type',
         'Alt Name' AS 'Fact Type',
         NULL AS MRIN,
         NameTable2.OwnerID AS 'RIN 1',
         NameTable2.Surname COLLATE NOCASE AS 'Surname 1',
         NameTable2.Suffix COLLATE NOCASE AS 'Suffix 1',
         NameTable2.Prefix COLLATE NOCASE AS 'Prefix 1',
         NameTable2.Given COLLATE NOCASE AS 'Given 1',
         NameTable2.Nickname COLLATE NOCASE AS 'Nickname 1',
         NULL AS 'RIN 2',
         NULL AS 'Surname 2',
         NULL AS 'Suffix 2',
         NULL AS 'Prefix 2',
         NULL AS 'Given 2',
         NULL AS 'Nickname 2',
         COUNT(1) AS Records,
         CAST(CitationTable.Fields AS TEXT) AS 'SrcDetFields'
FROM     SourceTemplateTable
         INNER JOIN SourceTable ON
         SourceTemplateTable.TemplateID = SourceTable.TemplateID
         INNER JOIN CitationTable ON
         SourceTable.SourceID = CitationTable.SourceID
         INNER JOIN NameTable AS NameTable1 ON
         CitationTable.OwnerID = NameTable1.NameID
         INNER JOIN NameTable AS NameTable2 ON
         NameTable1.OwnerID = NameTable2.NameID
WHERE    CitationTable.OwnerType = 7
GROUP BY 1, 2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
 
ORDER BY 1, 3, 2, 7, 13, 5
)
WHERE SrcDetFields LIKE '%ShortPage</Name><Value/>%'
ORDER BY LenDet DESC
;