Adding a calculated column from a pivot query - Or selecting from a pivot query?

I have been asked to do an analysis regarding our employee hours. Our company has an allowable number of hours each week (which includes overtime) that changes throughout the year. How would I determine both the difference each week and the number of times an employee has gone over the allowable amount?

Please note, that this is actually being used to readjust the allowable hours and not for any disciplinary measures. If a number of employees worked more than the allowable hours in a particular week, the allowable hours will likely be increased for that week.

I have gone so far as to create this query, but I am by no means an expert on pivot queries.

SELECT *
FROM
(SELECT PER_MASTER.EmployeeId, ARC_EARNINGS.EarningsCd, ARC_EARNINGS.CurHoursAmt, VAL_PAYCALENDAR.CheckDate, allowable2.Hours
FROM PER_MASTER 
JOIN ARC_EARNINGS on ARC_EARNINGS.EmployeeId=PER_MASTER.EmployeeId
JOIN VAL_PAYCALENDAR on VAL_PAYCALENDAR.RowId=ARC_EARNINGS.PayCalendarId
JOIN allowable2 on allowable2.Checkdate2=VAL_PAYCALENDAR.CheckDate
WHERE VAL_PAYCALENDAR.CheckDate between '1/5/22' and '1/5/23'
and PER_MASTER.EmployeeID=@Employee
) t
PIVOT(sum(t.CurHoursAmt)
FOR t.EarningsCd in ([Regular],[Overtime])
)p

EarningsCd is the type of hours (regular, overtime, vacation, etc).
CurHoursAmt is the number of hours for each type.
I need to use the table VAL_PAYCALENDAR for the paycheck date. The table allowable2 is a simple table with the paycheck date and the number of total allowable hours. Don't worry about the @Employee parameter.

I am doing this in SSRS along with other info. This query isn't something I can simply paste into excel and work from there.

Any help with this would be most appreciated!

Calculate the total number of hours worked each week (including regular and overtime hours) for each employee.
Compare the total number of hours worked to the allowable number of hours for each week.
Calculate the difference between the total number of hours worked and the allowable number of hours for each week.
Count the number of times the employee has gone over the allowable number of hours for each week.
Here is an example query that you could use:

SELECT 
    PER_MASTER.EmployeeId, 
    VAL_PAYCALENDAR.CheckDate,
    SUM(CASE WHEN ARC_EARNINGS.EarningsCd = 'Regular' THEN ARC_EARNINGS.CurHoursAmt ELSE 0 END) AS RegularHours,
    SUM(CASE WHEN ARC_EARNINGS.EarningsCd = 'Overtime' THEN ARC_EARNINGS.CurHoursAmt ELSE 0 END) AS OvertimeHours,
    allowable2.Hours AS AllowableHours,
    SUM(ARC_EARNINGS.CurHoursAmt) AS TotalHours,
    CASE 
        WHEN SUM(ARC_EARNINGS.CurHoursAmt) > allowable2.Hours THEN SUM(ARC_EARNINGS.CurHoursAmt) - allowable2.Hours 
        ELSE 0 
    END AS HoursOverAllowable,
    CASE 
        WHEN SUM(ARC_EARNINGS.CurHoursAmt) > allowable2.Hours THEN 1 
        ELSE 0 
    END AS WeeksOverAllowable
FROM 
    PER_MASTER 
    JOIN ARC_EARNINGS ON ARC_EARNINGS.EmployeeId = PER_MASTER.EmployeeId
    JOIN VAL_PAYCALENDAR ON VAL_PAYCALENDAR.RowId = ARC_EARNINGS.PayCalendarId
    JOIN allowable2 ON allowable2.Checkdate2 = VAL_PAYCALENDAR.CheckDate
WHERE 
    VAL_PAYCALENDAR.CheckDate BETWEEN '1/5/22' AND '1/5/23'
GROUP BY 
    PER_MASTER.EmployeeId, 
    VAL_PAYCALENDAR.CheckDate,
    allowable2.Hours