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:

```
  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   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...