GO
IF EXISTS(
    SELECT 1
    FROM sysobjects
    WHERE Type = 'P'
          AND Name = 'Reports_Ytd_arrest_totals_comparison'
)
  DROP PROCEDURE Reports_Ytd_arrest_totals_comparison
GO
CREATE PROCEDURE Reports_Ytd_arrest_totals_comparison
  (
      @YearsToCompare VARCHAR(30)
    , @FilterType     INTEGER = 0
    , @FilterValue    VARCHAR(1000) = NULL
  )
AS
  BEGIN
    SET NOCOUNT ON;
    DECLARE @m_statistics TABLE(
    GroupHeader [TypCrimeDetail]
    , [Month] VARCHAR(3)
    , [Year] VARCHAR(4)
    )

    DECLARE @m_years TABLE([Year] INT);

    INSERT @m_years ([Year])
      SELECT DISTINCT [Number]
      FROM [dbo].[dbIntegerListToTable](@YearsToCompare)
      ORDER BY [Number]

    -- For all cases, grouped by crime class
    IF @FilterType = 0
      BEGIN
        INSERT INTO @m_statistics
          SELECT
            CC.ccClass                                   CrimeClass,
            LEFT(DATENAME(MONTH, [NAC].[ArrestDate]), 3) [Month],
            DATEPART(YEAR, [NAC].[ArrestDate])           [Year]
          FROM NameArrestContact NAC
            JOIN Associations A
              ON A.AssociateTo = NAC.NameArrestContactId
            JOIN CrimeCodes CC
              ON A.AssociateWith = CC.CrimeCodesId
          WHERE DATEPART(YEAR, [NAC].[ArrestDate]) IN (SELECT [Year]
                                                       FROM @m_years)
                AND A.AssociateToType = 'NACT'
                AND A.AssociateWithType = 'CRCD'
      END
    -- Filter by crime classification
    ELSE IF @FilterType = 5
      BEGIN
        DECLARE @m_classes TABLE([Class] VARCHAR(20));

        INSERT @m_classes ([Class])
          SELECT DISTINCT [string]
          FROM [dbo].[dbStringListToTable](@FilterValue)

        DECLARE @m_person BIT = (
          SELECT 1
          FROM @m_classes c
          WHERE c.[Class] = 'CrimeAgainstPerson'
        );
        DECLARE @m_prop BIT = (
          SELECT 1
          FROM @m_classes c
          WHERE c.[Class] = 'CrimeAgainstProperty'
        );
        DECLARE @m_society BIT = (
          SELECT 1
          FROM @m_classes c
          WHERE c.[Class] = 'CrimeAgainstSociety'
        );
        DECLARE @m_part1 BIT = (
          SELECT 1
          FROM @m_classes c
          WHERE c.[Class] = 'Part_1'
        );
        DECLARE @m_part2 BIT = (
          SELECT 1
          FROM @m_classes c
          WHERE c.[Class] = 'Part_2'
        );
        DECLARE @m_drug BIT = (
          SELECT 1
          FROM @m_classes c
          WHERE c.[Class] = 'DrugAbuse'
        );
        DECLARE @m_liquor BIT = (
          SELECT 1
          FROM @m_classes c
          WHERE c.[Class] = 'LiquorLaws'
        );
        DECLARE @m_weapon BIT = (
          SELECT 1
          FROM @m_classes c
          WHERE c.[Class] = 'WeaponCrimes'
        );
        DECLARE @m_hate BIT = (
          SELECT 1
          FROM @m_classes c
          WHERE c.[Class] = 'HateCrime'
        );
        DECLARE @m_crime BIT = (
          SELECT 1
          FROM @m_classes c
          WHERE c.[Class] = 'CriminalActivity'
        );
        DECLARE @m_service BIT = (
          SELECT 1
          FROM @m_classes c
          WHERE c.[Class] = 'ServiceActivity'
        );
        DECLARE @m_president BIT = (
          SELECT 1
          FROM @m_classes c
          WHERE c.[Class] = 'PresidentRept'
        );
        DECLARE @m_chief BIT = (
          SELECT 1
          FROM @m_classes c
          WHERE c.[Class] = 'ChiefRept'
        );
        DECLARE @m_other BIT = (
          SELECT 1
          FROM @m_classes c
          WHERE c.[Class] = 'Other'
        );

        INSERT INTO @m_statistics
          SELECT
            CC.ccClass                                   CrimeClass,
            LEFT(DATENAME(MONTH, [NAC].[ArrestDate]), 3) [Month],
            DATEPART(YEAR, [NAC].[ArrestDate])           [Year]
          FROM NameArrestContact NAC
            JOIN Associations A
              ON A.AssociateTo = NAC.NameArrestContactId
            JOIN CrimeCodes CC
              ON A.AssociateWith = CC.CrimeCodesId
          WHERE DATEPART(YEAR, [NAC].[ArrestDate]) IN (SELECT [Year]
                                                       FROM @m_years)
                AND A.AssociateToType = 'NACT'
                AND A.AssociateWithType = 'CRCD'
                AND (
                  (@m_person = 1 AND [CC].[ccAgainstPerson] = 1)
                  OR (@m_prop = 1 AND [CC].[ccAgainstProperty] = 1)
                  OR (@m_society = 1 AND [CC].[ccAgainstSociety] = 1)
                  OR (@m_part1 = 1 AND [CC].[ccUCRPart1] = 1)
                  OR (@m_part2 = 1 AND [CC].[ccPart2] = 1)
                  OR (@m_drug = 1 AND [CC].[ccDrugAbuse] = 1)
                  OR (@m_liquor = 1 AND [CC].[ccLiquorLaw] = 1)
                  OR (@m_weapon = 1 AND [CC].[ccWeapon] = 1)
                  OR (@m_hate = 1 AND [CC].[ccHateCrime] = 1)
                  OR (@m_crime = 1 AND [CC].[ccCrime] = 1)
                  OR (@m_service = 1 AND [CC].[ccService] = 1)
                  OR (@m_president = 1 AND [CC].[ccUserDefined1] = 1)
                  OR (@m_chief = 1 AND [CC].[ccUserDefined2] = 1)
                  OR (@m_other = 1
                      AND [CC].[ccAgainstPerson] = 0
                      AND [CC].[ccAgainstProperty] = 0
                      AND [CC].[ccAgainstSociety] = 0
                      AND [CC].[ccUCRPart1] = 0
                      AND [CC].[ccPart2] = 0
                      AND [CC].[ccDrugAbuse] = 0
                      AND [CC].[ccLiquorLaw] = 0
                      AND [CC].[ccWeapon] = 0
                      AND [CC].[ccHateCrime] = 0
                      AND [CC].[ccCrime] = 0
                      AND [CC].[ccService] = 0
                      AND [CC].[ccUserDefined1] = 0
                      AND [CC].[ccUserDefined2] = 0)
                )
      END

    SELECT
      [R].GroupHeader,
      [R].[Year],
      ISNULL([R].[Jan], 0) [Jan],
      ISNULL([R].[Feb], 0) [Feb],
      ISNULL([R].[Mar], 0) [Mar],
      ISNULL([R].[Apr], 0) [Apr],
      ISNULL([R].[May], 0) [May],
      ISNULL([R].[Jun], 0) [Jun],
      ISNULL([R].[Jul], 0) [Jul],
      ISNULL([R].[Aug], 0) [Aug],
      ISNULL([R].[Sep], 0) [Sep],
      ISNULL([R].[Oct], 0) [Oct],
      ISNULL([R].[Nov], 0) [Nov],
      ISNULL([R].[Dec], 0) [Dec]
    FROM (
           SELECT *
           FROM (
                  SELECT
                    [CC].GroupHeader,
                    [y].[Year]         [Year],
                    [R].[Month],
                    COUNT([R].[Month]) [count]
                  FROM @m_years [y]
                    -- cross join is needed to add years when event did not occurred at all
                    CROSS JOIN (
                                 SELECT DISTINCT [S].GroupHeader
                                 FROM @m_statistics [S]
                               ) [CC]
                    LEFT JOIN @m_statistics [R]
                      ON [y].[Year] = [R].[Year]
                         AND [CC].GroupHeader = [R].GroupHeader
                  GROUP BY [CC].GroupHeader
                    , [y].[Year]
                    , [R].[Month]
                ) t
           PIVOT (
             SUM([t].[count])
           FOR [Month] IN (Jan, Feb, Mar, Apr,
               May, Jun, Jul, Aug, Sep, Oct, Nov, [Dec])
           ) p
         ) [R]
    ORDER BY [R].GroupHeader
  END