Select t.client_mnemonic, t.[Total Time], t.[Work Week], t.Group_Type, i.BarType, t.Transactions
FROM
(
SELECT n.Client_mnemonic,n.Transactions, round((([Total Elapsed Time (sec)]/WeeklyUserCount)/60.0),2) AS "Total Time" ,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(varchar(11), w.start_time) as "Date", Group_Type 
from workflow_data as w
WHERE
Client_mnemonic = (@ClientMnemonicParameter) and (Group_type = (@GroupSlicerOne)) and 
(
convert(date, start_time) between (@BeginDate) and (@EndDate)
) and duration <=1800 and
(
w.definition = 'Patient Search - Patient Search to Open Chart' 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(varchar(11), 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,count(workflow_id) as "Transactions"  
FROM workflow_data as w
WHERE
Client_mnemonic = (@ClientMnemonicParameter) and (Group_type = (@GroupSlicerOne)) and 
(
convert(date, start_time) between (@BeginDate) and (@EndDate)
) and duration <=1800 and
(
w.definition = 'Patient Search - Patient Search to Open Chart' 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 
)t
inner join
(
Select [Work_Week], 
CASE WHEN Seq1 <= @BaselineDuration THEN 'Baseline' 
             ELSE 'Trending'
           END as "BarType"
FROM
(
SELECT CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101) as "Work_Week",
ROW_NUMBER() OVER (ORDER BY CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101)) AS Seq1
FROM workflow_data
where client_mnemonic = (@ClientMnemonicParameter) and group_type = @GroupSlicerOne
group by CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101) 
)m
)i
ON t.[Work Week] = i.[Work_Week]
ORDER BY group_type,client_mnemonic,t.[Work Week]

Select t.client_mnemonic, t.[Average Time], t.[Work Week], t.Group_Type, i.BarType, t.Transactions
FROM
(
select q.Client_Mnemonic, q.[Average Time], q.Group_Type, q.[Work Week], q.Transactions
from
(
SELECT Client_Mnemonic, count(workflow_id) as "Transactions", round((avg(duration) *count(duration)/count(duration)),0) As "Average 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 = (@ClientMnemonicParameter) and (Group_type = (@GroupSlicerOne)) and --or Group_Type = (@GroupSlicerTwo)) and 
(
convert(date, start_time) between (@BeginDate) and (@EndDate)
)and
duration <= 1200 and
(
definition = 'Meds Rec - Admission'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Meds Rec - Transfer'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Meds Rec - Discharge'  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
)t
inner join 
(
Select [Work_Week], 
CASE WHEN Seq1 <= @BaselineDuration THEN 'Baseline' 
             ELSE 'Trending'
           END as "BarType"
FROM
(
SELECT CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101) as "Work_Week",
ROW_NUMBER() OVER (ORDER BY CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101)) AS Seq1
FROM workflow_data
where client_mnemonic = (@ClientMnemonicParameter) and group_type = @GroupSlicerOne
group by CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101) 
)m
)i
ON t.[Work Week] = i.[Work_Week]
Order by group_type desc, client_mnemonic, t.[Work Week]