January 7, 2015 at 1:09 pm
Ok so I took over for a previous DBA. He wrote SQL reports for a phone system to pull data. Problem is, this only queries for the current month and I needed last month's data. CAn anyone make any means of this query? Is there anyway I can tweak it to get last month's data? This query is actually pulling data from a MySQL database through and ODBC connection. It's a mess, but it's what I'm stuck with.
Select * into #wrap from openquery(SHORETEL_CCIR,'SELECT e.g_event_id,wc.w_name,e.event_id,ep.agent_id,
a.a_name,et.event_name,g.g_name,e.event_time,rc.rc_name,month(e.event_time) as ''Month'',YEAR(e.event_time) as ''Year'', field_name
FROM agent a
LEFT JOIN event_parties ep
on a.agent_id = ep.agent_id
LEFT JOIN events e ON ep.g_event_id=e.g_event_id
LEFT JOIN release_codes rc ON e.release_id=rc.rc_id
LEFT JOIN event_types et ON e.event_id=et.event_id
LEFT JOIN wu_code wc ON wc.w_number=e.w_number
LEFT JOIN ivr_apps ia ON ia.ivr_app_id=e.ivr_app_id
LEFT JOIN cause_code cc ON cc.cause_id=e.cause_id
LEFT JOIN dial_lists dl ON dl.dl_id=e.dl_id
LEFT JOIN event_call_profile ecp ON ecp.g_event_id=e.g_event_id
LEFT JOIN cp_fields cf ON cf.field_id = ecp.field_id
LEFT JOIN ccs_hdr ch ON ch.ccs_id=e.ccs_id
LEFT JOIN services s ON s.srv_id=e.srv_id
LEFT JOIN irn i ON i.irn_id=e.irn_id
LEFT JOIN event_groups eg ON eg.g_event_id=e.g_event_id
LEFT JOIN grp g ON g.group_id=eg.group_id
where year(event_time) =year(now())
' )
delete from #wrap where a_name not in (select distinct a_name from #wrap where g_name in ('Cust Serv','CS Overflow', 'Claims', 'Rep', 'LTC') and event_name like '%Agent%' AND a_name not like 'Ann Jegerlehner' )
Select month, count(event_name) as 'Answer' into #Answer from #wrap
where g_name !='NULL' AND field_name = 'Alternative Call ID'
AND event_name = 'agent answer'
group by month
select month,CONVERT(char(8), DATEADD(second, AverageWrap,'0:00:00'),108) as AverageWrap, AverageWrap as AveWSec into #avgWrap from (Select w.month,
SUM(
datediff(second,W.event_time,w2.event_time))/COUNT(w.a_name) as AverageWrap
FROM #Wrap W
cross apply
(
Select top 1 *
from #Wrap W3
where w3.agent_id = w.agent_id
and w3.event_time < w.event_time
and (w3.event_id = 4 OR w3.event_id = 2)
order by w3.event_time desc
) w3
cross apply
(
Select top 1 *
from #Wrap W2
where w.agent_id = w2.agent_id
and w2.event_time > w.event_time
and w2.event_id = 14
) w2
where w.event_id = 34
group by w.month)w
select month, CONVERT(char(8), DATEADD(second, AverageHold,'0:00:00'),108) as AverageHold, AverageHold as AvgHSec into #avgHold from (Select w.month,
SUM(
datediff(second,W.event_time,w2.event_time))/COUNT(w.a_name) as AverageHold
FROM #Wrap W
cross apply
(
Select top 1 *
from #Wrap W2
where w.agent_id = w2.agent_id
and w2.event_time > w.event_time
and (w2.event_id != 19)
order by w2.event_time
) w2
where w.event_id = 19
group by w.month)x
select month, CONVERT(char(8), DATEADD(second, AverageTreat,'0:00:00'),108) as AverageTreat, AverageTreat as AvgTSec into #avgTreat from (Select w.month,
SUM(
datediff(second,W.event_time,w2.event_time))/COUNT(w.a_name) as AverageTreat
FROM #Wrap W
cross apply
(
Select top 1 *
from #Wrap W2
where w.agent_id = w2.agent_id
and w2.event_time > w.event_time
and (w2.event_id =14)
order by w2.event_time
) w2
where w.event_id = 4 and w.field_name = 'Alternative Call ID'
group by w.month)x
select month,
case when TotalTreat> (24*60*60)
then
cast(TotalTreat/86400 as varchar(50))+':'+
Convert(VarChar, DateAdd(S, TotalTreat, 0), 108)
else
convert(varchar(8), dateadd(second, TotalTreat, '0:00:00'), 108)
end as TotalTreat, TotalTreat/3600.00 as TotalTHrs into #totalTreat from (Select w.month,
SUM(
datediff(second,W.event_time,w2.event_time)) as TotalTreat
FROM #Wrap W
cross apply
(
Select top 1 *
from #Wrap W2
where w.agent_id = w2.agent_id
and w2.event_time > w.event_time
and (w2.event_id =14)
order by w2.event_time
) w2
where w.event_id = 4 and w.field_name = 'Alternative Call ID'
group by w.month)x
select month, CONVERT(char(8), DATEADD(second, AverageTalk,'0:00:00'),108) as AverageTalk, AverageTalk as AvgTalkSec into #avgTalk from (Select w.month,
SUM(
datediff(second,W.event_time,w2.event_time))/COUNT(w.a_name) as AverageTalk
FROM #Wrap W
cross apply
(
Select top 1 *
from #Wrap W2
where w.agent_id = w2.agent_id
and w2.event_time > w.event_time
and (w2.event_id =34)
order by w2.event_time
) w2
where w.event_id = 4 and w.field_name = 'Alternative Call ID'
group by w.month)x
Select month, CONVERT(char(8), DATEADD(second, AverageRelease,'0:00:00'),108) as AverageRelease, AverageRelease as AvgReleaseSec into #avgRelease from (Select w.month,
sum(datediff(second,W.event_time,w2.event_time))/COUNT(w.a_name) as AverageRelease
FROM #Wrap W
cross apply
(
Select top 1 *
from #Wrap W2
where w.agent_id = w2.agent_id
and w2.event_time > w.event_time
and (w2.event_id = 33 or w2.event_id = 32 or w2.event_id = 30)
Order by w2.event_time
) w2
where w.event_id = 32
Group By w.month)x
Select month,
case when TotalRelease> (24*60*60)
then
cast(TotalRelease/86400 as varchar(50))+':'+
Convert(VarChar, DateAdd(S, TotalRelease, 0), 108)
else
convert(varchar(8), dateadd(second, TotalRelease, '0:00:00'), 108)
end as TotalRelease, TotalRelease/3600.00 as TotalReleaseHrs into #totalRelease from (Select w.month,
sum(datediff(second,W.event_time,w2.event_time)) as TotalRelease
FROM #Wrap W
cross apply
(
Select top 1 *
from #Wrap W2
where w.agent_id = w2.agent_id
and w2.event_time > w.event_time
and (w2.event_id = 33 or w2.event_id = 32 or w2.event_id = 30)
Order by w2.event_time
) w2
where w.event_id = 32
Group By w.month)x
select month, CONVERT(char(8), DATEADD(second, AverageRing,'0:00:00'),108) as AverageRing, AverageRing as AvgRingSec, totalringsec, Count into #ring from (Select w.month,
SUM(
iif(datediff(second,W.event_time,w2.event_time) > 12,12,
datediff(second,W.event_time,w2.event_time)))/COUNT(w.a_name) as AverageRing,
SUM(
iif(datediff(second,W.event_time,w2.event_time) > 12,12,
datediff(second,W.event_time,w2.event_time))) as totalringsec ,
count(w.a_name) as Count
FROM #Wrap W
cross apply
(
Select top 1 *
from #Wrap W2
where w.agent_id = w2.agent_id
and w2.event_time >= w.event_time
and (w2.event_id =4)
and w2.field_name = 'Alternative Call ID'
order by w2.event_time
) w2
where w.event_id = 5 and w.field_name = 'Alternative Call ID'
group by w.month)x
select w.month,a.answer as Answered,w.AverageWrap,w.AveWSec, isNULL(h.AverageHold,'00:00:00')as AverageHold, isNULL(h.AvgHSec,0) as AvgHSec,
isNULL(t.AverageTreat,'00:00:00')as AverageTreat, isNULL(t.AvgTSec,0)as AvgTSec,tt.totalTreat, tt.totalTHrs,
CONVERT(varchar, DATEADD(s, t.AvgTSec-w.AveWSec, 0), 108) as AverageTalk, tk.AvgTalkSec,ar.AverageRelease, ar.AvgReleaseSec, tr.TotalRelease, tr.TotalReleaseHrs,
r.AverageRing, r.AvgRingSec
FROM
#avgWrap w
LEFT JOIN
#avgHold h ON w.month=h.month
Left Join
#avgTreat t ON w.month=t.month
Left Join
#Answer a ON w.month=a.month
Left Join
#avgTalk tk ON w.month=tk.month
Left Join
#totalTreat tt ON w.month=tt.month
Left Join
#avgRelease ar ON w.month = ar.month
left Join
#totalRelease tr ON w.month = tr.month
left join
#ring r ON w.month = r.month
--where w.a_name in (@agent)
drop table #wrap
drop table #avgHold
drop table #avgWrap
drop table #avgTreat
drop table #answer
drop table #avgTalk
drop table #totalTreat
drop table #avgRelease
drop table #totalRelease
drop table #ring
January 7, 2015 at 1:46 pm
First, this site is for Microsoft's SQL Server and while they are similar, MySQL has some differences and one of them is where your issue is. This part is the problem.
where year(event_time) =year(now())
The script actually returns the entire year's worth of data, it just so happens that January is the only month this year. SQL Server doesn't use year(now()). So you need to figure out how to change that value to get last year's data. Maybe:
where year(event_time) =year(now()-1)
-SQLBill
January 7, 2015 at 2:47 pm
I did try this, but it still just gave me the same month's data.
January 7, 2015 at 3:43 pm
I have to admit that I know little about MySQL, but you should be able to use something like this:
WHERE event_time>DATEADD(m,-2,GETDATE())
(subtracts two months from the current date).
January 7, 2015 at 3:45 pm
For previous month, according to the first result on Google, would be using the following clause.
SELECT * INTO #Wrap
FROM OPENQUERY( Shoretel_Ccir, 'SELECT e.g_event_id,wc.w_name,e.event_id,ep.agent_id,
a.a_name,et.event_name,g.g_name,e.event_time,rc.rc_name,month(e.event_time) as ''Month'',YEAR(e.event_time) as ''Year'', field_name
FROM agent a
LEFT JOIN event_parties ep
on a.agent_id = ep.agent_id
LEFT JOIN events e ON ep.g_event_id=e.g_event_id
LEFT JOIN release_codes rc ON e.release_id=rc.rc_id
LEFT JOIN event_types et ON e.event_id=et.event_id
LEFT JOIN wu_code wc ON wc.w_number=e.w_number
LEFT JOIN ivr_apps ia ON ia.ivr_app_id=e.ivr_app_id
LEFT JOIN cause_code cc ON cc.cause_id=e.cause_id
LEFT JOIN dial_lists dl ON dl.dl_id=e.dl_id
LEFT JOIN event_call_profile ecp ON ecp.g_event_id=e.g_event_id
LEFT JOIN cp_fields cf ON cf.field_id = ecp.field_id
LEFT JOIN ccs_hdr ch ON ch.ccs_id=e.ccs_id
LEFT JOIN services s ON s.srv_id=e.srv_id
LEFT JOIN irn i ON i.irn_id=e.irn_id
LEFT JOIN event_groups eg ON eg.g_event_id=e.g_event_id
LEFT JOIN grp g ON g.group_id=eg.group_id
where event_time BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, ''%Y-%m-01 00:00:00'')
AND DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), ''%Y-%m-%d 23:59:59'')
' );
For previous year, is easier.
where year(event_time) =year(now()) - 1
If this doesn't work, you'll have to go through all the code (250 lines after I ran my formatting tool).
January 8, 2015 at 7:55 am
I tried this and ran the report and it came back with this..
An error occurred during the local report processing. Query execution failed for dataset 'DataSet1' Cannot get the column information from OLE DB provider 'MSDASQL' for linked server "Shortel_CCIR"
January 8, 2015 at 8:01 am
Luis Cazares (1/7/2015)
For previous month, according to the first result on Google, would be using the following clause.
SELECT * INTO #Wrap
FROM OPENQUERY( Shoretel_Ccir, 'SELECT e.g_event_id,wc.w_name,e.event_id,ep.agent_id,
a.a_name,et.event_name,g.g_name,e.event_time,rc.rc_name,month(e.event_time) as ''Month'',YEAR(e.event_time) as ''Year'', field_name
FROM agent a
LEFT JOIN event_parties ep
on a.agent_id = ep.agent_id
LEFT JOIN events e ON ep.g_event_id=e.g_event_id
LEFT JOIN release_codes rc ON e.release_id=rc.rc_id
LEFT JOIN event_types et ON e.event_id=et.event_id
LEFT JOIN wu_code wc ON wc.w_number=e.w_number
LEFT JOIN ivr_apps ia ON ia.ivr_app_id=e.ivr_app_id
LEFT JOIN cause_code cc ON cc.cause_id=e.cause_id
LEFT JOIN dial_lists dl ON dl.dl_id=e.dl_id
LEFT JOIN event_call_profile ecp ON ecp.g_event_id=e.g_event_id
LEFT JOIN cp_fields cf ON cf.field_id = ecp.field_id
LEFT JOIN ccs_hdr ch ON ch.ccs_id=e.ccs_id
LEFT JOIN services s ON s.srv_id=e.srv_id
LEFT JOIN irn i ON i.irn_id=e.irn_id
LEFT JOIN event_groups eg ON eg.g_event_id=e.g_event_id
LEFT JOIN grp g ON g.group_id=eg.group_id
where event_time BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, ''%Y-%m-01 00:00:00'')
AND DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), ''%Y-%m-%d 23:59:59'')
' );
For previous year, is easier.
where year(event_time) =year(now()) - 1
If this doesn't work, you'll have to go through all the code (250 lines after I ran my formatting tool).
OMG THANK YOU SO MUCH!!!! THIS WORKED!!!!
January 8, 2015 at 8:31 am
I also have a need for previous week. How would I go about editting this code?
where event_time >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND event_time < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply