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
 Difficult rolling average

Author  Topic 

emailuser
Yak Posting Veteran

74 Posts
Posted - 2012-02-01 : 10:14:25
Hi everyone , many thanks for all your past help , i have now come to a brick wall which for the life of me i cant work out how to get past

Heres the scenario we have 2 sql dbs one for our telephone system and one for our ticketing system, current we extract and count up the telephone data and the ticket data and on a daily basis then calculate a percent column based on this .. so if we have 100 calls and 100 tickets we get 100% , this works fine , what we now want to do is to give us a year to date figure based on todays date back to the beginning of the year , so if we had day1, 80% , day2 80% and day3 100% the rolling average would be 90%

As i said we currrently have it working with the attached script to give us day by day , but we now want a single rolling average figure ; in the script ACD relates to telephone calls , HOTH relates to tickets .. any any pointers great fully recieved ... 


Select 
abc
,date_from
,ACDCALLS
,HOTH
,Cast((HOTH/cast(ACDCALLS as Numeric(6,3)))*100 as Int) as Percentage
From (SELECT COUNT(ccmdb1.dbo.agent.agent_name) as abc,agent_activity_cur.date_from,
SUM(agent_activity_cur.acd_grp_conv_call_cnt) AS ACDCALLS, 

(SELECT COUNT(supportdeskdev.suppdeskdev.F0004_SUPEVENT.REFERENCE)
FROM supportdeskdev.suppdeskdev.F0004_SUPEVENT

WHERE 

supportdeskdev.suppdeskdev.F0004_SUPEVENT.OPENDATE = agent_activity_cur.date_from and supportdeskdev.suppdeskdev.F0004_SUPEVENT.CUSTOMERREF <>'Premium'

GROUP BY supportdeskdev.suppdeskdev.F0004_SUPEVENT.OPENDATE) as HOTH

FROM agent INNER JOIN agent_activity_cur
ON ccmdb1.dbo.agent.agent_id = agent_activity_cur.agent_id

where agent_activity_cur.date_from >'2012-01-01' and agent_activity_cur.agent_grp_id='16' 



GROUP BY datepart(day,agent_activity_cur.date_from), agent_activity_cur.date_from
HAVING (sum(acd_grp_conv_call_cnt + acd_dir_conv_call_cnt)) <>'0'


) A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-01 : 12:58:16
would you mind posting some sample data and explaining waht you're after?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts
Posted - 2012-02-01 : 13:36:21
Hi Visakh16 ,
the above returns this data, we are looking to have a single column which gives an average of the percent column
hope this makes sense ;

