Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2003
    Posts
    24

    Express business logic within Query statement

    I have a records of an equipment status table. The table schema is something like the followings:

    equipment_id, status, recorded_time,...

    I need to find the record of an equipment status which the most recent status is off. I also need to find the most recent record of the same equipment with the status on before it is off.

    How to express such business logic in query statements?

    Thanks for your helps in advance.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    what have you tried so far and what is wrong with the approach you have taken?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I expect you want an answer in SQL but you haven't told us what product or version you are using. The SQL99 RANK() or DENSE_RANK() functions may help you if they exist in your DBMS.

    You also didn't tell us what keys exist in your table. Without that information I don't want to guess at a solution.

  4. #4
    Join Date
    Jul 2003
    Posts
    24
    In the real case, there are more than one table involved. To simplify this discussion, let's assume one table only and the equipment_id is the primary key.

    Our DB admin told me the following query. Is it not right, however.

    select equipment_id from equipment_table where status='off' and recorded_time in (select max(recorded_time) from equipment_table)

    and the query statement,

    select equipment_id from equipment_table where status='off' order by recorded_time desc

    the first returned data is still not correct.
    Last edited by Vernon; 11-30-08 at 15:03.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    maybe this without knowing more...

    Code:
    select e.equipment_id, e2.recorded_time as is_off,MAX(e.recorded_time) as not_off
    from equipment_table e
    join 
    	(select equipment_id, MAX(recorded_time) as recorded_time
    	from equipment_table
    	where status = 'off' 
    	group by equipment_id) e2
    on e.equipment_id = e.equipment_id
    where e.status <> 'off' 
    and e.recorded_time < e2.recorded_time
    group by e.equipment_id, e2.recorded_time
    Last edited by Thrasymachus; 11-30-08 at 19:52.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Jul 2003
    Posts
    24
    Quote Originally Posted by Thrasymachus
    maybe this without knowing more...

    Code:
    select e.equipment_id, e2.recorded_time as is_off,MAX(e.recorded_time) as not_off
    from equipment_table e
    join 
    	(select equipment_id, MAX(recorded_time) as recorded_time
    	from equipment_table
    	where status = 'off' 
    	group by equipment_id) e2
    on e.equipment_id = e.equipment_id
    where e.status <> 'off' 
    and e.recorded_time < e2.recorded_time
    group by e.equipment_id, e2.recorded_time

    Thanks for your suggestion.

    I agree that the self-join approach might be the only solution. I have tested the above query and the result doesn't seem to be right. I need to study it more.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    if that's not right, what's wrong with it. and if all of your requirements are not fullfilled, please report to this thread...

    http://www.dbforums.com/showthread.php?t=1212452

    and read the post entitled "How to ask a question to get quick and correct answers".

    Otherwise you post is like going to the doctor and saying nothing but "it hurts".
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Jul 2003
    Posts
    24

    Smile

    I tested the query last night on my own application at home. Today, I get it tested again against our application DB at work. I don't get expected result with the test data. I am not a SQL person and not good on analyst SQL. I can't say what is wrong with the query. After some hours' work, I come out the following query:
    Code:
    select equipment_id,recorded_time from equipment_table e where e.status='off' and e.recorded_time > (select max(recorded_time) from equipment_table where status<>'off' and equipment_id=o.equipment_id );
    It returns a correct result and it is not too complicated.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    you are absolutely right. I did make a boo boo. I fixed it. But heh, yours does not fullfill all of your requirements. I thought you needed both the time it was off and the time before it was off.

    select e.equipment_id, e2.recorded_time as is_off,MAX(e.recorded_time) as not_off
    from equipment_table e
    join
    (select equipment_id, MAX(recorded_time) as recorded_time
    from equipment_table
    where status = 'off'
    group by equipment_id) e2
    on e.equipment_id = e2.equipment_id
    where e.status <> 'off'
    and e.recorded_time < e2.recorded_time
    group by e.equipment_id, e2.recorded_time
    Last edited by Thrasymachus; 12-02-08 at 08:41.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Jul 2003
    Posts
    24
    Quote Originally Posted by Thrasymachus
    you are absolutely right. I did make a boo boo. I fixed it. But heh, yours does not fullfill all of your requirements. I thought you needed both the time it was off and the time before it was off.

    select e.equipment_id, e2.recorded_time as is_off,MAX(e.recorded_time) as not_off
    from equipment_table e
    join
    (select equipment_id, MAX(recorded_time) as recorded_time
    from equipment_table
    where status = 'off'
    group by equipment_id) e2
    on e.equipment_id = e2.equipment_id
    where e.status <> 'off'
    and e.recorded_time < e2.recorded_time
    group by e.equipment_id, e2.recorded_time

    Thanks for your help. I will try it tomorrow.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •