I have a table with three columns:

Name Date Action
Fred Today this
Fred Today this
Fred Today that
Wilma Today this


I want to sort by name and by date, but only if 'this' was done more
than once on the same day, and not if 'that' was done on the same
day,They have to perform this more than once, and they cannot have
performed that on the same day.


Thanks,
Harry

Now what possible actions might this/that be?

This: drive to bowling alley
That: Feed Dino

Looks very much like a homework assignment to me...

Harry schreef op 23-08-2011 15:49:

On Aug 23, 1:41*pm, Henk van den Berg <m... (AT) myplace (DOT) net> wrote:
Not a homework question. Clinical research. A patient may have one or
more stents inserted during a procedure. He may also have a stent
taken out. I am looking for patients who had more than one stent
inserted during a procedure, and who did not have a stent taken out
during the same procedure.

harry

Harry wrote:
What do you want to display when the data actually contains rows that meet
your requirement? All the rows for that name and date? Or a single row for
that name and date?
Why don't you try again. Show us some sample data that includes data that
meets your requirements and then show us what you want the desired query to
return. The key being "show" - don't just describe the desired result: show
it to us.

On Aug 23, 2:11*pm, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
The raw data would be:

Name Date Proc
Fred 1 stent
Fred 1 stent
Fred 1 unstent
Wilma 2 stent
Wilma 2 stent
Barney 2 stent
Barnet 2 stent
Barney 2 stent
Barney 2 unstent
....


The query would return

Wilma 2 stent
Wilma 2 stent


Because Fred and Barney also had a stent pulled on the same day they
had more than one stent inserted.

HTH
Harry

Harry wrote:

;WITH q AS (
SELECT Name,Date
,SUM(CASE WHEN Proc='stent' THEN 1 ELSE 0 END) stents
,SUM(CASE WHEN Proc='unstent' THEN 1 ELSE 0 END) unstents
FROM tablename
GROUP BY Name, Date)
,q2 AS (
SELECT Name,Date FROM q WHERE stents>1 AND unstents=0)
SELECT t.Name,t.Date,t.Proc FROM tablename t
JOIN q2 ON t.Name=q2.Name and t.Date=q2.Date

This is untested air code (obviously), solely intended to provide the
general idea.
Hopefully, you actually haven't used those reserved keywords for your column
names ...

Harry schreef op 23-08-2011 19:56:

My bad, sorry.

select * from t1
where
proc = 'stent'
and (name, date) in
(
select name,date from t1
group by name,date
having count(distinct proc) = 1
)

Yonghang Wang wrote:
That will "work" with this set of data, but fail if the data contains a
single row for a patient for a date. The requirement is to only return rows
where a patient has multiple stents on a single day, and where he also has
an unstent on the same day.

oh,yes, u'r right. I made a mistake when tried to find some shortcut. how about change the predicate to :

having count(distinct proc) = 1 and count(*) > 1