Adding blank rows where data hasn't been recorded to fit pattern

Question

I'm looking at doing some reporting on staff scheduling, and need to build a graph that shows how many calls were coming through within 15 minute intervals, but also need to display when 0 calls are coming through.

My main problem is that the original data comes from the phone system which only writes a row if a call is received. I've got no problem splitting the data into 15 minute intervals, but I need to fill in the blanks.

For example, calls through at:

06:45
07:15
07:30

But no call through at 07:00. I need to be able to have an entry for this timestamp, so that when put into a graph, it visibly shows 0 calls were coming through at that moment.

I created a table that has every 15 minute interval of the day - 96 rows And i did a forced join with

the rows that do not match an existing time interval, in this case a 07:00, appears as null,
96 rows in total, 3 rows that match, and 93 that appear as full nulls. (I've only added in 3 test rows which I have provided above as the example calls through)

My main goal is that for every 15 minute interval for a data set, even if there is no call and thus no row entered on one table, I still need to show the 15 minute interval.

I don't have an issue with the SQL splitting every 15 minutes, so I don't think it's worth me posting the SQL here yet, as I think it's more of an object issue.

Any help is great!

Sample rows from original dbase after i've split by 15 minutes:

 Date        NumberOfCalls  TransInterval
 25/11/2015         1            06:45  
 25/11/2015         1            07:15  
 25/11/2015         1             07:30  
 25/11/2015         1             07:45  
 25/11/2015         1             08:00

Split using:

SELECT ALL CONVERT(varchar, start_time, 103) AS Date
     , COUNT(irn) AS NumberOfCalls
     , DATEPART(Year, start_time) AS TransactionYear
     , DATEPART(MONTH, start_time) AS TransactionMonth
     , DATEPART(DAY, start_time) AS TransactionDate
     , DATEPART(Hour, start_time) AS TransactionHour
     , DATEPART(Minute, start_time) / 15 AS TransactionInterval
     , CONVERT(Varchar, DATEPART(Hour, start_time)) 
          + CASE DatePart(Minute, [start_time]) / 15 
          WHEN 0 THEN ':00' 
          WHEN 1 THEN ':15' 
          WHEN 2 THEN ':30' 
          WHEN 3 THEN ':45' END AS TransInterval
FROM  dbo.callsdbase
WHERE start_time > '2015-11-25 00:00:00' 
      AND start_time < '2015-11-25 23:59:59'
GROUP BY DATEPART(Year, start_time)
      , DATEPART(MONTH, start_time)
      , DATEPART(DAY, start_time)
      , DATEPART(Hour, start_time)
      , DATEPART(Minute, start_time) / 15
      , start_time
ORDER BY date, transactionyear, TransactionMonth
      , transactiondate, transactionhour, TransactionInterval

I've cut out the excess columns such as datepart year/month/date/hour etc.

What I dream of:

**25/11/2015    0   06:30**  
  25/11/2015    1   06:45  
**25/11/2015    0   07:00**  
  25/11/2015    1   07:15  
  25/11/2015    1   07:30  
  25/11/2015    1   07:45  
  25/11/2015    1   08:00

My interval table I created that I tried to join with - Only 1 column:

I understand i'd need to link on the date at some stage, but I was just trying to get it to link for a couple of time intervals first.

Example of what happens when I link across the data without the 0's into the interval table with a forced right/ full outer join:

 Date   NrOfCalls  TYear  TMonth  TDate THour TInterval TransInterval  Expr1  
 2015-11-25 1      2015   11       25    6    3          06:45         06:45       
 2015-11-25 1      2015   11       25    7    1          07:15         07:15       
 2015-11-25 1      2015   11       25    7    2          07:30         07:30       
 NULL       NULL   NULL   NULL     NULL  NULL NULL       NULL          NULL  
 NULL       NULL   NULL   NULL     NULL  NULL NULL       NULL          NULL

The above kind of works

I have 96 0:15 minute intervals,
and it results 96 rows, 93 full null,
but the last column shouldn't be nulls, it should have from 00:15, to 23:45 around the 3 matching rows...

Show sample data, sample expected output, and the code you already tried. — dfundako, Dec 01 '16 at 20:19
Check this question: [Generating a series of dates](http://stackoverflow.com/questions/14105018/generating-a-series-of-dates). It has answers how to generate series. Use `LEFT JOIN` with your data to get time with no corresponding row in your data. If it works for you I could post it as an answer. — Adam, Dec 01 '16 at 20:53
Oh my... it's so simple... I just had my tables the wrong way round >.< I'm now getting all of the blanks aswell, so I just need to figure out the date too, either by putting in every date and 15 minute interval for the past 2 years, or something else! Thanks, post it as an answer :) — Alphaturian, Dec 01 '16 at 21:00
You commented below that the data is persisted in a mysql database do you also work with a mysql database or which [tag:DBMS] are you using? — surfmuggle, Dec 01 '16 at 21:27

score 0 · Answer 1 · answered Dec 01 '16 at 20:23

0

Is it possible to write the total calls every 15min into your database?

Than you could use the derivative between two datapoints for the delta.

answered Dec 01 '16 at 20:23

Christoph Bluoss

359
1
9

Unfortunately not, the table is tightly controlled by external provider, it's in MYSQL originally, have got it importing into a seperate dbase in SQL so I can work with it. - But I have no control over the original software/database. – Alphaturian Dec 01 '16 at 20:35

score 0 · Accepted Answer · edited May 23 '17 at 11:45

0

In question about Generating a series of dates you will find a method to generate a series of dates with given interval.

You can LEFT JOIN your data with generated series to get all desired times, even when there is no corresponding entry in your data.

edited May 23 '17 at 11:45

Community

1
1

answered Dec 01 '16 at 21:15

Adam

5,403
6
31
38

score 0 · Answer 3 · answered Dec 01 '16 at 21:22

What about something like this

CREATE TABLE #Calls (id int              
              , CallStart datetime 
              , CallDuration int -- in seconds              
              , EmployeeName nvarchar(35) COLLATE SQL_Latin1_General_CP1_CS_AS);


