Reputation: 4189
This is an extension of this query here. I have a result set that currently is correct and looks like this:
MD Name | # PTS | Med Staff | AVG LOS | AVG Bench LOS | AVG DIFF
MCP | 4 | Ortho SX | 8.000000| 2.650000 | 5.350000
I want to add onto this result, the total patients seen for the department and the same averages but for the departments as a whole. I was able to get the total patients per department to sum up correctly by doing the following as another column added onto the original query above.
(SELECT
COUNT(DISTINCT V.PT_ID)
FROM smsdss.pract_dim_v p
JOIN smsmir.vst_rpt v
on v.adm_pract_no = p.src_pract_no
WHERE pv.med_staff_dept = p.med_staff_dept
AND v.adm_dtime BETWEEN @STARTDATE AND @ENDATE
AND v.vst_type_cd = 'I'
AND p.spclty_desc != 'NO DESCRIPTION'
--AND pv.spclty_desc NOT LIKE 'HOSPITALIST%'
AND v.drg_std_days_stay IS NOT NULL
AND p.pract_rpt_name != '?'
AND p.orgz_cd = 's0x0'
AND p.med_staff_dept IN (
'INTERNAL MEDICINE',
'FAMILY PRACTICE',
'SURGERY'
)
) AS '# PTS For Dept'
I manually checked the sums, they are correct. I know want to add AVG LOS, AVG BENCH LOS and AVG DIFF over p.med_staff_dept. This is where I start getting wack-a-doo (technical term) results.
Here is the part where I try to get the AVG LOS per department:
(SELECT
AVG(v.len_of_stay)
FROM smsdss.pract_dim_v p
JOIN smsmir.vst_rpt v
ON v.adm_pract_no = p.src_pract_no
WHERE pv.med_staff_dept = p.med_staff_dept
AND v.adm_dtime BETWEEN @STARTDATE AND @ENDATE
AND v.vst_type_cd = 'I'
AND p.spclty_desc != 'NO DESCRIPTION'
--AND pv.spclty_desc NOT LIKE 'HOSPITALIST%'
AND v.drg_std_days_stay IS NOT NULL
AND p.pract_rpt_name != '?'
AND p.orgz_cd = 's0x0'
AND p.med_staff_dept IN (
'INTERNAL MEDICINE',
'FAMILY PRACTICE',
'SURGERY'
)
) AS 'DEPT ALOS'
Two of my groups get the right numbers when checked against excel, but the middle group of rows is vastly incorrect, like so:
# PTS FOR DEPT | DEPT ALOS
330 | 5.284848 <-- SAME IN EXCEL
737 | 4.952510 <-- EXCEL SHOWS 196 AND 6.214285
111 | 3.072072 <-- SAME IN EXCEL
This is my FROM Clause for the Query in total:
FROM smsmir.vst_rpt vr
JOIN smsdss.pract_dim_v pv
ON vr.adm_pract_no = pv.src_pract_no
WHERE vr.adm_dtime BETWEEN @STARTDATE AND @ENDATE
AND vr.vst_type_cd = 'I'
AND pv.spclty_desc != 'NO DESCRIPTION'
--AND pv.spclty_desc NOT LIKE 'HOSPITALIST%'
AND vr.drg_std_days_stay IS NOT NULL
AND pv.pract_rpt_name != '?'
AND pv.orgz_cd = 's0x0'
AND pv.med_staff_dept IN (
'INTERNAL MEDICINE',
'FAMILY PRACTICE',
'SURGERY'
)
GROUP BY pv.pract_rpt_name, pv.med_staff_dept, pv.spclty_desc
ORDER BY pv.med_staff_dept, AVG(vr.len_of_stay - vr.drg_std_days_stay)DESC
Desired final output:
MD NAME | # PTS | MED STAFF | AVG LOS | AVG BENCH | AVG DIFF | DEPT TOT | AVG LOS | AVG BNCH | AVG DIFF
MCP | 4 | Ortho SX | 8.000000| 2.650000 | 5.350000 | 300 | 4.990000| 4.500000 | 0.490000
Full code:
DECLARE @STARTDATE DATETIME
DECLARE @ENDATE DATETIME
SET @STARTDATE = '2013-05-01'
SET @ENDATE = '2013-05-31'
SELECT DISTINCT pv.pract_rpt_name AS 'PHYSICIAN'
, COUNT(DISTINCT vr.pt_id) AS '# PTS'
--, pv.spclty_desc AS 'SPECIALTY'
, pv.med_staff_dept AS 'MED STAFF'
, AVG(vr.len_of_stay) AS 'AVG LOS'
, AVG(vr.drg_std_days_stay) AS 'AVG DRG LOS BENCH'
, AVG(vr.len_of_stay - vr.drg_std_days_stay) AS 'OPPORTUNITY'
, (SELECT
COUNT(DISTINCT V.PT_ID)
FROM smsdss.pract_dim_v p
JOIN smsmir.vst_rpt v
on v.adm_pract_no = p.src_pract_no
WHERE pv.med_staff_dept = p.med_staff_dept
AND v.adm_dtime BETWEEN @STARTDATE AND @ENDATE
AND v.vst_type_cd = 'I'
AND p.spclty_desc != 'NO DESCRIPTION'
AND pv.spclty_desc NOT LIKE 'HOSPITALIST%'
AND v.drg_std_days_stay IS NOT NULL
AND p.pract_rpt_name != '?'
AND p.orgz_cd = 's0x0'
AND p.med_staff_dept IN (
'INTERNAL MEDICINE',
'FAMILY PRACTICE',
'SURGERY'
)
) AS '# PTS For Dept'
-- currently not working properly
, (SELECT
AVG(V.len_of_stay)
FROM smsmir.vst_rpt v
JOIN smsdss.pract_dim_v p
ON v.adm_pract_no = p.src_pract_no
WHERE pv.med_staff_dept = p.med_staff_dept
AND v.adm_dtime BETWEEN @STARTDATE AND @ENDATE
AND v.vst_type_cd = 'I'
AND p.spclty_desc != 'NO DESCRIPTION'
AND pv.spclty_desc NOT LIKE 'HOSPITALIST%'
AND v.drg_std_days_stay IS NOT NULL
AND p.pract_rpt_name != '?'
AND p.orgz_cd = 's0x0'
AND p.med_staff_dept IN (
'INTERNAL MEDICINE',
'FAMILY PRACTICE',
'SURGERY'
)
) AS 'DEPT ALOS'
FROM smsmir.vst_rpt vr
JOIN smsdss.pract_dim_v pv
ON vr.adm_pract_no = pv.src_pract_no
WHERE vr.adm_dtime BETWEEN @STARTDATE AND @ENDATE
AND vr.vst_type_cd = 'I'
AND pv.spclty_desc != 'NO DESCRIPTION'
AND pv.spclty_desc NOT LIKE 'HOSPITALIST%'
AND vr.drg_std_days_stay IS NOT NULL
AND pv.pract_rpt_name != '?'
AND pv.orgz_cd = 's0x0'
AND pv.med_staff_dept IN (
'INTERNAL MEDICINE',
'FAMILY PRACTICE',
'SURGERY'
)
GROUP BY pv.pract_rpt_name, pv.med_staff_dept, pv.spclty_desc
ORDER BY pv.med_staff_dept, AVG(vr.len_of_stay - vr.drg_std_days_stay)DESC
None of the Internal Medicine numbers are coming out correctly.
Upvotes: 1
Views: 176
Reputation: 4189
The problem I found is right here:
, (SELECT
COUNT(DISTINCT V.PT_ID)
FROM smsdss.pract_dim_v p
JOIN smsmir.vst_rpt v
on v.adm_pract_no = p.src_pract_no
WHERE pv.med_staff_dept = p.med_staff_dept
AND v.adm_dtime BETWEEN @STARTDATE AND @ENDATE
AND v.vst_type_cd = 'I'
AND p.spclty_desc != 'NO DESCRIPTION'
AND pv.spclty_desc NOT LIKE 'HOSPITALIST%' <-- should be p.spclty not pv.
AND v.drg_std_days_stay IS NOT NULL
AND p.pract_rpt_name != '?'
AND p.orgz_cd = 's0x0'
AND p.med_staff_dept IN (
'INTERNAL MEDICINE',
'FAMILY PRACTICE',
'SURGERY'
)
) AS '# PTS For Dept'
By changing the pv.spclty_desc to p.spclty_desc in both column select statements the problem was fixed. However, this does not tell me why in the statement only Internal Medicinie failed and not the other two.
Upvotes: 1