Update 07/07/2021: The audit extract report can now be done by the user via the UI. (System Page > Export Claims)


This is to extract claim and claim detail data out of Oncall Suite / eVMS system. 

  1. Run the below query on the server vs MSSQL Management Studio
  2. This will produces two results and save the data (with header) to the XLSX file with two sheets (Claim and Detail respectively)
  3. Upload the file to the OnCallSuite IIS folder on Bhalivateh00 server for customer to download
  4. Notify customer when complete

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