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,

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:

selecteventId, count(*)
fromeventTable
group by eventId
having count(*) = 1

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

selecteventId
fromeventTable
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:

selecteventId
from(selecteventId, count(*)
fromeventTable
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,
>>

[K, 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,

[K, 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 IS 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) NT NULL
CHECK (event_name IN ()),
event_date DATETIME DEFAULT CURRENT_TIMESTAMP NT NULL,
to be the entire daet duration,
event_status CHAR(1) DEFAULT 'P' NT 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
FRM Events
WHERE event_date = @my_event_date
AND event_status = 'P';

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

SELECT event_name, @my_event_date
FRM Events
WHERE event_date = @my_event_date
GRUP BY event_name
HAVING ( MIN(event_status <'C')
R MAX(event_status <'R'))
-- AND ****(*) = 2

The ****(*) 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 IS 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) NT NULL
CHECK (event_name IN ()),
event_date DATETIME DEFAULT CURRENT_TIMESTAMP NT NULL,
to be the entire daet duration,
event_status CHAR(1) DEFAULT 'P' NT 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
FRM Events
WHERE event_date = @my_event_date
AND event_status = 'P';

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

SELECT event_name, @my_event_date
FRM Events
WHERE event_date = @my_event_date
GRUP BY event_name
HAVING ( MIN(event_status <'C')
R MAX(event_status <'R'))
-- AND ****(*) = 2

The ****(*) 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.