SEO

Hi,

We have a bunch of events that take place everyday. At the end of each
day we want to find out all the events that have not been completed.

This is the structure of the table (eventId, eventName, status)


1 Event1 P
1 Event1 R
1 Event1 C
2 Event2 P
2 Event2 R
2 Event2 C
3 Event3 P
3 Event3 R
3 Event3 C
....
...
...


I am trying to write a query that will return me all events that dont
status R or C. I know how to do it with cursor logic, and using
various temp tables, but I am sure there is something in set logic that
could be applied here.

select id
from eventTable e
where e.date = '20060206'
group by eventId
having count(*) < 3

The above would give me all entries that have less than 3 events
associated w/them. But I also watned to check that the three status'
that it has are P, R, and C. thats where I am stuck.. any ideas?

Help is much aprpeciated.

Cheers,

dufffman@gmail.com wrote:
> Hi,
>
> We have a bunch of events that take place everyday. At the end of
> each day we want to find out all the events that have not been
> completed.
>
> This is the structure of the table (eventId, eventName, status)
>
>
> 1 Event1 P
> 1 Event1 R
> 1 Event1 C
> 2 Event2 P
> 2 Event2 R
> 2 Event2 C
> 3 Event3 P
> 3 Event3 R
> 3 Event3 C
> ...
> ..
> ..
>
>
> I am trying to write a query that will return me all events that dont
> status R or C. I know how to do it with cursor logic, and using
> various temp tables, but I am sure there is something in set logic
> that could be applied here.

select *
from eventTable e1
where not exist (select * from eventTable 2 where e1.eventId = e2.eventId
and status = 'R')
and not exist (select * from eventTable 2 where e1.eventId = e2.eventId and
status = 'C')

> select id
> from eventTable e
> where e.date = '20060206'
> group by eventId
> having count(*) < 3
>
> The above would give me all entries that have less than 3 events
> associated w/them. But I also watned to check that the three status'
> that it has are P, R, and C. thats where I am stuck.. any ideas?

But how? Since count(*) < 3 you can only have two status'.

But it sounds to me like you will need some kind of self-join, maybe along
these lines:

select
from eventTable e1
join eventTable e2
on e1.eventId = e2.eventId
join eventTable e3
on e3.eventId = e2.eventId
where e1. status = 'P'
and e2.status = 'R'
and e2.status ='C'
and e1.date = '20060206'


--
Kristian Damm Jensen

Kristian Damm Jensen wrote:
> dufffman@gmail.com wrote:
> > Hi,
> >
> > We have a bunch of events that take place everyday. At the end of
> > each day we want to find out all the events that have not been
> > completed.
> >
> > This is the structure of the table (eventId, eventName, status)
> >
> >
> > 1 Event1 P
> > 1 Event1 R
> > 1 Event1 C
> > 2 Event2 P
> > 2 Event2 R
> > 2 Event2 C
> > 3 Event3 P
> > 3 Event3 R
> > 3 Event3 C
> > ...
> > ..
> > ..
> >
> >
> > I am trying to write a query that will return me all events that dont
> > status R or C. I know how to do it with cursor logic, and using
> > various temp tables, but I am sure there is something in set logic
> > that could be applied here.
>
> select *
> from eventTable e1
> where not exist (select * from eventTable 2 where e1.eventId = e2.eventId
> and status = 'R')
> and not exist (select * from eventTable 2 where e1.eventId = e2.eventId and
> status = 'C')
>
> > select id
> > from eventTable e
> > where e.date = '20060206'
> > group by eventId
> > having count(*) < 3
> >
> > The above would give me all entries that have less than 3 events
> > associated w/them. But I also watned to check that the three status'
> > that it has are P, R, and C. thats where I am stuck.. any ideas?
>
> But how? Since count(*) < 3 you can only have two status'.
>

Precisely.. I want all entries that have 1 or 2 status' in it. Thus
all entries that are not in the completed state yet.


> But it sounds to me like you will need some kind of self-join, maybe along
> these lines:
>

I think this is something like what I was looking for. Which leads me
to my next quesetion. This would give me all the events that are good
(meaning they had entered the pending state, running state and are now
in the complete state).

So What i really need is something like..

