CHAPTER 2 REPORT AUTHORING: DESIGNING EFFICIENT QUERIES- Total service count for patient by specialty
- Diagnosis of the patient
- Estimated cost
- Dates of services
Listing 2-3 shows the query to produce this desired output from the healthcare application. You can
find the code for this query in the code download file EmployeeServices.sql in the SQL Queries folder.
Listing 2-3. Employee Cost Query for Healthcare Database
SELECT
Trx.PatID
, RTRIM(RTRIM(Patient.LastName) + ', ' + RTRIM(Patient.FirstName)) AS [Patient Name]
, Employee.EmployeelD
, RTRIM(RTRIM(Employee.LastName) + ', ' + RTRIM(Employee.FirstName)) AS [Employee
Name]
, ServicesLogCtgry.Service AS [Service Type]
, SUM(Chargelnfo.Cost) AS [Estimated Cost]
, COUNT(Trx.ServicesTbllD) AS Visit_Count
, Diag.Dscr AS Diagnosis
, DATENAME(mm, Trx.ChargeServiceStartDate) AS [Month]
, DATEPART(yy, Trx.ChargeServiceStartDate) AS [Year]
, Branch.BranchName AS Branch
FROM
Trx
JOIN Chargelnfo ON Trx.ChargelnfoID = Chargelnfo.ChargelnfoID
JOIN Patient ON Trx.PatID = Patient.PatID
JOIN Services ON Trx.ServicesTbllD = Services.ServicesTbllD
JOIN ServicesLogCtgry ON Services.ServicesLogCtgrylD =
ServicesLogCtgry.ServicesLogCtgryID
JOIN Employee ON Chargelnfo.EmployeeTbllD = Employee.EmployeeTbllD
JOIN Diag ON Chargelnfo.DiagTbllD = Diag.DiagTbllD
JOIN Branch ON TRX.BranchID = Branch.BranchID
WHERE
(Trx.TrxTypelD = 1) AND (Services.ServiceTypelD = 'v')
GROUP BY
ServicesLogCtgry.Service
, Diag.Dscr
, Trx.PatID
, RTRIM(RTRIM(Patient.LastName) + ', ' + RTRIM(Patient.FirstName))
, RTRIM(RTRIM(Employee.LastName) + ', ' + RTRIM(Employee.FirstName))
, Employee.EmployeelD
, DATENAME(mm, Trx.ChargeServiceStartDate)
, DATEPART(yy, Trx.ChargeServiceStartDate)
, Branch.BranchName
ORDER BY
Trx.PatID
The alias names identified with AS in the SELECT clause of the query should serve as pointers to the
data that answers the requirements of the report request. Again, knowing the schema of the database