Date ACD Calls HOTH Calls Percent
30/01/2012 00:00 133 129 96
04/01/2012 00:00 106 105 99
13/01/2012 00:00 106 105 99
31/01/2012 00:00 97 95 97
05/01/2012 00:00 93 106 113
23/01/2012 00:00 133 128 96
06/01/2012 00:00 131 130 99
24/01/2012 00:00 114 113 99
16/01/2012 00:00 107 106 99
25/01/2012 00:00 125 124 99
17/01/2012 00:00 129 128 99
26/01/2012 00:00 144 139 96
09/01/2012 00:00 119 118 99
18/01/2012 00:00 116 115 99
27/01/2012 00:00 129 125 96
10/01/2012 00:00 127 127 100
19/01/2012 00:00 88 85 96
11/01/2012 00:00 118 117 99
20/01/2012 00:00 121 121 100
01/02/2012 00:00 121 118 97
03/01/2012 00:00 103 103 100
12/01/2012 00:00 125 123 98
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-01 : 13:38:12
you just mean to return a single value rather than one per date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts
Posted - 2012-02-02 : 03:32:19
Hi Visakh16 , yes we want a single value which is the average of the percent column
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2012-02-02 : 03:38:40
You want a moving average of the lat three days?
Visakh will soon show you how to do this with OUTER APPLY.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts
Posted - 2012-02-02 : 07:20:47
Hi SwePeso , we want a rolling average YTD ... if that makes sense , so we can see at the time we run it how we are doing YTD ( the team has a goal to be at say 97% at year end , we will run this several times a day every day so they can see if they are on track or not ... hope this makes sense
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-02 : 10:18:46
do you mean this?


SELECT AVG(Percentage*1.0)
FROM
(
Select 
abc
,date_from
,ACDCALLS
,HOTH
,Cast((HOTH/cast(ACDCALLS as Numeric(6,3)))*100 as Int) as Percentage
From (SELECT COUNT(ccmdb1.dbo.agent.agent_name) as abc,agent_activity_cur.date_from,
SUM(agent_activity_cur.acd_grp_conv_call_cnt) AS ACDCALLS, 

(SELECT COUNT(supportdeskdev.suppdeskdev.F0004_SUPEVENT.REFERENCE)
FROM supportdeskdev.suppdeskdev.F0004_SUPEVENT

WHERE 

supportdeskdev.suppdeskdev.F0004_SUPEVENT.OPENDATE = agent_activity_cur.date_from and supportdeskdev.suppdeskdev.F0004_SUPEVENT.CUSTOMERREF <>'Premium'

GROUP BY supportdeskdev.suppdeskdev.F0004_SUPEVENT.OPENDATE) as HOTH

FROM agent INNER JOIN agent_activity_cur
ON ccmdb1.dbo.agent.agent_id = agent_activity_cur.agent_id

where agent_activity_cur.date_from >'2012-01-01' and agent_activity_cur.agent_grp_id='16' 



GROUP BY datepart(day,agent_activity_cur.date_from), agent_activity_cur.date_from
HAVING (sum(acd_grp_conv_call_cnt + acd_dir_conv_call_cnt)) <>'0'


) A
)R


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts
Posted - 2012-02-02 : 10:40:19
I see no CROSS APPLY (or OUTER APPLY). Are you ill?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-02 : 10:58:28
quote:
Originally posted by DonAtWork

I see no CROSS APPLY (or OUTER APPLY). Are you ill?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

first let me understand what OP is really after

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2012-02-02 : 12:02:57
someone will probably give you a more sensible answer but for s**ts and giggles:


-- Data
IF OBJECT_ID('tempdb..#data') IS NOT NULL DROP TABLE #data
CREATE TABLE #data (
	[Date] DATE NOT NULL
	, [ACD Calls] INT NOT NULL
	, [HOTH Calls] INT NOT NULL
	, [Percent] INT NOT NULL
	, [RollingPercentage] DECIMAL(5,2) NULL
	)

CREATE UNIQUE CLUSTERED INDEX IX_QUIRKY ON #data ([Date])

SET DATEFORMAT DMY

INSERT #data ([Date], [ACD Calls], [HOTH Calls], [Percent])
VALUES 
	('30/01/2012 00:00',	133,	129,	96)
	, ('04/01/2012 00:00',	106,	105,	99)
	, ('13/01/2012 00:00',	106,	105,	99)
	, ('31/01/2012 00:00',	97,		95,		97)
	, ('05/01/2012 00:00',	93,		106,	113)
	, ('23/01/2012 00:00',	133,	128,	96)
	, ('06/01/2012 00:00',	131,	130,	99)
	, ('24/01/2012 00:00',	114,	113,	99)
	, ('16/01/2012 00:00',	107,	106,	99)
	, ('25/01/2012 00:00',	125,	124,	99)
	, ('17/01/2012 00:00',	129,	128,	99)
	, ('26/01/2012 00:00',	144,	139,	96)
	, ('09/01/2012 00:00',	119,	118,	99)
	, ('18/01/2012 00:00',	116,	115,	99)
	, ('27/01/2012 00:00',	129,	125,	96)
	, ('10/01/2012 00:00',	127,	127,	100)
	, ('19/01/2012 00:00',	88,		85,		96)
	, ('11/01/2012 00:00',	118,	117,	99)
	, ('20/01/2012 00:00',	121,	121,	100)
	, ('01/02/2012 00:00',	121,	118,	97)
	, ('03/01/2012 00:00',	103,	103,	100)
	, ('12/01/2012 00:00',	125,	123,	98)


-- Quirky
DECLARE @percentage DECIMAL(5,2)
DECLARE @rolling DECIMAL(5,2) = 100.00

UPDATE #data SET
	[RollingPercentage] = CASE WHEN @percentage IS NULL THEN [Percent] ELSE @rolling END
	, @percentage = [percent]
	, @rolling = @rolling - ((@rolling - [percent]) / 2.0)
