July 21, 2014 at 8:11 pm
Hello,
I’m trying to create a report which will give me a break down of how many unique vehicles have been seen between two dates via a 15 minute interval and what Lane they were seen. My current script looks like this
SELECT l.Name [Name], count(l.Name) Total, p.Created
FROM PlateReads p
inner join Lanes l on p.Lane_ID = l.ID
where LicencePlate in (Select Plate from LPRnet_MelAir_C.dbo.TempVehiclePlates)
group by Name
Name being the Lane they were in and the Total being the amount of times a unique vehicle has been seen and p.Created being the date they were seen (thats what I need the interval powered off)
Ideally the output would look like this
16/03/201408:00 to 08:15Bus Lane 15
16/03/201408:00 to 08:15Elevated Road150
16/03/201408:00 to 08:15Public Pickup75
16/03/201408:15 to 08:30Bus Lane 13
16/03/201408:15 to 08:30Elevated Road120
16/03/201408:15 to 08:30Public Pickup55
All the way to 12/04/2014
I’ve got it so it says Lane and Count just can’t get the interval part
July 21, 2014 at 9:16 pm
.Netter (7/21/2014)
Hello,I’m trying to create a report which will give me a break down of how many unique vehicles have been seen between two dates via a 15 minute interval and what Lane they were seen. My current script looks like this
SELECT l.Name [Name], count(l.Name) Total, p.Created
FROM PlateReads p
inner join Lanes l on p.Lane_ID = l.ID
where LicencePlate in (Select Plate from LPRnet_MelAir_C.dbo.TempVehiclePlates)
group by Name
Name being the Lane they were in and the Total being the amount of times a unique vehicle has been seen and p.Created being the date they were seen (thats what I need the interval powered off)
Ideally the output would look like this
16/03/201408:00 to 08:15Bus Lane 15
16/03/201408:00 to 08:15Elevated Road150
16/03/201408:00 to 08:15Public Pickup75
16/03/201408:15 to 08:30Bus Lane 13
16/03/201408:15 to 08:30Elevated Road120
16/03/201408:15 to 08:30Public Pickup55
All the way to 12/04/2014
I’ve got it so it says Lane and Count just can’t get the interval part
It would be extremely helpful if we had some decent amount of test data to check our code suggestions against. Any chance of you attaching a zipped file with a couple of hours of data in it in a readily consumable format? Please see the first link in my signature line below under "Helpful links" for how to make the data "readily consumable". Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 21, 2014 at 9:25 pm
I can upload anything to the internet within working hours im afraid really strict rules here 🙁
July 21, 2014 at 11:25 pm
.Netter (7/21/2014)
I can upload anything to the internet within working hours im afraid really strict rules here 🙁
I assume you mean "I can't upload anything...". Understood, appreciated, and I was afraid you'd say that.
What do you want to do if there's no activity for any given lane for a 15 minute period? List the lane for the missing period or just no list the missing period?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 22, 2014 at 12:04 am
List the lane for the missing period regardless of any activity.
Thanks
July 22, 2014 at 12:12 pm
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;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply