Alphaturian Alphaturian - 1 year ago 59
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download