OPTION (MAXDOP 1)

SELECT * FROM #data


Results:

Date       ACD Calls   HOTH Calls  Percent     RollingPercentage
---------- ----------- ----------- ----------- ---------------------------------------
2012-01-03 103         103         100         100.00
2012-01-04 106         105         99          99.50
2012-01-05 93          106         113         106.25
2012-01-06 131         130         99          102.63
2012-01-09 119         118         99          100.82
2012-01-10 127         127         100         100.41
2012-01-11 118         117         99          99.71
2012-01-12 125         123         98          98.86
2012-01-13 106         105         99          98.93
2012-01-16 107         106         99          98.97
2012-01-17 129         128         99          98.99
2012-01-18 116         115         99          99.00
2012-01-19 88          85          96          97.50
2012-01-20 121         121         100         98.75
2012-01-23 133         128         96          97.38
2012-01-24 114         113         99          98.19
2012-01-25 125         124         99          98.60
2012-01-26 144         139         96          97.30
2012-01-27 129         125         96          96.65
2012-01-30 133         129         96          96.33
2012-01-31 97          95          97          96.67
2012-02-01 121         118         97          96.84


NB: Don't worry about this example -- it's probably not what you want to do!

If you could post the expected results for your data sample that would be good.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts
Posted - 2012-02-03 : 05:22:40
Thanks everyone for the great responses so far , i think i am trying to complicate this too much ... i think if we add all the HOTH calls ytd and all the ACD calls ytd then divide ACD by HOTH multiply by 100 we would get the correct output ... so sample data below

