ok...my thoughts,,,warts n' all 😀

first some sample data I've made up that I hope is somewhat similar to your situation

use [tempdb]

GO

SET NOCOUNT ON

/* build some example tables*/

IF OBJECT_ID('tempdb..Tally') IS NOT NULL DROP TABLE tempdb..Tally

IF OBJECT_ID('tempdb..LaneExample') IS NOT NULL DROP TABLE tempdb..LaneExample

IF OBJECT_ID('tempdb..TempVehiclePlates') IS NOT NULL DROP TABLE tempdb..TempVehiclePlates

IF OBJECT_ID('tempdb..PlateReadExample') IS NOT NULL DROP TABLE tempdb..PlateReadExample

CREATE TABLE Tally (N INT CONSTRAINT NPK PRIMARY KEY CLUSTERED (N))

;

WITH

L0 AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows

L1 AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows

L2 AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B), -- 16 rows

L3 AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows

L4 AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 rows

L5 AS(SELECT 1 AS C FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296 rows

Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)

INSERT INTO Tally

SELECT TOP 200000 N-1 FROM Nums ORDER BY N;

CREATE TABLE [dbo].[LaneExample](

[LID] [int] NULL,

[L_Name] [varchar](50) NULL

)

GO

INSERT INTO [laneexample] ([LID],[L_Name])VALUES(1,'Bus Lane')

INSERT INTO [laneexample] ([LID],[L_Name])VALUES(2,'Elevated Road')

INSERT INTO [laneexample] ([LID],[L_Name])VALUES(3,'Public Pickup')

INSERT INTO [laneexample] ([LID],[L_Name])VALUES(4,'Highway')

INSERT INTO [laneexample] ([LID],[L_Name])VALUES(5,'Lowway')

INSERT INTO [laneexample] ([LID],[L_Name])VALUES(6,'Slowway')

INSERT INTO [laneexample] ([LID],[L_Name])VALUES(7,'Fastway')

INSERT INTO [laneexample] ([LID],[L_Name])VALUES(8,'Suburban')

INSERT INTO [laneexample] ([LID],[L_Name])VALUES(9,'OffRoad')

INSERT INTO [laneexample] ([LID],[L_Name])VALUES(10,'Unclassified')

CREATE CLUSTERED INDEX [CIX_LE] ON [dbo].[LaneExample]

([LID] ASC)

SELECT N

INTO TempVehiclePlates

FROM Tally

WHERE (N % 8 = 0)

CREATE CLUSTERED INDEX [CIX_TVP] ON [dbo].[TempVehiclePlates]

([N] ASC)

DECLARE @NumberOfRows INT

, @StartDate DATETIME

, @EndDate DATETIME

, @Days INT

SELECT @NumberOfRows = 1000000

, @StartDate = '2014-03-01' /*Inc*/

, @EndDate = '2014-04-01' /*Exc*/

, @Days = DATEDIFF(dd, @StartDate, @EndDate)

SELECT TOP (@NumberOfRows)

PlateID = 1 + CAST(Abs(Checksum(Newid()) % 100000) AS INT)

, LaneID = 1 + CAST(Abs(Checksum(Newid()) % 10) AS INT)

, readdate = RAND(CHECKSUM(NEWID())) * @Days + @StartDate

INTO PlateReadExample

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

CROSS JOIN sys.all_columns ac3

CREATE CLUSTERED INDEX [CIX_jls] ON [dbo].[PlateReadExample]

([PlateID] ASC,[LaneID] ASC)

SET NOCOUNT OFF

now a possible solution.......

IF OBJECT_ID('tempdb..#allsegs') IS NOT NULL DROP TABLE #allsegs

SET STATISTICS TIME , IO ON;

DECLARE @Date_Start AS DATETIME

, @Date_End AS DATETIME

SET @Date_Start = (SELECT CONVERT(date, MIN(readdate)) FROM PlateReadExample)

SET @Date_End = (SELECT CONVERT(date, MAX(readdate)) FROM PlateReadExample)

SELECT

DATEADD(minute , t.n , @date_start) AS dtsegs

, l.LID

, l.L_Name

INTO #allsegs

FROM Tally AS t CROSS JOIN LaneExample AS l

WHERE DATEADD(minute , t.n , @date_start) <= DATEADD(minute , 1425 , @date_end)

AND (t.n) % 15 = 0

CREATE CLUSTERED INDEX [CIX_allsegs] ON [dbo].[#allsegs]

([dtsegs] ASC,[LID] ASC)

;WITH preformat as (

SELECT

DATEADD(minute , DATEDIFF(minute , 0 , pre.readdate) / 15 * 15 , 0) AS tds

, pre.LaneID

, COUNT(pre.PlateID) AS cnt

FROM PlateReadExample AS pre INNER JOIN

TempVehiclePlates AS tvp ON pre.PlateID = tvp.N

GROUP BY

DATEADD(minute , DATEDIFF(minute , 0 , pre.readdate) / 15 * 15 , 0)

, pre.LaneID

)

SELECT

CONVERT(varchar(10) , a.dtsegs , 103) + ' '

+ CONVERT(varchar(5) , a.dtsegs , 108) + ' to '

+ CONVERT(varchar(5) , DATEADD(minute , 15 , a.dtsegs) , 108) AS datetimeseg

, a.L_Name

, pf.cnt

FROM #allsegs AS a LEFT OUTER JOIN

preformat AS pf ON a.LID = pf.LaneID

AND a.dtsegs = pf.tds

ORDER BY a.dtsegs , a.LID

SET STATISTICS TIME , IO OFF;