All events
MINUS
select
from eventTable e1
join eventTable e2
on e1.eventId = e2.eventId
join eventTable e3
on e3.eventId = e2.eventId
where e1. status = 'P'
and e2.status = 'R'
and e2.status ='C'
and e1.date = '20060206'

not very good at sql syntax.. any ideas?

Thanks,

> select
> from eventTable e1
> join eventTable e2
> on e1.eventId = e2.eventId
> join eventTable e3
> on e3.eventId = e2.eventId
> where e1. status = 'P'
> and e2.status = 'R'
> and e2.status ='C'
> and e1.date = '20060206'
>
>
> --
> Kristian Damm Jensen

Some assumptions:

1 - an eventId will have 1, 2 or 3 records in the eventTable

1a - if there is only one record then status will always be 'P'

1b - if there are 2 records then status will always be 'P' and 'R'

1c - if there are 3 records then status will always be 'P', 'R' and 'C'

[In other words, you always start with a 'P', then add an 'R', then add a 'C'.]

2 - you only want the eventId's that have one record in eventTable (ie,
they only have a status of 'P' with no associated records having a status
of 'R' or 'C')

-----------------

First idea:

select eventId, count(*)
from eventTable
group by eventId
having count(*) = 1

If you don't like/want the 'count(*)' in the output list you can try:

select eventId
from eventTable
group by eventId
having count(*) = 1

The problem with this query is that you're depending on Sybase's
un-Ansi-like behaviour to return the values you want.

Another alternative, if you're running ASE 12.5.1+, is to use a derived table:

select eventId
from (select eventId, count(*)
from eventTable
group by eventId
having count(*) = 1) derived_table



dufffman@gmail.com wrote:

> Hi,
>
> We have a bunch of events that take place everyday. At the end of each
> day we want to find out all the events that have not been completed.
>
> This is the structure of the table (eventId, eventName, status)
>
>
> 1 Event1 P
> 1 Event1 R
> 1 Event1 C
> 2 Event2 P
> 2 Event2 R
> 2 Event2 C
> 3 Event3 P
> 3 Event3 R
> 3 Event3 C
> ...
> ..
> ..
>
>
> I am trying to write a query that will return me all events that dont
> status R or C. I know how to do it with cursor logic, and using
> various temp tables, but I am sure there is something in set logic that
> could be applied here.
>
> select id
> from eventTable e
> where e.date = '20060206'
> group by eventId
> having count(*) < 3
>
> The above would give me all entries that have less than 3 events
> associated w/them. But I also watned to check that the three status'
> that it has are P, R, and C. thats where I am stuck.. any ideas?
>
> Help is much aprpeciated.
>
> Cheers,
>

And if none of this gives you what you're looking for, please post back
with a set of sample raw data and what you would expect as a result set
from your proposed query.

(Make sure you include some raw data that will show up in the result set,
and some raw data that won't show up in the result set.)

Mark A. Parsons wrote:

> Some assumptions:
>
> 1 - an eventId will have 1, 2 or 3 records in the eventTable
>
> 1a - if there is only one record then status will always be 'P'
>
> 1b - if there are 2 records then status will always be 'P' and 'R'
>
> 1c - if there are 3 records then status will always be 'P', 'R' and 'C'
>
> [In other words, you always start with a 'P', then add an 'R', then add
> a 'C'.]
>
> 2 - you only want the eventId's that have one record in eventTable (ie,
> they only have a status of 'P' with no associated records having a
> status of 'R' or 'C')
>
> -----------------
>
> First idea:
>
> select eventId, count(*)
> from eventTable
> group by eventId
> having count(*) = 1
>
> If you don't like/want the 'count(*)' in the output list you can try:
>
> select eventId
> from eventTable
> group by eventId
> having count(*) = 1
>
> The problem with this query is that you're depending on Sybase's
> un-Ansi-like behaviour to return the values you want.
>
> Another alternative, if you're running ASE 12.5.1+, is to use a derived
> table:
>
> select eventId
> from (select eventId, count(*)
> from eventTable
> group by eventId
> having count(*) = 1) derived_table
>
>
>
> dufffman@gmail.com wrote:
>
>> Hi,
>>
>> We have a bunch of events that take place everyday. At the end of each
>> day we want to find out all the events that have not been completed.
>>
>> This is the structure of the table (eventId, eventName, status)
>>
>>
>> 1 Event1 P
>> 1 Event1 R
>> 1 Event1 C
>> 2 Event2 P
>> 2 Event2 R
>> 2 Event2 C
>> 3 Event3 P
>> 3 Event3 R
>> 3 Event3 C
>> ...
>> ..
>> ..
>>
>>
>> I am trying to write a query that will return me all events that dont
>> status R or C. I know how to do it with cursor logic, and using
>> various temp tables, but I am sure there is something in set logic that
>> could be applied here.
>>
>> select id
>> from eventTable e
>> where e.date = '20060206'
>> group by eventId
>> having count(*) < 3
>>
>> The above would give me all entries that have less than 3 events
>> associated w/them. But I also watned to check that the three status'
>> that it has are P, R, and C. thats where I am stuck.. any ideas?
>>
>> Help is much aprpeciated.
>>
>> Cheers,
>>

