title: what is this code doing can not understand it
body: <p>I am trying to figure out what a query is doing and I just do not understand why it would join its self to its self on multiple occassions highlighted bit is the piece i am talking about?</p>
  
  <p>its the part that starts with the [SupplyStatusUpdated] = COALESCE( gas supply and gas supply history</p>
  
  <pre><code>                        USE [CRM];
  
          SELECT  gs.GasSupplyID, 
                  [Account ID] = acc.AccountID,
                  [GMSReference] = cast(gms.SiteRefNum as varchar),
                  [AccountNumber] = cast(gms.AccountNum as varchar), 
                  [Organisation Name] = prf.[Name] ,
                  con.DeclaredDate, 
                  [Contract Date] = CAST(con.ContractDate AS DATE),
                  [Contract Version] = cv.Name,
                  [Contract Status] = cs.Name,
                  loa.ContractEndDate [LOA CED],
                  gs.CurrentSupplierEndDate [PrevSupplierEndDate],
                  loa.ContractEndDate,
                  con.StartDate,
                  [Supply Status] = gss.Name,
                  [SupplyStatusUpdated] = COALESCE(
                                                      (   
                                                          SELECT TOP 1 MAX(gsh2.CreatedDate)
                                                          FROM GasSupply gs2 
                                                              INNER JOIN GasSupplyHistory gsh2 
                                                                  ON gsh2.GasSupplyFK = gs2.GasSupplyID
                                                          WHERE gsh2.EventFK = 2 
                                                          AND gsh2.Notes LIKE '%Gas Supply Status (%'
                                                          AND gsh2.GasSupplyFK = gs.GasSupplyID
                                                          GROUP BY gsh2.GasSupplyFK
                                                      ),
                                                      (
                                                          SELECT TOP 1 MAX(gsh3.CreatedDate)
                                                          FROM GasSupplyHistory gsh3 
                                                              INNER JOIN  (
                                                                              SELECT gsh4.GasSupplyFK, MAX(gsh4.EventFK) AS [MaxEvent]
                                                                              FROM GasSupplyHistory gsh4
                                                                              WHERE gsh4.GasSupplyFK = gs.GasSupplyID
                                                                              GROUP BY gsh4.GasSupplyFK
                                                                              HAVING MAX(gsh4.EventFK) = 1
                                                                          ) dt 
                                                                  ON dt.GasSupplyFK = gsh3.GasSupplyFK
                                                      )
                                                  ),
                  [EAC] = gs.EstimatedAnnualConsumption,
                  [PreviousSupplier] = r.name,
                  [Branch] = b.name,
                  [LeadSource] = ls.name,
                  gs.UnitPrice,
                  gs.StandingCharge,
                  gs.WholesalePrice,
                  COALESCE(deal.weeknumber,DATEPART(ISOWK, con.[ContractDate])) AS [Week]
          FROM    acc.Account acc
              INNER JOIN [Profile] prf    
                  ON acc.ProfileFK = prf.ProfileID
              INNER JOIN [Contract] con       
                  ON acc.AccountID = con.AccountFK
              INNER JOIN [loacontract] lo
                  ON lo.ContractFK = con.ContractID
              LEFT join [LeadSource] ls
                  ON ls.LeadSourceID = con.LeadSourceFK
              INNER JOIN [ContractStatus] cs
                  ON cs.ContractStatusID = con.ContractStatusFK 
              INNER JOIN [ContractVersion] cv
                  ON cv.ContractVersionID = con.ContractVersionFK 
              INNER JOIN GasSupply gs
                  ON gs.ContractFK = con.ContractID
              INNER JOIN GasSupplyStatus gss
                  ON gss.GasSupplyStatusID = gs.GasSupplyStatusFK 
              LEFT JOIN Deal deal
                  ON deal.ContractFK = con.ContractID
              OUTER APPLY GetGMSReferenceNumbers(con.ContractID) gms
              LEFT JOIN Branch b
                  ON b.BranchID = deal.BranchFK
              LEFT JOIN DealBroker bro
                  ON  bro.DealFK = deal.DealID
                  AND bro.BrokerTypeFK = 36
              LEFT JOIN Person p
                  ON p.PersonID = bro.EmployeeFK
              LEFT JOIN Reseller r
                  ON r.ResellerID = gs.ResellerFK
              LEFT JOIN   (
                              SELECT  l.contractfk,
                                      MIN(l.contractenddate)[ContractEndDate]
                              FROM    CRM.[contract].LOAContract l
                              GROUP BY l.ContractFK       
                          ) loa
                  ON loa.ContractFK = con.ContractID
  
          WHERE acc.AccountID not in  (
                                          select AccountFK 
                                          from AccountOption 
                                          where OptionFK=9
                                      )
          AND  cast(gms.SiteRefNum as varchar) IS NULL
  </code></pre>