Alphaturian Alphaturian - 8 days ago 4
SQL Question

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

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 attempted to create a table that has every 15 minute interval of the day - 96 rows
Attempted to do a forced join thinking that perhaps this would force in a new row, which technically it does. However the rows that do not match an existing time interval, in this case a 07:00, appears as null, so I have 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:
06:30

06:45

07:00

07:15

07:30

07:45

08:00

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 NumberOfCalls TransactionYear TransactionMonth TransactionDate TransactionHour TransactionInterval 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...

Answer

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.