[OK, just re-read the other posts ... cancelled my first post ...]

Some assumptions:

1 - an eventId will have 1, 2 or 3 records in the eventTable

1a - if there is only one record then status will always be 'P'

1b - if there are 2 records then status will always be 'P' and 'R'

1c - if there are 3 records then status will always be 'P', 'R' and 'C'

[In other words, you always start with a 'P', then add an 'R', then add a
'C'; you can't have an 'R' unless you have a 'P'; you can't have a 'C'
unless you have an 'R']

2 - you only want the eventId's that have one or two records in eventTable
(ie, they only have a status of 'P' or 'R' with no associated records
having a status of 'C')

-----------------

First idea:

select eventId, count(*)
from eventTable
group by eventId
having count(*) = 2

If you don't like/want the 'count(*)' in the output list you can try:

select eventId
from eventTable
group by eventId
having count(*) = 2

The problem with this query is that you're depending on Sybase's
un-Ansi-like behaviour to return the values you want.

Another alternative, if you're running ASE 12.5.1+, is to use a derived table:

select eventId
from (select eventId, count(*)
from eventTable
group by eventId
having count(*) = 2) derived_table

------------------------------

And if none of this gives you what you're looking for, please post back
with a set of sample raw data and what you would expect as a result set
from your proposed query.

