Need help finding "New" patients

[mkfloque]

Hello All.
"New" patients are patients that weren't present the prior day. I'm needing a query to show how many patients were NEW on each day [each patient listed once for each service date]. Also, some patients will have multiple visits.

All advice appreciated, thanks .

Pt Name & Date/MAINAuto IDpt namedate,1smith1/1/20122smith1/2/20123smith1/3/20124jones1/3/20125jones1/4/20126jones1/5/20127garza1/2/20128garza1/3/20129garza1/4/201210garza1/5/201211smith2/2/201212smith2/3/201213smith2/4/201214smith4/16/201215smith4/17/2012

[John_G]

You haven't given much information, but something like this might be what you need:

Select Patient_ID from Visit_Table where Visit_Date = Date() and Patient_ID not in (Select Patient_ID from Visit_Table where Visit_Date = date()-1)

This list the patients who are here today, but were not there the previous day. If they were there two days ago but not yesterday, they will be in the list.

One potiential difficulty with this, though: what about weekends and holidays?

John

[mkfloque]

thank you John G. I'll try this. Fortunately, weekends & holidays won't matter.

[orange]

Also, please show your tables and relationships.

[RayMilhon]

Are you talking about Inpatients or outpatients. I am presuming Inpatients because it shouldn't matter for outpatients you would need each visit even if they came 2 days in a row.

So I would use the following

Select patient Information Where admit date = today

[mkfloque]

Hello again all,

Here's my test table. Each patient is listed once for each service date. These are INpatients.
There's no "admit date" and "discharge date" columns

As you'll see, Smith had multiple visits; i'm needing to know the first date of each visit (visit=consecutive dates with [no gaps] ).

The end query should give me # of new patients for each given day (example: 1/3/12 has 1 new patient).


Thanks again,
M

[RayMilhon]

So If Patient Sam is admitted on June 1, 2011 and Discharged June 10, 2011 He has 10 Records???

[mkfloque]

Yes; Sam would have 10 records

[John_G]

Hi -

I suggest that you need to rethink your database design. If Sam is admitted on June 1 and discharged on June 10, then that is ONE visit, not 10. Is there a reason why you have to one record for each day? If it is to track things that affect Sam during his 10-day stay, then your structure IS wrong, which is why you are having difficulty.

Please tell us more about what you are trying to do.

John

[RayMilhon]

In that case John G's suggestion will probably work for you. Although I wish you luck if you ever have to calculate length of Stay. That would be a nightmare in that database.

[mkfloque]

Actually, the data is imported from "EPIC", the program the hospital uses. EPIC lists each service date on a separate line. It's unfortunate because i know how to find "new" and "total" patients when given an admit date & discharge date on the same line/record.

[RayMilhon]

I've used Quadramed, Meditech and Cerner EPIC is the one I've never used. Although I suspect that EPIC has 2 tables 1 that lists the admit date and discharge date and a detail table that lists specific charges. For example,

A Patient is admitted on june 1, 2011 and discharged June 10, 2011

The master table will have 1 record with the patient, admit date and discharge date

Then there's a detail table that will have 1 record per charge.

June 1 Room Charge
June 1 Lab Charge
June 1 Radiology Charge
June 2 Room Charge
June 2 Lab Charge

So you're getting the data from the detail table.