0

so i've been trying to find an answer for a problem i have. i have a client with call information table. the table look like this: original table and he want me to create a new table that splits every record to hour quarters (0-15,15-30,30-45,45-60) and using the login time and duration of the call calculate for each row how many seconds in each hour quarter he was in the agentsate. so if he was in a talking state for 120 seconds(duration column) and he started talking on logindatetime of 14.3.18 11:44 i should add two columns to the new table for each quarter in the hour and what was the duration for it. something like this newtable does anyone have an idea? i thought about a cursor but i dont understand how to do it and it sounds like its going to have a big impact on performance. the duration in seconds can be up to a day, not only minutes or seconds.

thanks in advanced.

closed as unclear what you're asking by paparazzo, LowlyDBA, hot2use, RDFozz, Andriy M Mar 16 '18 at 8:59

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Please always include (via a tag) the version of SQL Server. Also, it's not quite clear what you're after. If the user started at 11:44 and you want to report duration per each 15-minute period, shouldn't there be a row for 11:30-11:45, representing the first minute they were talking, and another row for 11:45-12:00, representing the second minute they were talking? If that new column is only needed to represent which quarter hour the activity started in, this calculation is a lot simpler than you're making it sound. – Aaron Bertrand Mar 14 '18 at 11:55
  • what im trying to understand is how to calculate the results of duration in every 15 minutes.. like if it starts at 16:30 and ends in 16:52 how do i get new rows for every quarter like this: hour quarter 30-45, duration 15(in seconds but doesnt matter) and then another row : hour quarter 45-60 duration 7 – sql_girl Mar 14 '18 at 12:21
  • and the rows arent only for calls it can be on a break, talking, not ready, ready etc – sql_girl Mar 14 '18 at 12:23
  • 1
    I would store the end date. It makes more sense to track the end points and calculate the duration than storing one or the other. But really, your question should include real sample data and real desired results, so that anyone trying to write a query knows if it actually solves your problem. Your data doesn't technically have to be real, but it should be realistic. – Aaron Bertrand Mar 14 '18 at 15:07
  • 1
    That is not the actual output you desire? Come on show some effort. Please post text data and desired output. You can see my answer to this question for formatting option. dba.meta.stackexchange.com/questions/2919/… – paparazzo Mar 15 '18 at 14:16
1

Given this data:

USE tempdb;
GO

CREATE TABLE dbo.AgentLog
(
  AgentID int,
  AgentState varchar(32),
  LoginDateTime datetime,
  Duration int
);

INSERT dbo.AgentLog VALUES
(5045,'TALKING',   '20180318 11:44',130),
(5045,'WORK_READY','20180318 11:47',1200),
(5046,'READY',     '20180318 12:45',1800),
(5046,'TALKING',   '20180318 13:15',1801),
(5047,'READY',     '20180318 12:59',12000);

The way I thought about the problem is that you need to identify all the 15 time slots possible. Since you say no duration will last more than 24 hours, this means you need 96 time slots, plus one at the end to handle the end of any range.

Now, in order to distribute a call over 15-minute blocks, you need to join against all these time slots. There are either two or three categories of matching here, depending on the exact start/end time of any activity:

  1. the 15-minute interval where the activity begins
  2. any 15-minute interval completely encompassed by the activity
  3. the 15-minute interval where the activity ends

For 1., we measure the duration by the number of seconds that elapse between the start of the activity and the end of that first interval.

For 2., we measure the duration by the length of the interval.

For 3., we measure the duration by the number of seconds that elapse between the start of that last interval and the end of the activity.

So my query looks like this:

