Average of first 4 weeks of data

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

Average of first 4 weeks of data

Author

Topic

stwp86
Starting Member

42 Posts

Posted - 2012-07-28 : 10:06:12

Hey Everyone . . .

I have had great success with this forum, so I am returning with a new question. I have a set of data that has individual dates and duration by our clients. What I am looking to do is retrieve the average of those for weeks of data. We are currently trying to automate an excel process. The excel data would look like this:

Client | ElapTime | Date
ABC | 95.3 | 6/25/2012
ABC | 94.1 | 7/02/2012
ABC | 178.2 | 7/09/2012
ABC | 151.4 | 7/16/2012

Then we would average the Elapsed Time to get: 129.8

What I would like to do is get SQL output to display as such:

Client | AvgTime | Date
ABC | 129.8 | 6/25/2012
ABC | 129.8 | 7/02/2012
ABC | 129.8 | 7/09/2012
ABC | 129.8 | 7/16/2012

And then countinue to return that 4 week avg for every subsequent week there after. Also one of the tricky parts is that we would want to display the date as the Monday of the week that the item fell in. We are doing that elsewhere with the following bit:
CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101)

The reason is because we are using it as a baseline average line for our trending data.

Here are the table column names:

Client_Mnemonic
Duration (time for individual items by day)
Date (these are individual dates.

I am not great with SQL, so any help would be greatly appreciated, and please let me know if I need to clarify anything.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-28 : 12:49:48

you can do like this


SELECT Client,AVG(ElapTime) OVER (PARTITION BY Client,((Rn-1)/4)) AS AvgTime,Date
FROM
(
SELECT Client,ElapTime,Date,
ROW_NUMBER() OVER (PARTITION BY Client ORDER BY Date) AS Rn
FROM Time
)t

for getting Monday of week another way is

DATEDD(dd,(DATEDIFF(dd,0,Start_Time)/7)*7,0)

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

stwp86
Starting Member

42 Posts

Posted - 2012-07-28 : 19:33:04

So I am trying to adapt your query to my table and have come up with this:

SELECT t.Client_mnemonic,AVG(t.duration) OVER (PARTITION BY t.Client_mnemonic,((Rn-1)/4)) AS AvgTime,convert(date, t.start_time)
FROM workflow_data as a,
(
SELECT Client_mnemonic,duration,start_time,
ROW_NUMBER() OVER (PARTITION BY client_mnemonic ORDER BY start_time) AS Rn
FROM workflow_data
)t
WHERE
definition = 'Depart Process - Sign and Print Pat Ed' and datepart(dw, a.Start_Time) not in (1,7) or
definition = 'Depart Process - Save' and datepart(dw, a.Start_Time) not in (1,7) or
definition = 'Discharge Summary_v4 - Review and Sign' and datepart(dw, a.Start_Time) not in (1,7)

It's not quite working properly. . . Obviously I screwed something up

This is returning an average number for every row of data in the table.

What I am looking to do is group it up by group_type (control or pilot) and display only the monday of that week and the average elapsed time field should always be the same, which would be the average of the first for weeks of data for that client/group_type. Because I am trying to draw a straight line with this i need it to repeat that same average every week.

thoughts?

thanks!

T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-28 : 23:04:05

hmm...i cant get you unless you post some data to show exact scenario. could you post some with output?

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

stwp86
Starting Member

42 Posts

Posted - 2012-07-29 : 17:27:43

Here is the output from the query (It continues on like this until it hits every date and row in the table):

Client_mnemonic AvgTime (No column name)
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 12.63025 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25
ABC 138.2405 2012-06-25

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-29 : 20:06:14

do you mean this then?


SELECT Client,AVG(ElapTime) AS AvgTime,MIN(Date) AS [Date]
FROM
(
SELECT Client,ElapTime,Date,
ROW_NUMBER() OVER (PARTITION BY Client ORDER BY Date) AS Rn
FROM Time
)t
GROUP BY Client,(Rn-1)/4

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

stwp86
Starting Member

42 Posts

Posted - 2012-07-30 : 09:28:27

That's not quite it either, reading back through my posts let me try to clarify.

So, there are two flavors of this (you helped me with this one earlier):

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
ORDER BY n.client_mnemonic,m.WeekMinDate

And the other version looks like this:

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
definition = 'Depart Process - Sign and Print Pat Ed'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Depart Process - Save'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Discharge Summary_v4 - Review and Sign'  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 datepart(ww,workflow_data.Start_Time), Group_Type;

The goal of these two queries is to get elapsed time averages from our data by client and by group. This is the output from the queries, respectively:

First:
ABC 7.8 2012-06-25 Control
ABC 6.9 2012-06-25 Pilot
ABC 20.6 2012-07-02 Control
ABC 20.6 2012-07-02 Pilot
ABC 24.7 2012-07-09 Control
ABC 4.5 2012-07-09 Pilot
ABC 17.8 2012-07-16 Control
ABC 10.4 2012-07-16 Pilot

Second:
ABC 142 06/25/2012 Control
ABC 375 06/25/2012 Pilot
ABC 172 07/02/2012 Control
ABC 91 07/02/2012 Pilot
ABC 182 07/09/2012 Control
ABC 617 07/09/2012 Pilot
ABC 131 07/16/2012 Control
ABC 481 07/16/2012 Pilot

So, what am I looking for from this thread? For each of our clients we must calculate a 4 week baseline average (this is based only on the first 4 weeks of data we have, and as you can see those weeks must be aggregated by the monday of that week (ie 6/25, 7/02, or whatever that monday of the first 4 weeks of data are). The tricky part, I think, is this . . . I need to display that 4 week average for all monday aggregated dates because I need to be able to draw a straight line on a report builder graph. So for the above items the desired outcome of this new query would look like this:

First:
ABC 17.725 2012-06-25 Control
ABC 10.6 2012-06-25 Pilot
ABC 17.725 2012-07-02 Control
ABC 10.6 2012-07-02 Pilot
ABC 17.725 2012-07-09 Control
ABC 10.6 2012-07-09 Pilot
ABC 17.725 2012-07-16 Control
ABC 10.6 2012-07-16 Pilot

Second:
ABC 156.7 06/25/2012 Control
ABC 391 06/25/2012 Pilot
ABC 156.7 07/02/2012 Control
ABC 391 07/02/2012 Pilot
ABC 156.7 07/09/2012 Control
ABC 391 07/09/2012 Pilot
ABC 156.7 07/16/2012 Control
ABC 391 07/16/2012 Pilot

Then for every subsequent week that is added to the database that same average would be applied to the monday aggregated date, so projecting out a bit for the above queries it would look like this:

First:
ABC 17.725 2012-06-25 Control
ABC 10.6 2012-06-25 Pilot
ABC 17.725 2012-07-02 Control
ABC 10.6 2012-07-02 Pilot
ABC 17.725 2012-07-09 Control
ABC 10.6 2012-07-09 Pilot
ABC 17.725 2012-07-16 Control
ABC 10.6 2012-07-16 Pilot
ABC 17.725 2012-07-23 Control
ABC 10.6 2012-07-23 Pilot
ABC 17.725 2012-07-30 Control
ABC 10.6 2012-07-30 Pilot

Second:
ABC 156.7 06/25/2012 Control
ABC 391 06/25/2012 Pilot
ABC 156.7 07/02/2012 Control
ABC 391 07/02/2012 Pilot
ABC 156.7 07/09/2012 Control
ABC 391 07/09/2012 Pilot
ABC 156.7 07/16/2012 Control
ABC 391 07/16/2012 Pilot
ABC 156.7 07/23/2012 Control
ABC 391 07/23/2012 Pilot
ABC 156.7 07/30/2012 Control
ABC 391 07/30/2012 Pilot

Does this explanation make a little more sense?

Thanks again for the help!

T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 13:15:48

ok for that you can do like this


SELECT Client_mnemonic,AVG([Indirect Care - Total Elapsed Time Per Day]) OVER (PARTITION BY Client_mnemonic,Group_Type,((Seq-1)/4)) AS AvgTime ,[Work Week], Group_Type 
FROM
(
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 ,
ROW_NUMBER() OVER (PARTITION BY n.Client_mnemonic,n.Group_Type ORDER BY [Work Week])  AS Seq
...
without last order by
)r
ORDER BY client_mnemonic,[Work Week]


SELECT Client_Mnemonic,AVG() OVER (PARTITION BY Client_Mnemonic,Group_type,((Seq-1)/4)) AS AvgTime,[Work Week],Group_type
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,
ROW_NUMBER() OVER (PARTITION BY Client_Mnemonic,Group_Type ORDER BY [Work Week]) AS Seq 
FROM workflow_data
WHERE
definition = 'Depart Process - Sign and Print Pat Ed'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Depart Process - Save'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Discharge Summary_v4 - Review and Sign'  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 
)r
ORDER BY [Work Week], Group_Type;

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

stwp86
Starting Member

42 Posts

Posted - 2012-07-30 : 14:00:07

I am reciving the following errors when I run the queries:

First query (this one doesnt seem like a copy/paste job, am i missing something?):

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'.

Get invalid column name warnings over client_mnemonic, [Indirect Care - Total Elapsed Time Per Day], [Work Week], Group Types
Get an incorrect syntax near "last"
Get an incorrect syntax near ")r"
Get an incorrect syntax near "ORDER"

For the second query I get the following errors:
Msg 174, Level 15, State 1, Line 1
The AVG function requires 1 argument(s).
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'r'.

Also get - The AVG function requires 1 argument(s)

Thoughts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 14:19:23

first query its not copy paste. i just gave you outline you need to replace your earlier query inside it


SELECT Client_mnemonic,AVG([Indirect Care - Total Elapsed Time Per Day]) OVER (PARTITION BY Client_mnemonic,Group_Type,((Seq-1)/4)) AS AvgTime ,[Work Week], Group_Type 
FROM
(
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 ,
ROW_NUMBER() OVER (PARTITION BY n.Client_mnemonic,n.Group_Type ORDER BY [Work Week])  AS Seq
...
copy paste rest of query without last order by
)r
ORDER BY client_mnemonic,[Work Week]


and second query should be this


SELECT Client_Mnemonic,AVG([Discharge - Average Elapsed Time]) OVER (PARTITION BY Client_Mnemonic,Group_type,((Seq-1)/4)) AS AvgTime,[Work Week],Group_type
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,
ROW_NUMBER() OVER (PARTITION BY Client_Mnemonic,Group_Type ORDER BY [Work Week]) AS Seq 
FROM workflow_data
WHERE
definition = 'Depart Process - Sign and Print Pat Ed'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Depart Process - Save'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Discharge Summary_v4 - Review and Sign'  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 
)r
ORDER BY [Work Week], Group_Type;

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

