Query Not Pulling All Data - 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

Query Not Pulling All Data

Author

Topic

stwp86
Starting Member

42 Posts

Posted - 2012-07-31 : 12:37:13

Slightly lost here . . .

Why when I try to restrict the results of the following query to the values change so drastically? Here is a copy of the query that I worked on with some help from a SQLTeam forum member:

/* Indirect Care Workflow Numbers */

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,MIN([Date]) 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
	(
	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.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.client_mnemonic,m.WeekMinDate

This query results in the following data being displayed:

Client | Total Elapsed Time Per Day | Work Week | Group_Type
ABC 10.2 6/18/2012 Control
ABC 3.9 6/18/2012 Pilot
ABC 23.1 6/25/2012 Control
ABC 19.2 6/25/2012 Pilot
ABC 18.6 7/2/2012 Pilot
ABC 22.7 7/2/2012 Control
ABC 12.5 7/9/2012 Pilot
ABC 28.8 7/9/2012 Control
ABC 9.8 7/16/2012 Pilot
ABC 28.4 7/16/2012 Control
ABC 12.4 7/23/2012 Pilot
ABC 24.1 7/23/2012 Control
DEF 1.6 6/18/2012 Pilot
DEF 0.3 6/18/2012 Control
DEF 1.5 6/25/2012 Control
DEF 10.9 6/25/2012 Pilot
DEF 4.4 7/2/2012 Pilot
DEF 0.8 7/2/2012 Control
DEF 5.4 7/9/2012 Pilot
DEF 1.3 7/9/2012 Control
DEF 15.7 7/16/2012 Pilot
DEF 3.2 7/16/2012 Control
DEF 1.1 7/23/2012 Control
DEF 5.2 7/23/2012 Pilot
HIJ 20 6/11/2012 Pilot
HIJ 36.5 6/11/2012 Control
HIJ 18.3 6/18/2012 Pilot
HIJ 16 6/18/2012 Control
HIJ 7.2 6/25/2012 Control
HIJ 9.6 7/2/2012 Control
HIJ 2.4 7/2/2012 Pilot
HIJ 14.2 7/9/2012 Pilot
HIJ 6.9 7/9/2012 Control
HIJ 8.1 7/16/2012 Pilot
HIJ 6.7 7/16/2012 Control
HIJ 9.9 7/23/2012 Control
HIJ 12.5 7/23/2012 Pilot

But when I isolate client ABC this way:

/* Indirect Care Workflow Numbers */

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,MIN([Date]) 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
	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 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.client_mnemonic,m.WeekMinDate

I recieve this output from the above restricted query:

Client| Total Elapsed Time Per Day |Work Week | Group_Type
ABC 30.7 2012-06-25 Control
ABC 31.4 2012-06-25 Pilot
ABC 31.5 2012-07-02 Control
ABC 46.6 2012-07-02 Pilot
ABC 37 2012-07-09 Pilot
ABC 39 2012-07-09 Control
ABC 28.3 2012-07-16 Pilot
ABC 38.1 2012-07-16 Control
ABC 32.8 2012-07-23 Control
ABC 36 2012-07-23 Pilot

Notice also that the week of 6-18 is not displaying in the second query, however there is definately data that falls within that week (I checked and it also returns in the first one).

I just want to know if the first query is correctly pulling the data based. . .

Any help is greatly appreciated.

Thanks!

T

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-31 : 12:44:52

in the first query the first subquery is not by client - in the second it is.
To get the same result try removing the client filiter in the first subquery of the second query.

Alternatively you might need to group by client in the first query first subquery and join on that with the second subquery. Depends what result you want.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

stwp86
Starting Member

42 Posts

Posted - 2012-07-31 : 12:50:10

Not sure I follow?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-31 : 13:13:18

The first query gets the distinct user count for all users - the second only for client ABC.
If you change the second query to this it shoould give the same result for the client - depends on whether that is what you want. You have to decide which is correct. If the second query is correct then you need to group by the client in the first subquery and use that as part of the join

/* Indirect Care Workflow Numbers */

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,MIN([Date]) 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
(
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.client_mnemonic,m.WeekMinDate

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 13:16:54

The reason it works differently is because in the second case you'll return only ABC's records from second query and then joins dates to it. so any date values which doesnot have records for ABC client will be missed.
I'm not sure what exactly you're looking at so if you can explain that then we might be able to suggest best suggestion for that.

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

stwp86
Starting Member

42 Posts

Posted - 2012-07-31 : 13:45:13

This is all individual workflows that clients complete each day. We are able to capture them and I am working on putting meaningful numbers around them.

So I just did a bit of manual digging...the second query (restricted to client ABC) returns the correct values for the workflows. Except it is excluding the week of 6/18, which there is data for, seen below:

Workflow | Date
Home View - Schedule Viewer to Open Chart 6/21/2012
Patient List - Patient List to Open Chart 6/22/2012
Message Center - Accept Cosign Orders 6/22/2012
Message Center - Sign Document.workflow 6/22/2012
Message Center - Sign Document.workflow 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Message Center - Respond to Message 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Message Center - Sign Document.workflow 6/22/2012
Message Center - Sign Document.workflow 6/22/2012
Message Center - Respond to Message 6/22/2012
Message Center - Sign Document.workflow 6/22/2012
Message Center - Sign Document.workflow 6/22/2012
Message Center - Sign Document.workflow 6/22/2012
Message Center - Respond to Message 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Message Center - Sign Document.workflow 6/22/2012
Message Center - Sign Document.workflow 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Message Center - Sign Document.workflow 6/22/2012
Message Center - Sign Document.workflow 6/22/2012
Message Center - Sign Document.workflow 6/22/2012
Message Center - Sign Document.workflow 6/22/2012
Message Center - Accept Cosign Orders 6/22/2012
Message Center - Respond to Message 6/22/2012
Message Center - Accept Cosign Orders 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Message Center - Respond to Message 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Message Center - Respond to Message 6/22/2012
Message Center - Sign Document.workflow 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Message Center - Sign Document.workflow 6/22/2012
Patient List - Patient List to Open Chart 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Message Center - Respond to Message 6/22/2012
Message Center - Sign Document.workflow 6/22/2012
Message Center - Respond to Message 6/22/2012
Message Center - Sign Document.workflow 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Message Center - Respond to Message 6/22/2012
Message Center - Endorse Results 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Message Center - Respond to Message 6/22/2012
Message Center - Respond to Message 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Message Center - Respond to Message 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Message Center - Respond to Message 6/22/2012
Message Center - Respond to Message 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Message Center - Respond to Message 6/22/2012
Message Center - Respond to Message 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Patient List - Patient List to Open Chart 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Message Center - Accept Cosign Orders 6/22/2012
Message Center - Respond to Message 6/22/2012
Message Center - Respond to Message 6/22/2012
Message Center - Respond to Message 6/22/2012
Message Center - Respond to Message 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Patient Search - Patient Search Window 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012
Home View - Schedule Viewer to Open Chart 6/22/2012

So the question is, how can I get this to pick up the data from the 21st and 22nd and complete the calculations similar to the other dates (it rolls up and only displays the monday of that week)?

Desired outcome should be like this:

Client | Total Elapsed Time Per Day | Work Week | Group_Type
ABC | 27.2 | 6/18/2012 | Pilot
ABC | 31.4 | 6/25/2012 | Pilot
ABC | 46.6 | 7/2/2012 | Pilot
ABC | 37.0 | 7/9/2012 | Pilot
ABC | 28.3 | 7/16/2012 | Pilot
...

If I am not making any sense please let me know . . .

thanks!

T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 14:08:12

sorry i didnt understand. I cant see any records for 6/18 in first query so obviously you wont get any records for 6/18 in result as you're joining to it.

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

stwp86
Starting Member

42 Posts

Posted - 2012-07-31 : 14:22:35

Well that is the thing, I may not have data for the date of 6/18, but I have data that falls within that week 6/18-6/22. So for those data points that fall on 6/21 and 6/22, I would like to aggregate those and display that number with 6/18 as the date (the monday of that week)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 14:56:18

and this needs to be repeated for every date in first query?

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

stwp86
Starting Member

42 Posts

Posted - 2012-07-31 : 22:15:16

I need to get the aggregate numbers for the week and then display them as the monday of that week. I would like the data results to show like this:

Client | Total Elapsed Time Per Day | Work Week | Group_Type
ABC | 27.2 | 6/18/2012 | Pilot
ABC | 31.4 | 6/25/2012 | Pilot
ABC | 46.6 | 7/2/2012 | Pilot
ABC | 37.0 | 7/9/2012 | Pilot
ABC | 28.3 | 7/16/2012 | Pilot

So if any results occur during the 5 day work week they will be run through the calculation (query 2 calculates the correct numbers, but leaves out the first week) and displayed as the above is.

stwp86
Starting Member

42 Posts

Posted - 2012-07-31 : 22:16:10

sorry, forgot to say thanks for the help . . . this stuff is probably confusing (what i need and why)

I appreciate the assistance.

T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 22:56:53

sounds like this


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
(
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.client_mnemonic,m.WeekMinDate

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

stwp86
Starting Member

42 Posts

Posted - 2012-08-01 : 12:58:10

Adding the client_mnemonic to the where clause of the sub query actually provided me with the correct information, everything is checking out and I am getting all dates.

thanks, so much for the help again!

Quite possibly the best forum I have ever been apart of. . .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-01 : 13:02:35

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources