SQL Join Issue, Doing Something Really Wrong With Self Joins?

Okay, so I am trying to get a given output in SQL to list a custom quarter value along with an accounts given activation and termination date. My company uses an off-standard quarter reckoning so I created a little schema matching the custom quarter recognition to standard months. The query works great until i try to get the thing to also include the termination quarter as well.

I am going to use this as a view and generate a report from its results. Here is the query thus far. I recognize that I am not very good SQL yet so i am trying hard to use best practices and easily readable code. Please feel free to critique anything at all or perhaps suggest a more efficient way of achieving my goal.

SELECT ZoneA.ZoneDescription
        ,TrackerA.Zone
        ,MasterListA.Name
        ,SubscriptionsA.ActivationDate
        ,SubscriptionsA.TerminationDate
        ,ParentA.ProductParentType
        ,MONTH(SubscriptionsA.ActivationDate) AS ActivationMonth
        ,MONTH(SubscriptionsA.TerminationDate) AS TerminationMonth
        ,QuartersA.CompanyFiscalQuarter AS Activatio开发者_如何学PythonnQuarter
        ,QuartersB.CompanyFiscalQuarter AS TerminationQuarter
FROM BalanceTracker.Zone AS ZoneA INNER JOIN
        BalanceTracker.TrackerAccounts AS TrackerA ON ZoneA.ZoneID = TrackerA.Zone INNER JOIN
        BalanceTracker.SweepAccounts AS SweepA ON TrackerA.TrackedAccount = SweepA.SweepAccount INNER JOIN
        Fed.MasterAccountList AS MasterListA ON SweepA.MasterAccountListID = MasterListA.MasterAccountListID INNER JOIN
        Products.Subscriptions AS SubscriptionsA ON MasterListA.MasterAccountListID = SubscriptionsA.SnlId INNER JOIN
        Products.ProductParent AS ParentA ON SubscriptionsA.ProductCode = ParentA.ProductCode INNER JOIN
        Calendar.Quarters AS QuartersA ON SubscriptionsA.ActivationMonth = QuartersA.MonthNumber,
        Products.Subscriptions AS SubscriptionsB INNER JOIN
        Calendar.Quarters AS QuartersB ON SubscriptionsB.TerminationMonth = QuartersB.CompanyFiscalQuarter
ORDER BY TrackerA.Zone, SubscriptionsA.ActivationDate

Tables are as follows:

-Zone: ZoneID (PK) -TrackerAccounts: Tracked Account (PK), Zone (FK) -SweepAccounts: SweepAccount (PK, FK on TrackerAccounts), MasterAccountListId (FK on MasterAccountList) -MasterAccountList: MasterAccountListId (PK) -Subscriptions: SubscriptionId (PK), SnlId (FK on MasterAccountList), ProductCode (FK on ProductParent) -ProductParent: ProductCode (PK)

-Quarters: MonthNumber (PK)

---

Your problem is that when you say , Products.Subscriptions AS SubscriptionsB you're doing a cross join, so it's going to output NxN rows where N is the total number of subscriptions. I doubt you need that join at all, since you don't appear to be using any values from that instance i.e this should work just fine:

SELECT ZoneA.ZoneDescription
        ,TrackerA.Zone
        ,MasterListA.Name
        ,SubscriptionsA.ActivationDate
        ,SubscriptionsA.TerminationDate
        ,ParentA.ProductParentType
        ,MONTH(SubscriptionsA.ActivationDate) AS ActivationMonth
        ,MONTH(SubscriptionsA.TerminationDate) AS TerminationMonth
        ,QuartersA.CompanyFiscalQuarter AS ActivationQuarter
        ,QuartersB.CompanyFiscalQuarter AS TerminationQuarter
FROM BalanceTracker.Zone AS ZoneA INNER JOIN
        BalanceTracker.TrackerAccounts AS TrackerA ON ZoneA.ZoneID = TrackerA.Zone INNER JOIN
        BalanceTracker.SweepAccounts AS SweepA ON TrackerA.TrackedAccount = SweepA.SweepAccount INNER JOIN
        Fed.MasterAccountList AS MasterListA ON SweepA.MasterAccountListID = MasterListA.MasterAccountListID INNER JOIN
        Products.Subscriptions AS SubscriptionsA ON MasterListA.MasterAccountListID = SubscriptionsA.SnlId INNER JOIN
        Products.ProductParent AS ParentA ON SubscriptionsA.ProductCode = ParentA.ProductCode INNER JOIN
        Calendar.Quarters AS QuartersA ON SubscriptionsA.ActivationMonth = QuartersA.MonthNumber LEFT JOIN
        Calendar.Quarters AS QuartersB ON SubscriptionsA.TerminationMonth = QuartersB.CompanyFiscalQuarter