stwp86
Starting Member

42 Posts

Posted - 2012-07-30 : 15:47:26

I am still working my way through the first one, but the second creates the following error:
Msg 207, Level 16, State 1, Line 6
Invalid column name 'Work Week'.

Seems like it is happening around this bit of code:

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,
ROW_NUMBER() OVER (PARTITION BY Client_Mnemonic,Group_Type ORDER BY [Work Week]) AS Seq

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 15:58:08

where's the outser select?

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

stwp86
Starting Member

42 Posts

Posted - 2012-07-30 : 16:43:32

I didnt paste that in the reply, I was assuming the red was the where the error was originating from. The query was copy and pasted from your previous post, and I recieved the stated error.

This was the query that I used:

SELECT Client_Mnemonic,AVG([Discharge - Average Elapsed Time]) OVER (PARTITION BY Client_Mnemonic,Group_type,((Seq-1)/4)) AS AvgTime,[Work Week],Group_type
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,
ROW_NUMBER() OVER (PARTITION BY Client_Mnemonic,Group_Type ORDER BY [Work Week]) AS Seq 
FROM workflow_data
WHERE
definition = 'Depart Process - Sign and Print Pat Ed'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Depart Process - Save'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Discharge Summary_v4 - Review and Sign'  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 
)r
ORDER BY [Work Week], Group_Type;

