Combine Queries? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combine Queries?

Author

Topic

stwp86
Starting Member

42 Posts

Posted - 2012-08-02 : 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

52326 Posts

Posted - 2012-08-03 : 00:15:18

didnt i provide you solution here?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=177342

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stwp86
Starting Member

42 Posts

Posted - 2012-08-03 : 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

52326 Posts

Posted - 2012-08-03 : 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

42 Posts

Posted - 2012-08-03 : 09:47:29

I'll give it a go, thanks for the direction

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources