Processing records in select statement in Oracle DB
-
Hi guys I have a table with list of records or cards passing a place ID: int CarPlate: int PassDate: date PassType: byte > 1 enter, 2 exit I want to select the total amount of time each car has been in that place by date now this is my strategy but I cant implement it :( Imaging in a particular date a cards enters and exits are as followed: 1- enter > 2- enter > 3- < exit 4- enter > 5- < exit This particular car has entered 3 times and exited two times, I only want the enter and exit that are back to back so I need to select all the enters for all card in a particular date and then for every enter check whether there is a exit before the next enter or not and continue this to the end for each plate and then the next plate. I have problem implementing this process of going through plates and at the end for my end result should be something like this CarPlate | Date | EnterExit | Duration This EnterExit is a string of collections of enters and exits enter at ..... exit at ..... enter at ..... exit at ..... enter at ..... exit at ..... enter at ..... exit at ..... . . . Please help me out :)
-
Hi guys I have a table with list of records or cards passing a place ID: int CarPlate: int PassDate: date PassType: byte > 1 enter, 2 exit I want to select the total amount of time each car has been in that place by date now this is my strategy but I cant implement it :( Imaging in a particular date a cards enters and exits are as followed: 1- enter > 2- enter > 3- < exit 4- enter > 5- < exit This particular car has entered 3 times and exited two times, I only want the enter and exit that are back to back so I need to select all the enters for all card in a particular date and then for every enter check whether there is a exit before the next enter or not and continue this to the end for each plate and then the next plate. I have problem implementing this process of going through plates and at the end for my end result should be something like this CarPlate | Date | EnterExit | Duration This EnterExit is a string of collections of enters and exits enter at ..... exit at ..... enter at ..... exit at ..... enter at ..... exit at ..... enter at ..... exit at ..... . . . Please help me out :)
Have a look at this query:
WITH passes AS (
SELECT platenumber
,DATE
,InOut
,RoadName
,LEAD(InOut, 1, null) OVER (PARTITION BY platenumber ORDER BY date NULLS LAST) NEXT_InOut
,LEAD(RoadName, 1, null) OVER (PARTITION BY platenumber ORDER BY date NULLS LAST) NEXT_RoadName
,LEAD(Date, 1, null) OVER (PARTITION BY platenumber ORDER BY date NULLS LAST) NEXT_Date
FROM EMSINFO e
,Devices d
,Roads r
WHERE e.deviceID = d.DeviceID
AND d.RoadID = r.RoadID
)
SELECT platenumber
,DATE entrytime
,RoadName entryroad
,NEXT_RoadName exitroad
,NEXT_Date exittime
FROM passes
WHERE InOut = 'IN'
AND NEXT_InOut = 'OUT'Here I'm assuming that you have the same table structure as in the last question. Using the analytic function
LEADyou can work on the next row, so in this query I'm making sure that the row after the inwards passage is an outwards passage. Adjust to your needs. Remember that analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. So if you need to do more work on the data you have to use a CTE. (Like I did)Wrong is evil and must be defeated. - Jeff Ello[^]
-
Have a look at this query:
WITH passes AS (
SELECT platenumber
,DATE
,InOut
,RoadName
,LEAD(InOut, 1, null) OVER (PARTITION BY platenumber ORDER BY date NULLS LAST) NEXT_InOut
,LEAD(RoadName, 1, null) OVER (PARTITION BY platenumber ORDER BY date NULLS LAST) NEXT_RoadName
,LEAD(Date, 1, null) OVER (PARTITION BY platenumber ORDER BY date NULLS LAST) NEXT_Date
FROM EMSINFO e
,Devices d
,Roads r
WHERE e.deviceID = d.DeviceID
AND d.RoadID = r.RoadID
)
SELECT platenumber
,DATE entrytime
,RoadName entryroad
,NEXT_RoadName exitroad
,NEXT_Date exittime
FROM passes
WHERE InOut = 'IN'
AND NEXT_InOut = 'OUT'Here I'm assuming that you have the same table structure as in the last question. Using the analytic function
LEADyou can work on the next row, so in this query I'm making sure that the row after the inwards passage is an outwards passage. Adjust to your needs. Remember that analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. So if you need to do more work on the data you have to use a CTE. (Like I did)Wrong is evil and must be defeated. - Jeff Ello[^]
Thanx, your comments are really helpful, I have another question I have to use to functions, both return either zero or ID function1- Finds the Next enter after a particular enter function2- Checks if there is an exit between these two in my query multiple times and I want to see how I can declare a variable to hold them I wanna say
SELECT platenumber
,EnterDATE
,EnterRoad entryroad
,ExitDate exitroad: // select date from table where id = return value of function2
,ExitRoad exittime // select road from table join ... where id = return value of function2and also in 2, 3 other places, How can I declare a Variable so I dont need to use it over and over again? Regards, K1
-
Thanx, your comments are really helpful, I have another question I have to use to functions, both return either zero or ID function1- Finds the Next enter after a particular enter function2- Checks if there is an exit between these two in my query multiple times and I want to see how I can declare a variable to hold them I wanna say
SELECT platenumber
,EnterDATE
,EnterRoad entryroad
,ExitDate exitroad: // select date from table where id = return value of function2
,ExitRoad exittime // select road from table join ... where id = return value of function2and also in 2, 3 other places, How can I declare a Variable so I dont need to use it over and over again? Regards, K1
mrkeivan wrote:
How can I declare a Variable so I dont need to use it over and over again?
You use a stored procedure. This is the direct answer to your question. But I don't really get what you're after, can you elaborate?
Wrong is evil and must be defeated. - Jeff Ello[^]