You have the patients of a saint . . .Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 17:06:08

it should be this


SELECT Client_Mnemonic,AVG([Discharge - Average Elapsed Time]) OVER (PARTITION BY Client_Mnemonic,Group_type,((Seq-1)/4)) 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
definition = 'Depart Process - Sign and Print Pat Ed'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Depart Process - Save'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Discharge Summary_v4 - Review and Sign'  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 [Work Week], Group_Type;

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

stwp86
Starting Member

42 Posts

Posted - 2012-07-30 : 17:25:13

So close I can taste it . . .

The query returned the following information:

ABC 56.75 06/04/2012 Pilot
ABC 56.75 06/11/2012 Pilot
ABC 56.75 06/18/2012 Pilot
ABC 56.75 06/25/2012 Pilot
ABC 69.25 07/02/2012 Pilot
ABC 69.25 07/09/2012 Pilot
ABC 69.25 07/16/2012 Pilot
ABC 69.25 07/23/2012 Pilot

The only change that I am looking for is if the 56.75 be displayed for all dates, so it seemed to have shifted to 69.25, but I need those values to also be 56.75 . . .

Is that possible?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 17:36:05

quote:
Originally posted by stwp86

So close I can taste it . . .

The query returned the following information:

ABC 56.75 06/04/2012 Pilot
ABC 56.75 06/11/2012 Pilot
ABC 56.75 06/18/2012 Pilot
ABC 56.75 06/25/2012 Pilot
ABC 69.25 07/02/2012 Pilot
ABC 69.25 07/09/2012 Pilot
ABC 69.25 07/16/2012 Pilot
ABC 69.25 07/23/2012 Pilot