ORDER BY TrackerA.Zone, SubscriptionsA.ActivationDate

---

Reformatted for me to get readability... You don't have a join condition to your second "Subscriptions" which would create a Cartesian join... Other than that, your query structure looks fine (overall)

SELECT 
      ZoneA.ZoneDescription
      ,TrackerA.Zone
      ,MasterListA.Name
      ,SubscriptionsA.ActivationDate
      ,SubscriptionsA.TerminationDate
      ,ParentA.ProductParentType
      ,MONTH(SubscriptionsA.ActivationDate) AS ActivationMonth
      ,MONTH(SubscriptionsA.TerminationDate) AS TerminationMonth
      ,QuartersA.CompanyFiscalQuarter AS ActivationQuarter
      ,QuartersB.CompanyFiscalQuarter AS TerminationQuarter
FROM 
   BalanceTracker.Zone AS ZoneA 
      INNER JOIN BalanceTracker.TrackerAccounts AS TrackerA 
         ON ZoneA.ZoneID = TrackerA.Zone 
         INNER JOIN BalanceTracker.SweepAccounts AS SweepA 
            ON TrackerA.TrackedAccount = SweepA.SweepAccount 
            INNER JOIN Fed.MasterAccountList AS MasterListA 
               ON SweepA.MasterAccountListID = MasterListA.MasterAccountListID 
               INNER JOIN Products.Subscriptions AS SubscriptionsA 
                  ON MasterListA.MasterAccountListID = SubscriptionsA.SnlId 
                  INNER JOIN Products.ProductParent AS ParentA 
                     ON SubscriptionsA.ProductCode = ParentA.ProductCode 
                  INNER JOIN Calendar.Quarters AS QuartersA 
                     ON SubscriptionsA.ActivationMonth = QuartersA.MonthNumber
                  INNER JOIN Calendar.Quarters AS QuartersB 
                     ON SubscriptionsA.TerminationMonth= QuartersB.CompanyFiscalQuarter    
ORDER BY 
   TrackerA.Zone, 
   SubscriptionsA.ActivationDate

---

Juat a guess that the last condition could be a join on QuartersB.MonthNumber and not on QuartersB.CompanyFiscalQuarter:

     Calendar.Quarters AS QuartersB 
         ON SubscriptionsA.TerminationMonth = QuartersB.MonthNumber  

You can also change the last join into a LEFT JOIN if as you say the TerminationMonth can be NULL.

SELECT 
      ZoneA.ZoneDescription
    , TrackerA.Zone
    , MasterListA.Name
    , SubscriptionsA.ActivationDate
    , SubscriptionsA.TerminationDate
    , ParentA.ProductParentType
    , MONTH(SubscriptionsA.ActivationDate) AS ActivationMonth
    , MONTH(SubscriptionsA.TerminationDate) AS TerminationMonth
    , QuartersA.CompanyFiscalQuarter AS ActivationQuarter
    , QuartersB.CompanyFiscalQuarter AS TerminationQuarter
FROM 
     BalanceTracker.Zone AS ZoneA  INNER JOIN
     BalanceTracker.TrackerAccounts AS TrackerA 
         ON ZoneA.ZoneID = TrackerA.Zone  INNER JOIN
     BalanceTracker.SweepAccounts AS SweepA 
         ON TrackerA.TrackedAccount = SweepA.SweepAccount  INNER JOIN
     Fed.MasterAccountList AS MasterListA 
         ON SweepA.MasterAccountListID = MasterListA.MasterAccountListID  INNER JOIN 
     Products.Subscriptions AS SubscriptionsA 
         ON MasterListA.MasterAccountListID = SubscriptionsA.SnlId  INNER JOIN 
     Products.ProductParent AS ParentA 
         ON SubscriptionsA.ProductCode = ParentA.ProductCode  INNER JOIN 
     Calendar.Quarters AS QuartersA 
         ON SubscriptionsA.ActivationMonth = QuartersA.MonthNumber  INNER JOIN 
     Calendar.Quarters AS QuartersB 
         ON SubscriptionsA.TerminationMonth = QuartersB.MonthNumber  
ORDER BY 
      TrackerA.Zone 
    , SubscriptionsA.ActivationDate