command implement loop
-
Hey guys I have two tables 1- Holds the cars passed (CarId, DeviceId, PassDate) 2- Holds the List of Cameras capturing each car (DeviceID, DeviceName) I want to check on a specific Date the maximum number of car passed in period(every 60 minutes, 2 hours …) So I have to use a double loop the first for each device and the second for the period.
For(everydevice)
{
For(every period in the time)
}The should look like this List
Device Name --------- Max Passed Cars
Since I’m new to oracle SQL Programming I dunno how to implement this, I appreciate it if you can help me out
-
Hey guys I have two tables 1- Holds the cars passed (CarId, DeviceId, PassDate) 2- Holds the List of Cameras capturing each car (DeviceID, DeviceName) I want to check on a specific Date the maximum number of car passed in period(every 60 minutes, 2 hours …) So I have to use a double loop the first for each device and the second for the period.
For(everydevice)
{
For(every period in the time)
}The should look like this List
Device Name --------- Max Passed Cars
Since I’m new to oracle SQL Programming I dunno how to implement this, I appreciate it if you can help me out
You can do this in a single select query, grouping by deviceID, calculating Count(carId) with a where clause on the passdate between period start and period end
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
You can do this in a single select query, grouping by deviceID, calculating Count(carId) with a where clause on the passdate between period start and period end
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
If i'm not mistaken this works when I want to get the maximum number, but here I want to see for example what is the maximum passed number of cars in every 60 minutes.
You can also add a grouping on the DatePassed column, formatted in such a way as to just display the date and hour
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
Hey guys I have two tables 1- Holds the cars passed (CarId, DeviceId, PassDate) 2- Holds the List of Cameras capturing each car (DeviceID, DeviceName) I want to check on a specific Date the maximum number of car passed in period(every 60 minutes, 2 hours …) So I have to use a double loop the first for each device and the second for the period.
For(everydevice)
{
For(every period in the time)
}The should look like this List
Device Name --------- Max Passed Cars
Since I’m new to oracle SQL Programming I dunno how to implement this, I appreciate it if you can help me out
I wish everyone was as clear in what they want.
WITH CTE AS (
SELECT CarId
,TO_CHAR(PassDate, 'YYYY-MM-DD HH24') AS hours
,TRUNC(PassDate, 'HH24') AS hours
,DeviceName
FROM cars c,devices d
WHERE c.DeviceID = d.DeviceID
)
SELECT Count(carID) Cars_passed
,hours
,devicename
FROM CTE
GROUP BY hourly
,devicename<edit>Revisited and fixed so that hours remains the correct type</edit>
Wrong is evil and must be defeated. - Jeff Ello[^]
-
You can do this in a single select query, grouping by deviceID, calculating Count(carId) with a where clause on the passdate between period start and period end
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
Chris Quinn wrote:
I'm an optoholic
Actually, you mean optiholic because optimist is spelled with an 'i', not an 'o'. ;)
The difficult we do right away... ...the impossible takes slightly longer.
-
Chris Quinn wrote:
I'm an optoholic
Actually, you mean optiholic because optimist is spelled with an 'i', not an 'o'. ;)
The difficult we do right away... ...the impossible takes slightly longer.
... but alcoholic is spelt with an o, not an i!
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
... but alcoholic is spelt with an o, not an i!
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
Touche!
The difficult we do right away... ...the impossible takes slightly longer.
-
I wish everyone was as clear in what they want.
WITH CTE AS (
SELECT CarId
,TO_CHAR(PassDate, 'YYYY-MM-DD HH24') AS hours
,TRUNC(PassDate, 'HH24') AS hours
,DeviceName
FROM cars c,devices d
WHERE c.DeviceID = d.DeviceID
)
SELECT Count(carID) Cars_passed
,hours
,devicename
FROM CTE
GROUP BY hourly
,devicename<edit>Revisited and fixed so that hours remains the correct type</edit>
Wrong is evil and must be defeated. - Jeff Ello[^]
