Filtering Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Filtering Dates

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2009-05-21 : 17:15:43

Hello,

I have a query I am trying to write where I only want to bring back a patients records to show the date of the last visit, and the date of the next visit but none of the other past visits before the last visit. Here is my query so far:

select a.first_name,a.last_name,cast(a.date_of_birth as datetime) as PtDOB,
cast(c.appt_date as datetime) as DOS
from person a
join hiv_flowsheet_ b on a.person_id = b.person_id
join appointments c on b.person_id = c.person_id
join provider_mstr d on c.rendering_provider_id = d.provider_id
where d.description like '%applin%' and c.appt_date > getdate()

Currently my query only brings back the date of the next visit. How can I also bring back the record for the last visit and basically get both of them but in different columns? Is there a way to do this without losing one or the other? I hope I am making sense with how I am explaining this. Please let me know if I left anything out. Thanks! :)

Thanks in Advance!
Sherri

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-21 : 18:37:14

Could you provide some sample data from each table? Can a person have a last visit date and 2 scheduled next visits? Please provide sample data from each table and what result you would like to see.

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 03:46:55

sounds like this:-


select a.first_name,a.last_name,cast(a.date_of_birth as datetime) as PtDOB,
cast(MAX(CASE WHEN c.appt_date <= getdate() THEN c.appt_date ELSE NULL END) as datetime) as dateoflastvisit,
cast(MIN(CASE WHEN c.appt_date > getdate() THEN c.appt_date ELSE NULL END) as datetime) as dateofnextvisit
from person a
join hiv_flowsheet_ b on a.person_id = b.person_id
join appointments c on b.person_id = c.person_id
join provider_mstr d on c.rendering_provider_id = d.provider_id
where d.description like '%applin%' 
group by a.first_name,a.last_name,cast(a.date_of_birth as datetime)

sross81
Posting Yak Master

228 Posts

Posted - 2009-05-22 : 10:27:08

This works perfect! Thanks :)

quote:

Originally posted by visakh16

sounds like this:-


select a.first_name,a.last_name,cast(a.date_of_birth as datetime) as PtDOB,
cast(MAX(CASE WHEN c.appt_date <= getdate() THEN c.appt_date ELSE NULL END) as datetime) as dateoflastvisit,
cast(MIN(CASE WHEN c.appt_date > getdate() THEN c.appt_date ELSE NULL END) as datetime) as dateofnextvisit
from person a
join hiv_flowsheet_ b on a.person_id = b.person_id
join appointments c on b.person_id = c.person_id
join provider_mstr d on c.rendering_provider_id = d.provider_id
where d.description like '%applin%' 
group by a.first_name,a.last_name,cast(a.date_of_birth as datetime)

Thanks in Advance!
Sherri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 13:51:31

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources