| Author |
 Topic  |
|
|
stwp86
Starting Member
USA
41 Posts |
 Posted - 08/02/2012 : 23:51:46
|
I have the following queries . . . I was hoping someone would have an idea of how I can combine them, and make certain to keep the results intact. I basically just want to be able to have the average time number from the first query available in my second query's select statement. Thoughts?
First - Average baseline query:
SELECT Client_Mnemonic,AVG(CASE WHEN Seq <= 4 THEN [Discharge - Average Elapsed Time] ELSE NULL END) OVER (PARTITION BY Client_Mnemonic,Group_type) AS AvgTime,[Work Week],Group_type
FROM
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY Client_Mnemonic,Group_Type ORDER BY [Work Week]) AS Seq
FROM
(
SELECT Client_Mnemonic, round((avg(duration) *count(duration)/count(duration)),0) As "Discharge - Average Elapsed Time",
CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101) As "Work Week", Group_Type
FROM workflow_data
WHERE
client_mnemonic = 'ABC' and
(
definition = 'Notes - Review Clinical Notes' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Amb Summary - Review Amb Summary_v4' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'ED Summary - Review ED Summary_v4' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'ICU Summary - Review ICU Summary_v4' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Inpt Summary - Review Inpt Summary_v4' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Overview - Review Overview' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Review Chart - Review Review Chart' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Flowsheet - Review Flowsheet' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Problems and Dx - Review Problems and Dx' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Interactive View - Review Interactive View' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Documentation - Review Documentation' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Histories - Review Family History' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Histories - Review Past Medical History' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Histories - Review Procedure History' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Histories - Review Social History' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Orders - Review Orders' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Orders - Review Med List' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'MAR Summary - Review MAR Summary' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Health Maintenance - Review Health Maintenance' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Immunization Schedule - Review Immunization Schedule' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Growth Chart - Review Growth Chart' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Patient Information - Review Patient Demographics' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Patient Information - Review Visit List' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Patient Information - Review PPR Summary' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Generic View - Review Generic View' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Procedures and Diagnosis - Review Procedures and Diagnosis' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Med Profile - Review Med Profile' and datepart(dw, workflow_data.Start_Time) not in (1,7)
)
GROUP BY datepart(ww,workflow_data.start_time), Client_Mnemonic,
CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101), Group_Type
)q
)r
ORDER BY Group_Type,[Work Week];
Second - Total Elasped Time:
SELECT n.Client_mnemonic,round((([Total Elapsed Time (sec)]/WeeklyUserCount)/60.0),1) AS "Indirect Care - Total Elapsed Time Per Day" ,m.WeekMinDate as "Work Week", n.Group_Type
FROM
(
select sum([Distinct User Count by Day]) AS WeeklyUserCount,DATEADD(wk,DATEDIFF(wk,0,[Date]),0) AS weekMinDate, Group_Type
from
(
select count(distinct w.user_name) as "Distinct User Count by Day", convert(date, w.start_time) as "Date", Group_Type
from workflow_data as w
WHERE
client_mnemonic = 'ABC' and
(
w.definition = 'Patient Search - Patient Search Window' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Patient Lists - Patient List to Open Chart' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Home View - Schedule Viewer to Open Chart' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Sign Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Modify Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Accept Proposal-Refill Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Reject Proposal-Refill Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Accept Cosign Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Reject Cosign Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Endorse Results FYI' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Results FYI' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Endorse Results' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Results' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Respond to Message' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Delete Message' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - View Sent Items' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Create New Message-Reminder' and datepart(dw, w.Start_Time) not in (1,7)
)group by CONVERT(date, w.start_time), Group_Type
)t
GROUP BY DATEADD(wk,DATEDIFF(wk,0,[Date]),0), Group_Type
)m
INNER JOIN
(
SELECT
Client_Mnemonic, CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, w.Start_Time) - 5, w.Start_Time)),101) as WeekDate,
(count(w.User_Name)*avg(w.duration)) as "Total Elapsed Time (sec)", Group_Type
FROM workflow_data as w
WHERE
w.client_mnemonic = 'ABC' and
(
w.definition = 'Patient Search - Patient Search Window' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Patient Lists - Patient List to Open Chart' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Home View - Schedule Viewer to Open Chart' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Sign Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Modify Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Accept Proposal-Refill Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Reject Proposal-Refill Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Accept Cosign Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Reject Cosign Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Endorse Results FYI' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Results FYI' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Endorse Results' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Results' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Respond to Message' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Delete Message' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - View Sent Items' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Create New Message-Reminder' and datepart(dw, w.Start_Time) not in (1,7)
)GROUP BY client_mnemonic, CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101), Group_Type
)n
ON n.WeekDate = m.WeekMinDate and n.group_type = m.group_type --and n.Group_Type = 'Pilot' and m.Group_Type = 'Pilot'
ORDER BY n.group_type,n.client_mnemonic,m.WeekMinDate
As always thanks for the help! |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
|
|
stwp86
Starting Member
USA
41 Posts |
Posted - 08/03/2012 : 08:59:15
|
Hi Visakh . . .
I think at this point you know these queries far better than I do . . . I accidentally posted the incorrect query for the second one. Here are the correct ones:
Average Elapsed Time:
SELECT Client_Mnemonic, round((avg(duration) *count(duration)/count(duration)),1) As "Data Review - Average Elapsed Time",
CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101) as "Work Week", Group_Type
FROM workflow_data
WHERE
client_mnemonic = 'ABC' and
(
definition = 'Notes - Review Clinical Notes' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Amb Summary - Review Amb Summary_v4' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'ED Summary - Review ED Summary_v4' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'ICU Summary - Review ICU Summary_v4' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Inpt Summary - Review Inpt Summary_v4' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Overview - Review Overview' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Review Chart - Review Review Chart' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Flowsheet - Review Flowsheet' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Problems and Dx - Review Problems and Dx' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Interactive View - Review Interactive View' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Documentation - Review Documentation' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Histories - Review Family History' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Histories - Review Past Medical History' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Histories - Review Procedure History' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Histories - Review Social History' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Orders - Review Orders' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Orders - Review Med List' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'MAR Summary - Review MAR Summary' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Health Maintenance - Review Health Maintenance' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Immunization Schedule - Review Immunization Schedule' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Growth Chart - Review Growth Chart' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Patient Information - Review Patient Demographics' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Patient Information - Review Visit List' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Patient Information - Review PPR Summary' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Generic View - Review Generic View' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Procedures and Diagnosis - Review Procedures and Diagnosis' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Med Profile - Review Med Profile' and datepart(dw, workflow_data.Start_Time) not in (1,7)
)
GROUP BY datepart(ww,workflow_data.start_time), client_mnemonic,
CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101), Group_Type
ORDER BY client_mnemonic, group_type, datepart(ww,workflow_data.Start_Time);
Average Baseline:
SELECT Client_Mnemonic,AVG(CASE WHEN Seq <= 4 THEN [Discharge - Average Elapsed Time] ELSE NULL END) OVER (PARTITION BY Client_Mnemonic,Group_type) AS AvgTime,[Work Week],Group_type
FROM
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY Client_Mnemonic,Group_Type ORDER BY [Work Week]) AS Seq
FROM
(
SELECT Client_Mnemonic, round((avg(duration) *count(duration)/count(duration)),0) As "Discharge - Average Elapsed Time",
CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101) As "Work Week", Group_Type
FROM workflow_data
WHERE
client_mnemonic = 'MAYO_MN' and
(
definition = 'Notes - Review Clinical Notes' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Amb Summary - Review Amb Summary_v4' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'ED Summary - Review ED Summary_v4' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'ICU Summary - Review ICU Summary_v4' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Inpt Summary - Review Inpt Summary_v4' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Overview - Review Overview' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Review Chart - Review Review Chart' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Flowsheet - Review Flowsheet' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Problems and Dx - Review Problems and Dx' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Interactive View - Review Interactive View' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Documentation - Review Documentation' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Histories - Review Family History' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Histories - Review Past Medical History' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Histories - Review Procedure History' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Histories - Review Social History' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Orders - Review Orders' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Orders - Review Med List' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'MAR Summary - Review MAR Summary' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Health Maintenance - Review Health Maintenance' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Immunization Schedule - Review Immunization Schedule' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Growth Chart - Review Growth Chart' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Patient Information - Review Patient Demographics' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Patient Information - Review Visit List' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Patient Information - Review PPR Summary' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Generic View - Review Generic View' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Procedures and Diagnosis - Review Procedures and Diagnosis' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Med Profile - Review Med Profile' and datepart(dw, workflow_data.Start_Time) not in (1,7)
)
GROUP BY datepart(ww,workflow_data.start_time), Client_Mnemonic,
CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101), Group_Type
)q
)r
ORDER BY Group_Type,[Work Week];
That being said, this is a slightly different request. Basically, the reason for this request is that the reporting tool that I am using does not allow me to have multiple data sets attached to a single chart, and naturally these two queries go hand in hand with one another. So, I tried combining them myself, but when I did that I recieved the incorrect output (go figure :/). I relly just need to be able to grab the average baseline number in the other query. Desired output would look like this:
client_mnemonic |Avg Elap Time | Date | Group_type | Average Baseline
ABC 50.8 06/04/2012 Pilot 56.75
ABC 54.5 06/04/2012 Pilot 56.75
ABC 51.4 06/04/2012 Pilot 56.75
ABC 71.3 06/04/2012 Pilot 56.75
...
As Always, I appreciate the assistance!
T
|
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 08/03/2012 : 09:41:46
|
i think this is again asking for the same principle . you just need to join queries on common columns.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
stwp86
Starting Member
USA
41 Posts |
Posted - 08/03/2012 : 09:47:29
|
| I'll give it a go, thanks for the direction |
|
|
| |
Topic |
|
|
|
Combine Queries?
