How to get this table from that Database [message #669027] |
Thu, 29 March 2018 04:36 |
|
Nanex
Messages: 1 Registered: March 2018
|
Junior Member |
|
|
Hey guys,
I'm fairly new to PL/SQL(Oracle), but have basic knowledge in MySQL.
So, I got a SQL database with logs which looks anything like
Package ID || Location ID || Action || Timestamp
1 || A || Req || yyyy:mm:dd
2 || D || Arrive || yyyy:mm:dd
..........................................................................
As you can see, there's no way to see which package got from A to B or from A to C or something like that, from the first glance.
Only way to get this is to do it over the timestamp later on in excel with vba, which is no problem.
The package ID and location ID is more complex, of course..and there's also more actions, which are not needed.
The package ID is unique.
What I want now is to see, how many movements were done from location A to C or from A to D or from C to F and so on.
At the end of the location ID you can see, if it's an input or an output, so my table should look like
Movements || O1 || O2 || O3 || O4 || O5 || O6 ....
I1 || x || y || || e || f || s
I2 || e || g || s || || || w
I3 . . . . . . .. . . . . . . . . . . .. . . . . . . . . .. . . .
I4 . . .. . .. . . . . . . . . . . . . . . . . . . . . . . .. . .
I5 . . . . . . . . . . . . . . . . . . . . .. . . . . . .. . . . . .. .
I6
...
My Idea is to count the Movements overall on the inputs and on the outputs with the package ID.
That works in a way, I get two tables in PL/SQL Developer, where you can see how much movements were done in the different locations
Location || movements
I1 || 2345
I2 || 2345
I3 || 13423
.......
Location || movements
O1 || 1234234
O2 || 234523
O3 || 234523
...
My problem now is, how to connect those both tables to see how often a package got from point A to B for example?
You have any idea?
Is this even possible?
EDIT:
I don't know how to format this right, tried to get a better overview with '||'
I hope you get the idea
[mod-edit: code tags added by BB]
[Updated on: Thu, 29 March 2018 16:07] by Moderator Report message to a moderator
|
|
|
|
Re: How to get this table from that Database [message #669033 is a reply to message #669027] |
Thu, 29 March 2018 08:30 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
I have NO IDEA what you are asking or saying. It's a complete mess. I see no rows for B or C or F, so why you even mention them is beyond me.
Why don't you start all over with create table and insert statements along with a coherent description.
|
|
|