select C.ID as ClaimID
into #claims
from Claims C
join Users U
on U.ID = C.UserID
where C.ClaimStart > '2020-06-30'      -- Update start date here
and C.ClaimEnd < '2020-07-01'           -- Update end date here
order by U.LastName, U.FirstName


select C.ID as ClaimID, C.UserID as EmployeeNumber,
U.LastName, U.FirstName, S.Name as SpecialtyName,
R_S.[Hours] as S_Hours,
R_W.[Hours] as W_Hours,
R_T.[Hours] as T_Hours,
R_P.[Hours] as P_Hours,
(select sum(value) from Claim_Codes where claimid = C.ID) as TotalCMBSValue,
C.ClaimStart, C.ClaimEnd, datediff(mi, C.ClaimStart, C.ClaimEnd) / cast(60 as decimal(10,2)) as ClaimDurationHours

from #claims X
join Claims C
on C.ID = X.ClaimID
join Users U
on U.ID = C.UserID
left join Claim_Recall R_S
on R_S.ClaimID = C.ID
and R_S.RecallType = 'S'
left join Claim_Recall R_W
on R_W.ClaimID = C.ID
and R_W.RecallType = 'W'
left join Claim_Recall R_T
on R_T.ClaimID = C.ID
and R_T.RecallType = 'T'
left join Claim_Recall R_P
on R_P.ClaimID = C.ID
and R_P.RecallType = 'P'
--left join Claim_Codes CC
-- on CC.ClaimID = C.ID
left join Specialties S
on S.ID = U.SpecialtyID
order by U.LastName, U.FirstName, S.Name

select X.ClaimID, CO.Code, CC.Value as AmountPaid, CO.IsTheatre, CO.IsAssist, CO.Category, CO.[Group], CO.[SubGroup],CO.Rate as ListedFullRate, Co.Created as CodeRateStartDate, CO.[Description]
from #claims X
join Claim_Codes CC
on CC.ClaimID = X.ClaimID
join CMBSCodes CO
on CO.ID = CC.CMBSCodeID