Date ACD Calls HOTH Calls Percent RollingPercentage
---------- ----------- ----------- ----------- ---------------------------------------
2012-01-03 103 103 100 100
2012-01-04 106 105 99 100
2012-01-05 93 106 113 96
2012-01-06 131 130 99 95
2012-01-09 119 118 99 96
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-03 : 09:25:20
[code]
With CTE
AS
(
Select
abc
,date_from
,ACDCALLS
,HOTH
,Cast((HOTH/cast(ACDCALLS as Numeric(6,3)))*100 as Int) as Percentage
From (SELECT COUNT(ccmdb1.dbo.agent.agent_name) as abc,agent_activity_cur.date_from,
SUM(agent_activity_cur.acd_grp_conv_call_cnt) AS ACDCALLS,

(SELECT COUNT(supportdeskdev.suppdeskdev.F0004_SUPEVENT.REFERENCE)
FROM supportdeskdev.suppdeskdev.F0004_SUPEVENT

WHERE

supportdeskdev.suppdeskdev.F0004_SUPEVENT.OPENDATE = agent_activity_cur.date_from and supportdeskdev.suppdeskdev.F0004_SUPEVENT.CUSTOMERREF <>'Premium'

GROUP BY supportdeskdev.suppdeskdev.F0004_SUPEVENT.OPENDATE) as HOTH

FROM agent INNER JOIN agent_activity_cur
ON ccmdb1.dbo.agent.agent_id = agent_activity_cur.agent_id

where agent_activity_cur.date_from >'2012-01-01' and agent_activity_cur.agent_grp_id='16'



GROUP BY datepart(day,agent_activity_cur.date_from), agent_activity_cur.date_from
HAVING (sum(acd_grp_conv_call_cnt + acd_dir_conv_call_cnt)) <>'0'
)

SELECT c1.*,RollAvg
FROM CTE c1
CROSS APPLY (SELECT SUM(ACD)*100.0/NULLIF(SUM(HOTH),0) AS RollAvg
FROM CTE
WHERE Date >= DATEADD(yy,DATEDIFF(yy,0,c1.[Date]),0)
AND Date < DATEADD(dd,DATEDIFF(dd,0,c1.[Date])+1,0)
)c2

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts
Posted - 2012-02-03 : 11:54:13
Hi Visakh16 , should this work with SQL2000? as i get an error : A SELECT statement must begin with the word "select".
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2012-02-03 : 12:05:21
Can't use Common table expressions (CTE) or APPLY (CROSS OR OUTER) in sql 2000.

2005 is the first compatibility level to support it.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-03 : 12:05:56
nope it wont. for sql 2000 you've to get first query results onto temporary table (#Temp) and then use subquery approach instead

SELECT t.*,(SELECT SUM(ACD)*100.0/NULLIF(SUM(HOTH),0) AS RollAvg
             FROM #temp 
             WHERE Date >= DATEADD(yy,DATEDIFF(yy,0,t.[Date]),0)
             AND Date < DATEADD(dd,DATEDIFF(dd,0,t.[Date])+1,0)
            ) AS RollAvg
FROM #temp t




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts
Posted - 2012-02-14 : 04:12:47
Hi everyone i have been away for a few days ... visakh16 , i am not sure what you mean ..subquery approach ...

again perhaps i am thinking too deep to work this out , perhaps if this approach is used it can work with sql 2000 ?? ... add up column1 data ACD calls from 01-01-2012 , then add up column2 data HOTH calls from 01-01-2012 then divide one by the other and multiply by 100 ...
to give us the result column ( percent )
example data and output would be

Date(dd/mm/yy) ACD HOTH Result
01-02-12 120 120 100
02-02-12 110 110 100
03-02-12 120 115 101
04-02-12 130 125 102
05-02-12 130 130 101
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-14 : 09:39:44
quote:
Originally posted by emailuser

Hi everyone i have been away for a few days ... visakh16 , i am not sure what you mean ..subquery approach ...

again perhaps i am thinking too deep to work this out , perhaps if this approach is used it can work with sql 2000 ?? ... add up column1 data ACD calls from 01-01-2012 , then add up column2 data HOTH calls from 01-01-2012 then divide one by the other and multiply by 100 ...
to give us the result column ( percent )
example data and output would be

Date(dd/mm/yy) ACD HOTH Result
01-02-12 120 120 100
02-02-12 110 110 100
03-02-12 120 115 101
04-02-12 130 125 102
05-02-12 130 130 101

I meant what I showed in previous suggestion
your second logic is what subquery does in my suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts
Posted - 2012-02-15 : 05:22:01
Hi Visakh16 again thanks for a quick response ... could you repost the code i should use please ..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-15 : 09:16:27
same as befor


SELECT t.*,(SELECT SUM(ACD)*100.0/NULLIF(SUM(HOTH),0) AS RollAvg
             FROM #temp 
             WHERE Date >= DATEADD(yy,DATEDIFF(yy,0,t.[Date]),0)
             AND Date < DATEADD(dd,DATEDIFF(dd,0,t.[Date])+1,0)
            ) AS RollAvg
FROM #temp t





only thing to note is if you want to consider only data for that month instead of year you need to change yy to mm inside datediff,dateadd functions above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts
Posted - 2012-02-15 : 13:04:40
Hi Visakh16 , how would i incorporate it into the main script which pulls the data ???? , i am not very clever on nested if statements .. etc

Select
abc
,date_from
,ACDCALLS
,HOTH
,Cast((HOTH/cast(ACDCALLS as Numeric(6,3)))*100 as Int) as Percentage
From (SELECT COUNT(ccmdb1.dbo.agent.agent_name) as abc,agent_activity_cur.date_from,
SUM(agent_activity_cur.acd_grp_conv_call_cnt) AS ACDCALLS,

(SELECT COUNT(supportdeskdev.suppdeskdev.F0004_SUPEVENT.REFERENCE)
FROM supportdeskdev.suppdeskdev.F0004_SUPEVENT

WHERE

supportdeskdev.suppdeskdev.F0004_SUPEVENT.OPENDATE = agent_activity_cur.date_from and supportdeskdev.suppdeskdev.F0004_SUPEVENT.CUSTOMERREF <>'Premium'

GROUP BY supportdeskdev.suppdeskdev.F0004_SUPEVENT.OPENDATE) as HOTH

FROM agent INNER JOIN agent_activity_cur
ON ccmdb1.dbo.agent.agent_id = agent_activity_cur.agent_id

where agent_activity_cur.date_from >'2012-01-01' and agent_activity_cur.agent_grp_id='16'



GROUP BY datepart(day,agent_activity_cur.date_from), agent_activity_cur.date_from
HAVING (sum(acd_grp_conv_call_cnt + acd_dir_conv_call_cnt)) <>'0'


) A
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site