WITH SubscriptionInfo AS
(
    SELECT
        [Subscriptions].[Customer_Id]
        ,[DistributorTypes].[Name]                              AS [Distributor Type]
        ,[Customers].Zip_Id
        ,[Subscriptions].[UnsubscribeReason_Id]
        ,[Subscriptions].[Id]                                   AS [Subscription ID]
        ,CONVERT(DATE, [Subscriptions].[StartDate])             AS [Subscription Start Date]
        ,CONVERT(DATE, [Subscriptions].[EndDate])               AS [Subscription End Date]
        ,[PriorityLevels].PriorityLevel                         AS [Priority Level]
        ,CONVERT(DATE, [SubscriptionPriorityLevels].StartDate)  AS [Priority Level Start Date]
        ,CONVERT(DATE, [SubscriptionPriorityLevels].EndDate)    AS [Priority Level End Date]
        ,[FundingSources].[Name]                                AS [Funding Source]
        ,CONVERT(DATE, [SubscriptionFundingSources].StartDate)  AS [SubscriptionFundingSources Start Date]
        ,CONVERT(DATE, [SubscriptionFundingSources].EndDate)    AS [SubscriptionFundingSources End Date]
    FROM [Subscriptions]

    LEFT JOIN [SubscriptionPriorityLevels]
        ON [SubscriptionPriorityLevels].Subscription_Id = Subscriptions.Id
    LEFT JOIN [PriorityLevels]
        ON [PriorityLevels].Id = SubscriptionPriorityLevels.PriorityLevel_Id
    LEFT JOIN [SubscriptionFundingSources]
        ON [SubscriptionFundingSources].Subscription_Id = Subscriptions.Id
    LEFT JOIN [FundingSources]
        ON [FundingSources].Id = [SubscriptionFundingSources].FundingSource_Id
    LEFT JOIN [Customers]
        ON [Customers].Id = [Subscriptions].Customer_Id
    LEFT JOIN [DistributorTypes]
        ON [DistributorTypes].Id = Customers.DistributorType_Id

    WHERE
        ([Subscriptions].StartDate >= '2016-07-01')             -- Dummy dates, would later be parameters
        AND ([Subscriptions].EndDate <= '2017-06-30'
            OR [Subscriptions].EndDate IS NULL)
        AND ([PriorityLevels].PriorityLevel IN (2, 3))          -- Only care about these two levels
        AND ([Customers].DistributorType_Id = 1)                -- Distributor Type: Number One Distrubition
        AND ([SubscriptionFundingSources].FundingSource_Id = 2) -- Funding Source: First Bank
)

SELECT
    [SubscriptionInfo].Customer_Id
    ,[SubscriptionInfo].[Subscription ID]
    ,MAX([SubscriptionInfo].[Priority Level]) AS [Highest Priority Level]
    ,CASE   -- Determine which fiscal quarter each Subscription Start Date belongs to
        WHEN MONTH([SubscriptionInfo].[Subscription Start Date]) IN (7, 8, 9)       THEN 1  -- July, August, September
        WHEN MONTH([SubscriptionInfo].[Subscription Start Date]) IN (10, 11, 12)    THEN 2  -- October, November, December
        WHEN MONTH([SubscriptionInfo].[Subscription Start Date]) IN (1, 2, 3)       THEN 3  -- January, Feburary, March
                                                                                    ELSE 4  -- April, May, June
    END AS [Fiscal Quarter Start Date]
    ,CASE   -- Determine which fiscal quarter each Subscription Start Date belongs to
        WHEN MONTH([SubscriptionInfo].[Subscription End Date]) IN (7, 8, 9)         THEN 1  -- July, August, September
        WHEN MONTH([SubscriptionInfo].[Subscription End Date]) IN (10, 11, 12)      THEN 2  -- October, November, December
        WHEN MONTH([SubscriptionInfo].[Subscription End Date]) IN (1, 2, 3)         THEN 3  -- January, Feburary, March
                                                                                    ELSE 4  -- April, May, June
    END AS [Fiscal Quarter End Date]
FROM [SubscriptionInfo]


GROUP BY
    [SubscriptionInfo].Customer_Id
    ,[SubscriptionInfo].[Subscription ID]
    ,CASE   -- Group Subscription Start Date's into Fiscal Quarters
        WHEN MONTH([SubscriptionInfo].[Subscription Start Date]) IN (7, 8, 9)       THEN 1  -- July, August, September
        WHEN MONTH([SubscriptionInfo].[Subscription Start Date]) IN (10, 11, 12)    THEN 2  -- October, November, December
        WHEN MONTH([SubscriptionInfo].[Subscription Start Date]) IN (1, 2, 3)       THEN 3  -- January, Feburary, March
                                                                                    ELSE 4  -- April, May, June
    END
    ,CASE   -- Group Subscription End Date's into Fiscal Quarters
        WHEN MONTH([SubscriptionInfo].[Subscription End Date]) IN (7, 8, 9)         THEN 1  -- July, August, September
        WHEN MONTH([SubscriptionInfo].[Subscription End Date]) IN (10, 11, 12)      THEN 2  -- October, November, December
        WHEN MONTH([SubscriptionInfo].[Subscription End Date]) IN (1, 2, 3)         THEN 3  -- January, Feburary, March
                                                                                    ELSE 4  -- April, May, June
    END

ORDER BY
    [SubscriptionInfo].Customer_Id