Patients who come in within 14 days

Question

Post reply

Patients who come in within 14 days

jbon007

SSChasing Mays

Points: 602
More actions
September 15, 2010 at 4:27 am

#226885

Dear All,
I'm trying to wriite sql query and i would like to know how would write the following in sql:
anyone with a crn and admdate who has a record within 14 days admdate between a 5 month period.
so far i am here:
SELECT crn, admission, admdate, admtime, disdate, admsource, admtype
FROM mfCarlRepos.dbo.admissions as table_name1
WHERE (admdate >= '2010-04-01' AND admdate <= '2010-08-31')
The above shows me all records between april and august with criteria in admdate. My next step is to show that any CRN that has admdate who has come in again within 14 days and display those records.
regards
F

Viewing 15 posts - 1 through 14 (of 14 total)

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

SELECT crn, admission, admdate, admtime, disdate, admsource, admtype

FROM mfCarlRepos.dbo.admissions as table_name1

WHERE admdate BETWEEN '2010-04-01' AND '2010-08-31'

Have you heard of between?

SELECT crn, admission, admdate, admtime, disdate, admsource, admtype

FROM mfCarlRepos.dbo.admissions as table_name1

WHERE admdate BETWEEN ( Dateadd(dd, Datediff(dd, 0, Getdate()), 0) ) AND (

Dateadd(dd, -14,

(

Dateadd(dd, Datediff(dd, 0, Getdate()), 0) )) )

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

jbon007 SSChasing Mays Points: 602 More actions · Answer 2

thank you for responding....however i don't think i've clearly explained myself.

The CRN is patient identification No. What i'm trying to do is see how many of the same patients (CRN's) have come in with the 14 days.

Here is a scenario:

I'm patient A had an operation on 15th August then i come again on the 20th august due to complications.

e.g

crn admdate

1234 19/05/2010

1234 15/08/2010*

1234 20/08/2010*

3456 01/07/2010

3456 11/08/2010*

3456 14/08/2010*

Display only these records

1234 has come in 4 days

3456 has come in 3 days

Johan Bijnens SSC Guru Points: 135404 More actions · Answer 3

If this is for homework, please mention the url as a ref 😀

use mfCarlRepos

SELECT crn

, admission

, admdate

, admtime

, disdate

, admsource

, admtype

FROM dbo.admissions as table_name1

WHERE admdate between '2010-04-01' AND '2010-08-31'

and exists ( Select *

FROM dbo.admissions as ALZDBA

WHERE ALZDBA.crn = table_name1.crn

and ALZDBA.admdate > table_name1.admdate

and ALZDBA.admdate <= dateadd(dd, 14, table_name1.admdate)

/* tell your system what you know - this may help filtering your initial indexes and reduce the working set */

and ALZDBA.admdate between '2010-04-01' AND '2010-08-31'

)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

jbon007 SSChasing Mays Points: 602 More actions · Answer 4

This is perfect,

Can you quickly go over the SQL statement. Its learning curve and learning as i go along.

Nope its not homework i'm afraid, i'm out of practice 5 years ago, helping trainee sql developer. Books only go certain far!

many thanks for your response

F

Mike01 SSChampion Points: 11412 More actions · Answer 5

This can also be done in a CTE

Setup

declare @t table (CRN int, admdate smalldatetime)

insert into @t

select 1234,'05/19/2010' union all

select 1234 ,'08/15/2010' union all

select 1234 ,'08/20/2010' union all

select 3456 ,'07/01/2010' union all

select 3456 ,'08/11/2010' union all

select 3456 ,'08/14/2010'

Code

;with cte as

(select CRN, ADMDate,

ROW_NUMBER() over (PARTITION by CRN order by CRN, ADMDate) RowNum

from @t)

select a.CRN, a.admdate

from cte a

left outer join cte b

on a.CRN = b.CRN

and a.RowNum = b.RowNum - 1

where DATEDIFF(d, a.admdate, b.admdate) <= 14

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Johan Bijnens SSC Guru Points: 135404 More actions · Answer 6

With the extra "exists" clause it checks for existance of _any_ data in the query within the brackets.

Because I'm using a Correlated Subquery (check Books Online (bol) for "Correlated Subqueries")

I had it tied to the original query by using the columns prefixed by the alias 'table_name1' of the object dbo.admissions.

You could imagine this as if it would grab a row from 'table_name1' and use that data to see if it fullfils for existance in the exists-part of your query, based on the column values of that row for the specified columns in the embeded query.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

jbon007 SSChasing Mays Points: 602 More actions · Answer 7

thank you for both responses.

i have similar query which if i wanted to calculate the 14 days from the disdate from the admdate

e.g

crn admdate DISdate

1234 19/05/2010 24/05/2010*

1234 01/06/2010* 19/06/2010

1234 20/08/2010 19/07/2010

3456 01/07/2010 19/07/2010

3456 20/07/2010 23/07/2010*

3456 24/07/2010* 30/11/2010

Display only these records

1234 has discharge on the 24th may but returned back on the 1st of june so its within 14 days

3456 has discharged on the 23rd July but returned back on the 24th july

ALZDBA, I have made a slight modification to the code

use mfCarlRepos

SELECT crn

, admission

, admdate

, admtime

, disdate

, admsource

, admtype

FROM dbo.admissions as table_name1

WHERE admdate between '2010-04-01' AND '2010-08-31'

and exists ( Select *

FROM dbo.admissions as ALZDBA

WHERE ALZDBA.crn = table_name1.crn

and ALZDBA.admdate > table_name1.admdate

and ALZDBA.admdate <= dateadd(dd, 14, table_name1.DISdate)

/* tell your system what you know - this may help filtering your initial indexes and reduce the working set */

and ALZDBA.admdate between '2010-04-01' AND '2010-08-31'

and ALZDBA.admdate <= dateadd(dd, 14, table_name1.DISdate) would changing admdate to disdate achieve the result i want.

many thanks for you help in this

Johan Bijnens SSC Guru Points: 135404 More actions · Answer 8

The easiest way to find out is to actually run the query and use the returned results to check for correctness using the subquery.

You'll only get comfortable with sql by using it.

Learn to play, play to learn

Off course, taking a course on SQL may get you there with a head start.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004504 More actions · Answer 9

Hmmm... not homework? How do you explain the remarkable similarity here...

http://www.sqlservercentral.com/Forums/Topic986540-391-1.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95461 More actions · Answer 10

Jeff Moden (9/15/2010)
Hmmm... not homework? How do you explain the remarkable similarity here...
http://www.sqlservercentral.com/Forums/Topic986540-391-1.aspx

Even the column names are identical. Seems like homework to me also.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004504 More actions · Answer 11

All these bloody "medical database" problems seem like homework to me. I know they can sometimes get a little screwy but there is just no way that the health care and medical industries can be [font="Arial Black"]THAT [/font]screwed up and with the same problems over and over and over and... 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jbon007 SSChasing Mays Points: 602 More actions · Answer 12

His one of my staff....i asked him to search on the forums. Just pot luck that he came to the same forum. I'm more of apps developer and done sql and quite rusty since i have touched it for year or two.

Jeff Moden SSC Guru Points: 1004504 More actions · Answer 13

farouqdin (9/16/2010)
His one of my staff....i asked him to search on the forums. Just pot luck that he came to the same forum. I'm more of apps developer and done sql and quite rusty since i have touched it for year or two.

Ah... yep... that would explain it. Are you all set, now?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jbon007 SSChasing Mays Points: 602 More actions · Answer 14

jbon007

SSChasing Mays

Points: 602

September 16, 2010 at 2:17 pm

#1222590

yup we got it working finally.