Query in SQL Server or Oracles

mrkeivan

Hi Guys,
I want to write a query in Oracle/SQL Server (doesn’t make a lot of difference)
I have 3 tables:
EMSINFO (EMSINFOID, DEVICEID, DATE, PlateNumber)
Devices (DEVICEID, INOUT)
Roads (ROADID, ROADNAME)

EMSINFO Contains the enters and exits of vehicles and it’s captured by a device
We have the device id,
And roads has the lists of all the roads in the zone
based on the INOUTwe know whether its enter or exit
Now I want to select PlateNumber, total time in the zone (enter time – exit time) and
Details of when entered and when exited in a text format in one record, because each vehicle can enter and exit multiple times in a particular day

Return Fields of store procedure:
PlateNo: Plate number
text: Enter from (road name) at (time)
Exited from (road name) at ( time)
Duration: Total Duration of being in the zone

My question is generating that text or enters and exits
I appreciate the help

Mr.K

Jorgen Andersson

There is no info on which road the device controls. Should possibly be: Devices (DEVICEID, INOUT, ROADID)

Wrong is evil and must be defeated. - Jeff Ello[^]

mrkeivan

Yes, that's right, neglected to mention it, there is a ROADIDin Device table, now do you have any solution for this problem?

Mycroft Holmes

There are a number of solutions to this, you can use a CTE or 2 queries. Select all the records using an inner join on the foreign keys and order by platenumber road and date.This should give you the reads for each plate so you can get the max/min for each plate/road and do a simple timespan function on the dates.

Never underestimate the power of human stupidity RAH

Jorgen Andersson

Try something like this:

WITH Entries AS (
SELECT PlateNumber
,DATE
,RoadName
FROM EMSINFO e
,Devices d
,Roads r
WHERE e.deviceID = d.DeviceID
AND d.RoadID = r.RoadID
AND d.InOut = 'IN'
)
,Exits AS (
SELECT e.PlateNumber
,e2.DATE EntryTime
,e2.RoadName EntryRoad
,Min(DATE) ExitTime
FROM Entries e2
,EMSINFO e
,Devices d
WHERE e2.platenumber = e.platenumber
AND e.deviceID = d.DeviceID
AND d.InOut = 'OUT'
AND e.DATE > e2.DATE
GROUP BY e.PlateNumber
,e2.DATE
,e2.RoadName
)
SELECT e.PlateNumber
,EntryTime
,EntryRoad
,ExitTime
,r.RoadName ExitRoad
FROM Exits e
,EMSINFO e2
,devices d
,Roads r
WHERE e.platenumber = e2.platenumber
AND e.ExitTime = e2.DATE
AND e2.DeviceID = d.DeviceID
AND d.RoadID = r.RoadID

There are probably better implementations to your problem, Search for sql+islands+and+gaps[^]. "Islands and Gaps" is the name of this classic problem.

Wrong is evil and must be defeated. - Jeff Ello[^]

mrkeivan

Thanx, I will check it out