;WITH Activity AS 
(
  SELECT *, aStart = LoginDateTime, aEnd = DATEADD(SECOND, Duration, LoginDateTime)
  FROM dbo.AgentLog
), ARanges AS
(
  SELECT *, 
    ASRange = SMALLDATETIMEFROMPARTS
    (YEAR(a.aStart),MONTH(a.aStart),DAY(a.aStart),DATEPART(HOUR,a.aStart),DATEPART(MINUTE,a.aStart)/15*15),
    AERange = SMALLDATETIMEFROMPARTS
    (YEAR(a.aEnd),MONTH(a.aEnd),DAY(a.aEnd),DATEPART(HOUR,a.aEnd),DATEPART(MINUTE,a.aEnd)/15*15)
  FROM Activity AS a
), RealRanges AS
(
  SELECT *, 
    RSRange = DATEADD(MINUTE, 15*fifteen.rn, ARanges.ASRange),
    RERange = DATEADD(MINUTE, 15*(fifteen.rn+1), ARanges.ASRange)
    FROM ARanges 
    INNER JOIN 
    (
      SELECT TOP (97) rn = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
      FROM sys.all_objects ORDER BY [object_id]
    ) AS fifteen
    ON fifteen.rn <= DATEDIFF(MINUTE, ARanges.ASRange, ARanges.AERange)/15
)
SELECT AgentID, AgentState, LoginDateTime, Duration, QuarterHour = DATEPART(HOUR, RSRange),
    Abbreviation = CONCAT(DATEPART(MINUTE, RSRange) ,'-', DATEPART(MINUTE, RERange)),
    QuarterHourDuration = CASE 
      WHEN RSRange <= aStart THEN DATEDIFF(SECOND, aStart, DATEADD(MINUTE, 15, RSRange)) 
      WHEN RERange >= aEnd THEN DATEDIFF(SECOND, RSRange, aEnd) 
      ELSE DATEDIFF(SECOND, RSRange, RERange)
    END
 FROM RealRanges
 WHERE DATEDIFF(SECOND, RSRange, aEnd) > 0;
  • hi can you explain what you did in the realranges aranges please? i dont really understand the calculation.. i found a problem with this query when inserting a duration under a minute and when i add other clumns to the first table. :( thanks – sql_girl Mar 20 '18 at 9:01
  • like for example why are you doing the /15*15 – sql_girl Mar 20 '18 at 9:04
  • @winter_sql It's to round, using integer math, to 15 minutes. If you're changing the problem, it's possible you need a new solution. Please always ask the problem you have, without "dumbing it down." – Aaron Bertrand Mar 20 '18 at 13:13
1

3 Step approach:

  1. generate the intervals.
  2. match calls against overlapping ranges
  3. figure the duration of each call within that range
create table #calls 
(id int not null identity primary key, 
starttime datetime, 
duration int)

insert into #calls (starttime, duration) values 
('2018-05-14 8:33:00', 45) -- 1 period
,('2018-05-14 8:42:00', 460) -- 2 periods
,('2018-05-14 8:55:00', 3500) -- 5 periods

--Assume table [Number] (N int) with rows 1 to 1 mil. 
;
with Intervals as 
(select top (150000) dateadd(mi, n*15, '2018-01-01') interval_start, 
dateadd(mi, n*15+15, '2018-01-01') interval_end 
from Number order by N)
select * , 

case
--Full 15 min 
when starttime <= interval_start and dateadd(ss, duration, c.starttime) >=  interval_end 
then 15*60
-- partial, overlapping start
when starttime <= interval_start and dateadd(ss, duration, c.starttime) <  interval_end 
then datediff(ss, interval_start, dateadd(ss, duration, c.starttime))
-- partial, overlapping end
when starttime > interval_start and dateadd(ss, duration, c.starttime) >= interval_end 
then datediff(ss, c.starttime, interval_end)
-- partial, wholly contained
when starttime > interval_start and dateadd(ss, duration, c.starttime) < interval_end 
then duration
else null
end  interval_duration

from #calls c inner join Intervals 
on dateadd(ss, duration, c.starttime) >= interval_start 
and c.starttime < interval_end


go

drop table #calls
1

To me the requirements are not clear. But this is a try.

declare @t table (agent int, task char(1), logTm datetime, dur smallint);
insert into @t values 
       (100, 'a', '2000-010-01 09:01', 10)
     , (100, 'a', '2000-010-01 09:02', 10)
     , (100, 'b', '2000-010-01 09:08', 15)
     , (100, 'a', '2000-010-01 09:16', 11)
     , (100, 'a', '2000-010-01 09:17', 12)
     , (100, 'b', '2000-010-01 09:18', 17)
     , (100, 'c', '2000-010-01 09:36', 11)
     , (100, 'c', '2000-010-01 09:37', 12)
     , (100, 'c', '2000-010-01 09:38', 17);
with cte as 
(
select t.agent, cast(t.logTm as date) dt
     , datepart(hour, t.logTm) as hr 
     , t.task
     , datepart(MINUTE, t.logTm) / 15 as qtr
     , q.name 
     , t.dur
from @T t 
join ( values (0, '0-14'), (1, '15-29'), (2, '30-44'), (3, '45-59') 
     ) q(id, name)
  on datepart(MINUTE, t.logTm) / 15 = q.id 
) 
select t.agent, t.task, t.dt, t.hr, t.name, sum(t.dur) as dur 
from cte t
group by t.agent, t.dt, t.hr, t.qtr, t.task, t.name 
order by t.agent, t.dt, t.hr, t.qtr, t.task; 

agent       task dt         hr          name  dur
----------- ---- ---------- ----------- ----- -----------
100         a    2000-10-01 9           0-14  20
100         b    2000-10-01 9           0-14  15
100         a    2000-10-01 9           15-29 23
100         b    2000-10-01 9           15-29 17
100         c    2000-10-01 9           30-44 40
  • Thank you this really really helped. its what i needed pretty much! – sql_girl Mar 18 '18 at 7:28
  • it really helped but didnt split to seconds the duration – sql_girl Mar 19 '18 at 9:32
  • @GilDeWinter Question was not clear on that. Answer is only as good as the question. – paparazzo Mar 19 '18 at 10:51
  • yeah youre right i wasnt clear enough. – sql_girl Mar 19 '18 at 10:52
  • thanks alot for helping anyway it was helpful. sorry i wasnt clear – sql_girl Mar 19 '18 at 10:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.