I am tracking equipment faults for A shift and B shift. I have 2 out 3 queries working: (1) count unique equipment faults for each shift (I have this working) (2) display a count of the equipment faults that are common to both shifts (I can't get this to work). I think PIVOT will make this work but I'm struggling with how to take the two data results from (1) and use it here in (2) for the PIVOT. (3) display a summary count of all unique equipment faults (I have this working). I appreciate any help and apologize if my formatting is not correct for this (my first) post.

Here is the sample data:

CREATE TABLE tbl_PrestartRdyScoreboard_test
    (   
    ndx int NOT NULL, 
    ShiftStart datetime null, 
    SheduledShift varchar(1) null, 
    PrestartMaintTeam varchar(1) null, 
    EmailCount smallint null,
    RdyStatus bit null,
    Equipment varchar(50) null, 
    CONSTRAINT ndx PRIMARY KEY (ndx)
    )

INSERT INTO tbl_PrestartRdyScoreboard_test
    (
    [ndx], [ShiftStart], [SheduledShift], [PrestartMaintTeam], [EmailCount], [RdyStatus], [Equipment]
     )
VALUES
    (1, '2023-10-10 06:15:00.000', 'A', 'A', 0, 0, 'Welder'),
    (2, '2023-10-10 17:30:00.000', 'B', 'B', 0, 0, 'Press'),
    (3, '2023-10-11 06:15:00.000', 'A', 'A', 0, 0, 'Drill'),
    (4, '2023-10-11 17:30:00.000', 'B', 'B', 0, 0, 'Drill'),
    (5, '2023-10-12 06:15:00.000', 'B', 'B', 0, 0, 'Drill'),
    (6, '2023-10-12 17:30:00.000', 'A', 'A', 0, 0, 'Saw')
    ;

The second query is the troublesome one because it shows 'Drill' as common in Shift A and Shift B instead of just indicating there is a count of 3 Drill incidents:

declare @start datetime = DATEADD(day, -30, getutcdate())
declare @end datetime = getutcdate()
--declare @SheduledShift1 VARCHAR(1)= 'D' -- days
--declare @SheduledShift2 VARCHAR(1)= 'A' -- afternoons
;

--Count Unique Equipment Events by Team. This is working
SELECT DISTINCT PrestartMaintTeam, Equipment, count(Equipment) as NumIncidents
FROM tbl_PrestartRdyScoreboard_test
WHERE ShiftStart > @start and ShiftStart < @end and PrestartMaintTeam <> 'U' and Equipment <> ''
GROUP BY PrestartMaintTeam, Equipment
order by PrestartMaintTeam, Equipment
;

--Count Unique Equipment Events by Team, only listing the ones that appear on both shifts. Not Working.
With 
t1 AS 
    (
    SELECT DISTINCT PrestartMaintTeam, Equipment, count(Equipment) as NumIncidents
    FROM tbl_PrestartRdyScoreboard_test
    WHERE ShiftStart > @start and ShiftStart < @end and PrestartMaintTeam = 'A' and Equipment <> ''
    GROUP BY PrestartMaintTeam, Equipment
),
t2 AS
    (
    SELECT DISTINCT PrestartMaintTeam, Equipment, count(Equipment) as NumIncidents
    FROM tbl_PrestartRdyScoreboard_test
    WHERE ShiftStart > @start and ShiftStart < @end and PrestartMaintTeam = 'B' and Equipment <> ''
    GROUP BY PrestartMaintTeam, Equipment
)
SELECT * FROM t1 INNER JOIN t2
ON t1.Equipment = t2.Equipment
ORDER BY t1.Equipment
;

--Count of each Equipment Event across both teams. This is working.
SELECT COUNT(Equipment) as EquipCount, [Equipment]
FROM [tbl_PrestartRdyScoreboard_test]
where ShiftStart > @start and ShiftStart < @end and PrestartMaintTeam <> 'U' and Equipment <> ''
group by [Equipment]
order by [Equipment] asc

Here is the output:

I would like to add up the resulting common equipment faults so all that is displayed is:

When I tried to apply a PIVOT to the result sets of (1) where I count by Shift A and also Shift B, I get an error stating there are duplicate column names (Equipment). The thing is, of course there are duplicate columns named Equipment because that is what I'm trying to find in common between both shifts.