comparison Query

Hi guys,

I'm trying to do a comparison of 2 data sets. Basically what I want is: 'where event date from event number -24 is earlier than the event date for event number -13'

To get the eventdate for the eventno's, I have the following 2 queries:

select eventdate
from caseevent, cases
where eventno = -24

select eventdate
from caseevent, cases
where eventno = -13

So what i'm trying to say is: I want it so that the value of the first query is compared to be LESS than the value of the second query...

Any help please?

Thank you!

Do you want this for a specific event ? I did not get your point in your query, could you describe the functionality in non SQL words ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

basically eventdate and eventno are 2 different fields. The first query works out the eventdate for the event number -24, and the second query works out the eventdate for the event number -13. Then, the eventdate values for the first query have to be compared with the eventdate parts for the 2nd query.

Overall this is meant to be one query, so once I've worked out the comparison part, I will need to figure out how to make it one query. Can I somehow compare the retrieved results?

Many thanks

|||

Hi,

Can you provide data for those two tables for better understanding and also in your query you have mentioned two tables: caseevent and cases. Is there any specific relationship between those two tables.

Regards,

Kiran.Y

|||

the relationship is that once we've sorted out this comparison issue, i need to show certain values from each table, although for this situation now, they are not been joined or cross referenced...

If I run each query invidualy i do get results, but like after 3 minutes!! the closest i've got so far is:

select distinct eventdate
from caseevent, cases
where eventno = -24
and eventdate < all

(select distinct eventdate
from cases
where eventno = -13)

This gives results straight away. Just wondering if a subquery is the best method for doing this

Thanks,

|||

anyone? anything? Help please!!

EEK!!

|||

As you did not post any DDL yet, this is based on assumptions.

Maybe an exists will speed up your data retrieval:

select distinct eventdate
from caseevent A
where eventno = -24
and EXISTS
(select distinct eventdate
from cases B
where B.eventno = -13

AND A.EventDate < B.eventdate)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Cool, I didn't think about the EXIST clause. I'll try this 2mo as am off work 2day, but then logic seems right in it from the looks of it. When I do run the previous query I showed u, it was a bit slow but if the EXIST clause speeds things up then it should be better

Thank you!!

|||

Hello!!

I tried the query you sent me and I think it gives the right search facility, so that should be fine. But another part of the query is that I need to show other columns within the result.

so the query I have so far is:

select eventdate
from caseevent A
where A.eventno = -24
and EXISTS
(select distinct eventdate
from caseevent B
where B.eventno = -13

AND A.EventDate < B.eventdate)

What I'm trying to do is the following:

Report of cases where eventdate for eventno = -24 is earlier than the eventdate for eventno = -13. Need report to include IRN, eventdate for eventno = -24, eventdate for eventno = -13, and initials.

The columns available from the tables are:

Caseevent: eventdate, eventno, caseid

cases: irn, caseid

name: initials

I don't know if this should be a query or a view! I'm still fresh with stuff like Views so wouldn't know how to do this. So far the logic of comparing the results has been understood, but how do I show the other values in my final results table?

Many Thanks

|||

Hi Guys,

Any comments or ideas on my previous email please?

The main thing I'm not aware of is how to combine to columns into one final column in my final results table...

Still trying to do this as a query. I'm looking into views to see if it can be a more efficient approach.

Thanks!|||

TSQL SELECT clauses support expressions or concatenations as

SELECT FirstName + ' ' + LastName AS FullName

Does this do what you want?