CREATE TABLE #Timeslots (id int              
              , TimeSlotLower datetime 
              , TimeSlotUpper datetime);

Now we enter some test data

INSERT INTO #Calls (id, CallStart,  CallDuration, EmployeeName) VALUES 
        (1, '2016-07-18 07:00:02', 150, 'John'),
        (2, '2016-07-18 07:07:35', 450, 'John'),        
        (3, '2016-07-18 07:01:01', 70, 'Anne'),
        (4, '2016-07-18 07:14:59', 350, 'Bob'), -- no calls between 7:15 and 7:30
        (5, '2016-07-18 07:30:01', 91, 'John'),
        (6, '2016-07-18 07:32:27', 470, 'Anne'),
        (7, '2016-07-18 07:44:48', 350, 'Bob');


INSERT INTO #Timeslots (id, TimeSlotLower,  TimeSlotUpper) VALUES 
        (1, '2016-07-18 07:00:00', '2016-07-18 07:14:59'),
        (1, '2016-07-18 07:15:00', '2016-07-18 07:29:59'),
        (1, '2016-07-18 07:30:00', '2016-07-18 07:44:59'),
        (1, '2016-07-18 07:45:00', '2016-07-18 07:59:59'),
        (1, '2016-07-18 08:00:00', '2016-07-18 08:14:59');

And finally we join both tables

SELECT c.EmployeeName, Avg(c.CallDuration) as CallDuration_Avg
       , Count(c.id) as NumberOfCalls
       , t.TimeSlotLower, t.TimeSlotUpper
   FROM #Timeslots t
      LEFT JOIN #Calls c
      ON t.TimeSlotLower <= c.CallStart 
         AND c.CallStart <= t.TimeSlotUpper
   Group By c.EmployeeName, t.TimeSlotLower, t.TimeSlotUpper
   ORDER By t.TimeSlotLower

The result looks like this

Adding blank rows where data hasn't been recorded to fit pattern

3 Answers3