Transitive SQL query on same table
- by MiKu
Hey. consider d following table and data...
in_timestamp | out_timestamp | name | in_id | out_id | in_server | out_server | status
timestamp1 | timestamp2 | data1 |id1 | id2 | others-server1 | my-server1 | success
timestamp2 | timestamp3 | data1 | id2 | id3 | my-server1 | my-server2 | success
timestamp3 | timestamp4 | data1 | id3 | id4 | my-server2 | my-server3 | success
timestamp4 | timestamp5 | data1 | id4 | id5 | my-server3 | others-server2 | success
the above data represent log of a execution flow of some data across servers.
e.g. some data has flowed from some 'outside-server1' to bunch of 'my-servers' and finally to destined 'others-server2'.
Question :
1) I need to give this log in representable form to client where he doesn't need to know anything about the bunch of 'my-servers'. All i am supposed to give is timestamp of the data entered my infrastructure and when it left; drilling down to following info.
in_timestamp (of 'others_server1' to 'my-server1')
out_timestamp (of 'my-server3' to 'others-server2')
name
status
I want to write sql for the same! Can someone help?
NOTE : there might not be 3 'my-servers' all the time. It differs from situation to situation. e.g. there might be 4 'my-server' involved for, say, data2!
2) Are there any other alternatives to SQL? I mean stored procs/etc?
3) Optimizations? (The records are huge in number! As of now, it is around 5 million a day. And we are supposed to show records that are upto a week old.)
In advance, THANKS FOR THE HELP! :)