The only change that I am looking for is if the 56.75 be displayed for all dates, so it seemed to have shifted to 69.25, but I need those values to also be 56.75 . . .

Is that possible?

Thanks!

so what about next weeks average? you just wanted first four week avg for all?

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

stwp86
Starting Member

42 Posts

Posted - 2012-07-30 : 17:43:21

Exactly, I only need the first 4 week average to repeat. Basically, I need to create a baseline and be able to continue to displaay that same number for every new week of info that is added to our dashboard (it will just be a straight line)

Make Sense?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 19:26:31

[code]
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
definition = 'Depart Process - Sign and Print Pat Ed' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Depart Process - Save' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Discharge Summary_v4 - Review and Sign' 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 [Work Week], Group_Type;
[/code]

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

stwp86
Starting Member

42 Posts

Posted - 2012-07-30 : 20:34:59

That one is spot on, exactly what I was looking for.

Now for the other flavor of that query I just started working with what you put up previously, looks like this:

SELECT Client_mnemonic,AVG([Indirect Care - Total Elapsed Time Per Day]) OVER (PARTITION BY Client_mnemonic,Group_Type,((Seq-1)/4)) AS AvgTime ,[Work Week], Group_Type 
FROM
(
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 ,
ROW_NUMBER() OVER (PARTITION BY n.Client_mnemonic,n.Group_Type ORDER BY [Work Week])  AS Seq
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
)r
ORDER BY client_mnemonic,[Work Week]

I am recieving the following error:
Msg 207, Level 16, State 1, Line 5
Invalid column name 'Work Week'.

But now that I am thinking about this will this return the same way as the query you just assisted me with? I will look at it and see if I can copy some things from your previous one that only returns the same value.
thanks for the assitance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 21:36:18

should be like


SELECT Client_Mnemonic,AVG(CASE WHEN Seq <= 4 THEN [Indirect Care - Total Elapsed Time Per Day] 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 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
)p
)q
ORDER BY client_mnemonic,[Work Week]

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

stwp86
Starting Member

42 Posts

Posted - 2012-07-30 : 22:11:42

Awesome stuff, thanks a million . . . Grade A MVP

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources