Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Table name = Table-Name

ID     Status    description   Tracking ID  
1      Strat     Frog          1
2      Start     Dog           2
3      Process   Frog          1
4      Completed Dog           2
5      Start     Rabbit        3
6      Error     Frog          1
7      Stop      Rabbit        3
8      Start     Elephant      4
9      process   Elephant      4
10     Start     Human         5
11     Stop      Human         5
12     Start     Butterfly     6
13     completed Butterfly     6
14     start     lion          7
15     error     lion          8
16     complted  lion          8
17     start     tiger         9
18     error     tiger         9

Result should be " Frog and Elephant and tiger"

  • Start of every record(descrpition --status is Start)
  • End of every record ( status is stop or done or completed) status process is in between (their will be mulitple records with name s//y to process...ie. process 1 ...process 2...process 3 )

Query:

select *
from Table-Name
where datetime < to_date('2012/12/06:06:00:00', 'yyyy/mm/dd:hh24:mi:ss') And datetime > to_date('2012/12/04:22:00:00', 'yyyy/mm/dd:hh24:mi:ss')
And not description in (Select *
                        from Table-Name
                        where (Status like '%Complete%' or Status like '%stop%')
                        and description in (Select description
                                           from Table-Name
                                            where Status Like '%start%'));

Note:

tracking IDs may change up on error

share|improve this question
You want a result set with a single row that contains the literal string 'Frog and Elephant and tiger'? Or a result set with three rows? I'm not sure what s//y is supposed to mean. I'm not sure why tiger would be returned since it only has a start and an error row. Should we ignore the fact that you're mixing the case of your status values-- you have both Start and start as well as the misspelled Strat. "Frog" also has no Stop or Completed row, just Start, Process, and Error. – Justin Cave Dec 17 '12 at 19:53
What do you mean with Table name = Table-Name? – a_horse_with_no_name Dec 17 '12 at 20:00
Thanks for the answer this forum is very fast ...and i am new to this sql so lot of mistakes ...thanks for spelling corrections ...... """" I need 'in-complete' records out of complete records """" Lion is also a complete record – user16301 Dec 17 '12 at 20:02
select * from Table-Name where datetime < to_date('2012/12/06:06:00:00', 'yyyy/mm/dd:hh24:mi:ss') And datetime > to_date('2012/12/04:22:00:00', 'yyyy/mm/dd:hh24:mi:ss')And not description in (Select * from Table-Name where (Status like '%Complete%' or Status like '%stop%') and description in (Select description from Table-Name where Status Like '%start%'));....................................................................‌​..........................................formate of result doesnt matter – user16301 Dec 17 '12 at 20:16
Its not working ...kindly help me out ...to get the results – user16301 Dec 17 '12 at 20:16
show 1 more comment

closed as off topic by Justin Cave, Paul White, Phil, jcolebrand Dec 17 '12 at 21:04

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined in the FAQ. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about closed questions here.

Browse other questions tagged or ask your own question.