comparing 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)

comparing dates

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-06-03 : 12:48:59

Hello,

I have some code that is trying to first find the first date of service that a person visited. Using the min function is not working. Is there another function? Right now I am still getting multiple rows for each person instead of just the first visit.

Then I want to be able to compare the first date of service to the date of birth and determine if the patient first visited before they were 2 years old. I can't figure out how to do this.

select distinct b.person_id,b.date_of_birth,min(a.enc_timestamp)
from patient_encounter a
join person b on a.person_id = b.person_id
where b.date_of_birth >= '20060301' and b.date_of_birth <= '20060531'
group by b.person_id,b.date_of_birth,a.enc_timestamp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 12:59:24

Try like this:-

select b.person_id,b.date_of_birth,min(a.enc_timestamp)
from patient_encounter a
join person b on a.person_id = b.person_id
where b.date_of_birth >= '20060301' and b.date_of_birth <= '20060531'
group by b.person_id,b.date_of_birth

sross81
Posting Yak Master

228 Posts

Posted - 2008-06-03 : 13:14:35

That works to remove the duplicates.

How can I compare to see if the first date of service was before the patient's second birthday? I just can't seem to decide how to get started?

quote:

Originally posted by visakh16

Try like this:-

select b.person_id,b.date_of_birth,min(a.enc_timestamp)
from patient_encounter a
join person b on a.person_id = b.person_id
where b.date_of_birth >= '20060301' and b.date_of_birth <= '20060531'
group by b.person_id,b.date_of_birth

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 13:25:33

May be this:-

SELECT *
FROM
(
select b.person_id,b.date_of_birth,
min(a.enc_timestamp) AS firstservicedate
from patient_encounter a
join person b on a.person_id = b.person_id
where b.date_of_birth >= '20060301' and b.date_of_birth <= '20060531'
group by b.person_id,b.date_of_birth)tmp
WHERE CASE WHEN MONTH(date_of_birth)>MONTH(firstservicedate)
       OR (MONTH(date_of_birth)=MONTH(firstservicedate)
        AND   DAY(date_of_birth)>DAY(firstservicedate))
     THEN YEAR(firstservicedate)-YEAR(date_of_birth)-1
     ELSE YEAR(firstservicedate)-YEAR(date_of_birth)
END <2

sross81
Posting Yak Master

228 Posts

Posted - 2008-06-03 : 13:31:12

Ok that looks good. Thanks!

quote:

Originally posted by visakh16

May be this:-

SELECT *
FROM
(
select b.person_id,b.date_of_birth,
min(a.enc_timestamp) AS firstservicedate
from patient_encounter a
join person b on a.person_id = b.person_id
where b.date_of_birth >= '20060301' and b.date_of_birth <= '20060531'
group by b.person_id,b.date_of_birth)tmp
WHERE CASE WHEN MONTH(date_of_birth)>MONTH(firstservicedate)
       OR (MONTH(date_of_birth)=MONTH(firstservicedate)
        AND   DAY(date_of_birth)>DAY(firstservicedate))
     THEN YEAR(firstservicedate)-YEAR(date_of_birth)-1
     ELSE YEAR(firstservicedate)-YEAR(date_of_birth)
END <2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources