Express business logic within Query statement

Vernon · #1 (**permalink**) 11-29-08, 20:34

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.

Thrasymachus · #2 (**permalink**) 11-30-08, 11:40

what have you tried so far and what is wrong with the approach you have taken?

dportas · #3 (**permalink**) 11-30-08, 11:40

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.

Vernon · #4 (**permalink**) 11-30-08, 13:29

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.

Thrasymachus · #5 (**permalink**) 11-30-08, 15:43

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

Vernon · #6 (**permalink**) 12-01-08, 00:04

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.

Thrasymachus · #7 (**permalink**) 12-01-08, 08:52

if that's not right, what's wrong with it. and if all of your requirements are not fullfilled, please report to this thread...

Read this first! FAQ for DBForums Microsoft SQL Server forum

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".

Vernon · #8 (**permalink**) 12-01-08, 19:39

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.

Thrasymachus · #9 (**permalink**) 12-02-08, 07:38

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

Vernon · #10 (**permalink**) 12-02-08, 19:44

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech