Silly query

gistfile1.sql

select PossibleA.SystemName,
	   PossibleA.SystemDescription,
       PossibleA.SortOrder,

       TotalA.TotalA,
       TotalB.TotalB,

       PossibleA.Buildings as PossibleABuildings,
       PossibleA.EHT as PossibleAEHT,
       PossibleA.Electrical as PossibleAElectrical,
       PossibleA.Instrument as PossibleAInstrument,
       PossibleA.Mechanical as PossibleAMechanical,
       PossibleA.Piping as PossibleAPiping,
       
       CompleteA.Buildings as CompleteABuildings,
       CompleteA.EHT as CompleteAEHT,
       CompleteA.Electrical as CompleteAElectrical,
       CompleteA.Instrument as CompleteAInstrument,
       CompleteA.Mechanical as CompleteAMechanical,
       CompleteA.Piping as CompleteAPiping,
       
       PossibleA.Buildings - CompleteA.Buildings as RemainingABuildings,
       PossibleA.EHT - CompleteA.EHT as RemainingAEHT,
       PossibleA.Electrical - CompleteA.Electrical as RemainingAElectrical,
       PossibleA.Instrument - CompleteA.Instrument as RemainingAInstrument,
       PossibleA.Mechanical - CompleteA.Mechanical as RemainingAMechanical,
       PossibleA.Piping - CompleteA.Piping as RemainingAPiping,
       
       PossibleB.Buildings as PossibleBBuildings,
       PossibleB.EHT as PossibleBEHT,
       PossibleB.Electrical as PossibleBElectrical,
       PossibleB.Instrument as PossibleBInstrument,
       PossibleB.Mechanical as PossibleBMechanical,
       PossibleB.Piping as PossibleBPiping,
       
       CompleteB.Buildings as CompleteBBuildings,
       CompleteB.EHT as CompleteBEHT,
       CompleteB.Electrical as CompleteBElectrical,
       CompleteB.Instrument as CompleteBInstrument,
       CompleteB.Mechanical as CompleteBMechanical,
       CompleteB.Piping as CompleteBPiping,
       
       PossibleB.Buildings - CompleteB.Buildings as RemainingBBuildings,
       PossibleB.EHT - CompleteB.EHT as RemainingBEHT,
       PossibleB.Electrical - CompleteB.Electrical as RemainingBElectrical,
       PossibleB.Instrument - CompleteB.Instrument as RemainingBInstrument,
       PossibleB.Mechanical - CompleteB.Mechanical as RemainingBMechanical,
       PossibleB.Piping - CompleteB.Piping as RemainingBPiping
       
from 
(select systemname, 
        discipline,
        SystemDescription,
        SortOrder, 
        apossible 
   from vwchecksheetstatus) as vwc
        pivot( avg(APossible) for Discipline IN ([EHT], 
                                         [Instrument], 
                                         [Mechanical], 
                                         [Electrical], 
                                         [Buildings],
                                         [Piping])) as PossibleA

full outer join 

(select systemname, 
        discipline, 
        acomplete 
   from vwchecksheetstatus) as vwc
        pivot( avg(acomplete) for Discipline IN ([EHT], 
                                         [Instrument], 
                                         [Mechanical], 
                                         [Electrical], 
                                         [Buildings],
                                         [Piping])) as CompleteA

on PossibleA.systemname = CompleteA.SystemName

full outer join 

(select systemname, 
        discipline, 
        bcomplete 
   from vwchecksheetstatus) as vwc
        pivot( avg(bcomplete) for Discipline IN ([EHT], 
                                         [Instrument], 
                                         [Mechanical], 
                                         [Electrical], 
                                         [Buildings],
                                         [Piping])) as CompleteB
on PossibleA.systemname = CompleteB.SystemName

full outer join 

(select systemname, 
        discipline, 
        bpossible 
   from vwchecksheetstatus) as vwc
        pivot( avg(bpossible) for Discipline IN ([EHT], 
                                         [Instrument], 
                                         [Mechanical], 
                                         [Electrical], 
                                         [Buildings],
                                         [Piping])) as PossibleB
on PossibleA.systemname = PossibleB.SystemName              

full outer join 

(select s.Name as systemname,
		COUNT(*) as TotalA
	from tblTags t 
	 inner join tblTagTypeChecksheets tt on t.TagTypeID = tt.TypeID
	 inner join tblSystems s on t.SystemID = s.ID
	 inner join tblDiscipline d on t.DisciplineID = d.ID
	 inner join tblChecksheets c on tt.ChecksheetID = c.ID
	WHERE T.StatusID = 1
	  AND c.Type = 1
	 group by s.Name) AS TotalA

on PossibleA.systemname = TotalA.systemname
	 
full outer join 

(select s.Name as systemname,
		COUNT(*) as TotalB
	from tblTags t 
	 inner join tblTagTypeChecksheets tt on t.TagTypeID = tt.TypeID
	 inner join tblSystems s on t.SystemID = s.ID
	 inner join tblDiscipline d on t.DisciplineID = d.ID
	 inner join tblChecksheets c on tt.ChecksheetID = c.ID
	WHERE T.StatusID = 1
	  AND c.Type = 2
	 group by s.Name) AS TotalB	 
	 
on PossibleA.systemname = TotalB.systemname