Help with Query

Question

Help with Query

SQLNeophyte

SSC Enthusiast

Points: 158
More actions
November 4, 2010 at 12:01 pm

#242618

I have three tables, persons ,cases and caseActivities.
Each person have a case and each case have different case activities with different dates.
Now iam trying to query only those person's case activities where their last activity date is less than 10/01/2009.
Below is the query iam trying to use: iam not getting the desired output, it is even giving me the persons profile whose last case activity datetime is greater than 10/01/2009. and i need the below mentioned data from the three tables.
Persons Cases CaseActivities
First Name
Lastname
Participantid Participantid
CaseId CaseId
Case_Status Activity_datetime
Activity
select p.first.name,p.last.name,c.case_status,c.program.type,c.counselor,ca.activity_datetime,ca.activity
from participants p
inner join cases c on p.participant_id = c.participant_id
inner join case_activities ca on c.case_id = ca.case_id
where c.case_status = 'active' and
(select top 1 ca.activity_datetime from case_activities ca
where c.case_id = ca.case_id and c.case_status = 'active') < '10/01/2009' ;
Any help greatly appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Your select top 1 clause lacks an ORDER BY. Therefore it'll return the first RANDOM row from your case_activities table.

I guess you'd need an ORDER BY ca.activity_datetime DESC.

As a side note: I vote against comparing to date formats like the one you're using (10/01/2009). This format depends of the DATEFORMAT setting. Try to set DATEFORMAT to dmy and see the effect... I'd recommend using the YYYYMMDD notation since this is unrelated to the DATEFORMAT setting ('20091001').

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

It seems to be that the subquery is a bit extraneous - or am I missing something there.

select p.first.name,p.last.name,c.case_status,c.program.type,c.counselor,ca.activity_datetime,ca.activity

from participants p

inner join cases c on p.participant_id = c.participant_id

inner join case_activities ca on c.case_id = ca.case_id

where c.case_status = 'active' and

ca.activity_datetime < '10/01/2009'

Order by ca.activity_datetime ;

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

LutzM SSC Guru Points: 107049 More actions · Answer 3

I think because of last activity date is less than 10/01/2009. the simple join won't work.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Then this

with lastactivity as (

select ca.case_id as case_id,max(ca.activity_datetime) as activity_datetime

From case_activities ca

Inner Join Cases c

on c.case_id = ca.case_id

where c.case_id = ca.case_id

and c.case_status = 'active'

and ca.activity_datetime < '10/01/2009'

Group by ca.case_id

)

select p.first.name,p.last.name,c.case_status,c.program.type,c.counselor,ca.activity_datetime,ca.activity

from participants p

inner join cases c on p.participant_id = c.participant_id

inner join case_activities ca on c.case_id = ca.case_id

Inner join lastactivity la

On la.case_id = c.case_id

where c.case_status = 'active'

and la.activity_datetime < '10/01/2009'

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SanDroid SSChampion Points: 10068 More actions · Answer 5

A Max and Group by with the Date Filter on the Join should do the trick.

select p.first.name,p.last.name,c.case_status,c.program.type,c.counselor,MAX(ca.activity_datetime) as activity_datetime,ca.activity

from participants p

inner join cases c on p.participant_id = c.participant_id

inner join case_activities ca on c.case_id = ca.case_id and ca.activity_datetime < '10/01/2009'

where c.case_status = 'active'

Group by p.first.name, p.last.name, c.case_status, c.program.type,c.counselor,ca.activity

Let me know how that works. 😎

SQLNeophyte SSC Enthusiast Points: 158 More actions · Answer 6

Hi Sandroid,

your query is not giving the desired output.

i was able to get the last Date Accurately, using the MAX, but i need the CaseActivity on the Same Date.

Currently the output is 2 differnt case Activities with the Max date, the o/p should be Max date with the Case Activity on that date.

Thank u

GSquared SSC Guru Points: 260824 More actions · Answer 7

Use Jason's (Cirque's) solution. That filters it the right way for that kind of thing.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SanDroid SSChampion Points: 10068 More actions · Answer 8

SQLNeophyte (11/4/2010)
Hi Sandroid,
Currently the output is 2 differnt case Activities with the Max date, the o/p should be Max date with the Case Activity on that date.

Then you will have to use the WITH clause solution Cirque posted.