(Make sure you include some raw data that will show up in the result set,
and some raw data that won't show up in the result set.)





dufffman@gmail.com wrote:

> Hi,
>
> We have a bunch of events that take place everyday. At the end of each
> day we want to find out all the events that have not been completed.
>
> This is the structure of the table (eventId, eventName, status)
>
>
> 1 Event1 P
> 1 Event1 R
> 1 Event1 C
> 2 Event2 P
> 2 Event2 R
> 2 Event2 C
> 3 Event3 P
> 3 Event3 R
> 3 Event3 C
> ...
> ..
> ..
>
>
> I am trying to write a query that will return me all events that dont
> status R or C. I know how to do it with cursor logic, and using
> various temp tables, but I am sure there is something in set logic that
> could be applied here.
>
> select id
> from eventTable e
> where e.date = '20060206'
> group by eventId
> having count(*) < 3
>
> The above would give me all entries that have less than 3 events
> associated w/them. But I also watned to check that the three status'
> that it has are P, R, and C. thats where I am stuck.. any ideas?
>
> Help is much aprpeciated.
>
> Cheers,
>

[OK, just re-read the other posts ... cancelled my first post ...]

Some assumptions:

1 - an eventId will have 1, 2 or 3 records in the eventTable

1a - if there is only one record then status will always be 'P'

1b - if there are 2 records then status will always be 'P' and 'R'

1c - if there are 3 records then status will always be 'P', 'R' and 'C'

[In other words, you always start with a 'P', then add an 'R', then add a
'C'; you can't have an 'R' unless you have a 'P'; you can't have a 'C'
unless you have an 'R']

2 - there are only the 3 different status values of 'P', 'R' and 'C'

3 - you only want the eventId's that have one or two records in eventTable
(ie, they only have a status of 'P' or 'R' with no associated records
having a status of 'C')

-----------------

First idea:

select eventId, count(*)
from eventTable
group by eventId
having count(*) < 3

If you don't like/want the 'count(*)' in the output list you can try:

select eventId
from eventTable
group by eventId
having count(*) < 3

The problem with this query is that you're depending on Sybase's
un-Ansi-like behaviour to return the values you want.

Another alternative, if you're running ASE 12.5.1+, is to use a derived table:

select eventId
from (select eventId, count(*)
from eventTable
group by eventId
having count(*) < 3) derived_table

------------------------------

And if none of this gives you what you're looking for, please post back
with a set of sample raw data and what you would expect as a result set
from your proposed query.

(Make sure you include some raw data that will show up in the result set,
and some raw data that won't show up in the result set.)


dufffman@gmail.com wrote:

> Hi,
>
> We have a bunch of events that take place everyday. At the end of each
> day we want to find out all the events that have not been completed.
>
> This is the structure of the table (eventId, eventName, status)
>
>
> 1 Event1 P
> 1 Event1 R
> 1 Event1 C
> 2 Event2 P
> 2 Event2 R
> 2 Event2 C
> 3 Event3 P
> 3 Event3 R
> 3 Event3 C
> ...
> ..
> ..
>
>
> I am trying to write a query that will return me all events that dont
> status R or C. I know how to do it with cursor logic, and using
> various temp tables, but I am sure there is something in set logic that
> could be applied here.
>
> select id
> from eventTable e
> where e.date = '20060206'
> group by eventId
> having count(*) < 3
>
> The above would give me all entries that have less than 3 events
> associated w/them. But I also watned to check that the three status'
> that it has are P, R, and C. thats where I am stuck.. any ideas?
>
> Help is much aprpeciated.
>
> Cheers,
>

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

What you did post looks screwed up. Where is the date of these events?
Your data elements are also wrong according to ISO-11179 rules. What
kind of status? Why is an event_id needed at all (surely you did not
use an IDENTITY column!!)? etc.

Here is a guess at a correct DDL:

CREATE TABLE Events
(event_name CHAR(10) NOT NULL
CHECK (event_name IN (..)),
event_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, --understood
to be the entire daet duration,
event_status CHAR(1) DEFAULT 'P' NOT NULL
CHECK (event_status IN ('P', 'R', 'C')),
PRIMARY KEY (event_name, event_date));

>> I am trying to write a query that will return me all events that don't [have] status [both] R or C <<

That leaves only 'P' according ot your vague narrative.

SELECT event_name, @my_event_date
FROM Events
WHERE event_date = @my_event_date
AND event_status = 'P';

Or did you mean that events that lack either 'R' or 'C' codes, but have
'P'?

SELECT event_name, @my_event_date
FROM Events
WHERE event_date = @my_event_date
GROUP BY event_name
HAVING ( MIN(event_status <> 'C')
OR MAX(event_status <> 'R'))
-- AND COUNT(*) = 2

The COUNT(*) is for the ('P', 'R') and ('C', 'P') cases. You did not
say what to do about ('C'), ('R', 'C') and the empty case. You can
modify this easily, tho.

It should run about 10 to 100 times faster than your cursor on large
data sets.






>>.. I know how to do it with cursor logic, and using various temp tables <<

Why?? You should never write a cursor when a query can do the job.
That is about 99.98% of the time.

>> I also wanted to check that the three statuses that it has are P, R, and C. <<

That makes no sense; if it is missing 'R' or 'C', then is cannot have
all three. You use a CHECK() constraitn to limit the possible values.

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

What you did post looks screwed up. Where is the date of these events?
Your data elements are also wrong according to ISO-11179 rules. What
kind of status? Why is an event_id needed at all (surely you did not
use an IDENTITY column!!)? etc.

Here is a guess at a correct DDL:

CREATE TABLE Events
(event_name CHAR(10) NOT NULL
CHECK (event_name IN (..)),
event_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, --understood
to be the entire daet duration,
event_status CHAR(1) DEFAULT 'P' NOT NULL
CHECK (event_status IN ('P', 'R', 'C')),
PRIMARY KEY (event_name, event_date));

>> I am trying to write a query that will return me all events that don't [have] status [both] R or C <<

That leaves only 'P' according ot your vague narrative.

SELECT event_name, @my_event_date
FROM Events
WHERE event_date = @my_event_date
AND event_status = 'P';

Or did you mean that events that lack either 'R' or 'C' codes, but have
'P'?

SELECT event_name, @my_event_date
FROM Events
WHERE event_date = @my_event_date
GROUP BY event_name
HAVING ( MIN(event_status <> 'C')
OR MAX(event_status <> 'R'))
-- AND COUNT(*) = 2

The COUNT(*) is for the ('P', 'R') and ('C', 'P') cases. You did not
say what to do about ('C'), ('R', 'C') and the empty case. You can
modify this easily, tho.

It should run about 10 to 100 times faster than your cursor on large
data sets.






>>.. I know how to do it with cursor logic, and using various temp tables <<

Why?? You should never write a cursor when a query can do the job.
That is about 99.98% of the time.

>> I also wanted to check that the three statuses that it has are P, R, and C. <<

That makes no sense; if it is missing 'R' or 'C', then is cannot have
all three. You use a CHECK() constraitn to limit the possible values.

dufffman@gmail.com wrote:
> Kristian Damm Jensen wrote:
>> dufffman@gmail.com wrote:
>>> Hi,
>>>
>>> We have a bunch of events that take place everyday. At the end of
>>> each day we want to find out all the events that have not been
>>> completed.
>>>
>>> This is the structure of the table (eventId, eventName, status)
>>>
>>>
>>> 1 Event1 P
>>> 1 Event1 R
>>> 1 Event1 C
>>> 2 Event2 P
>>> 2 Event2 R
>>> 2 Event2 C
>>> 3 Event3 P
>>> 3 Event3 R
>>> 3 Event3 C
>>> ...
>>> ..
>>> ..
>>>
>>>
>>> I am trying to write a query that will return me all events that
>>> dont status R or C. I know how to do it with cursor logic, and
>>> using various temp tables, but I am sure there is something in set
>>> logic that could be applied here.
>>
>> select *
>> from eventTable e1
>> where not exist (select * from eventTable 2 where e1.eventId =
>> e2.eventId and status = 'R')
>> and not exist (select * from eventTable 2 where e1.eventId =
>> e2.eventId and status = 'C')
>>
>>> select id
>>> from eventTable e
>>> where e.date = '20060206'
>>> group by eventId
>>> having count(*) < 3
>>>
>>> The above would give me all entries that have less than 3 events
>>> associated w/them. But I also watned to check that the three
>>> status' that it has are P, R, and C. thats where I am stuck.. any
>>> ideas?
>>
>> But how? Since count(*) < 3 you can only have two status'.
>>
>
> Precisely.. I want all entries that have 1 or 2 status' in it. Thus
> all entries that are not in the completed state yet.
>
>
>> But it sounds to me like you will need some kind of self-join, maybe
>> along these lines:
>>
>
> I think this is something like what I was looking for. Which leads me
> to my next quesetion. This would give me all the events that are good
> (meaning they had entered the pending state, running state and are now
> in the complete state).
>
> So What i really need is something like..
>
> All events
> MINUS
> select
> from eventTable e1
> join eventTable e2
> on e1.eventId = e2.eventId
> join eventTable e3
> on e3.eventId = e2.eventId
> where e1. status = 'P'
> and e2.status = 'R'
> and e2.status ='C'
> and e1.date = '20060206'

i.e. all events that are not good?

If so you are on the right track and were so from the start.

Try

select *
from eventTable e1
where e2.date = '20060206'
and not exists
(select eventID from eventTable e2
where e1.eventId = e2.eventID
group by eventID
having count(*) = 3)

The subselect will find all those that are good (my select above will too,
but theres no reason to do a triple selfjoin for that),

On the other hand, if I understand correctly the records for an event will
include the statuses (?) 'P', 'R' and 'C' and in that order, and you are
interested in those events without status 'C'. If so, it can be made even
more simple:

select *
from eventTable e1
where e1.date = '20060206'
and not exists
(select eventID from eventTable e2
where e1.eventId = e2.eventID
and e2.status = 'C')


--
Kristian Damm Jensen