Number of patients in a day

Question

Number of patients in a day

Sjey

SSC-Addicted

Points: 468
More actions
June 1, 2016 at 8:01 am

#326708

DDL
(PROVIDER_ID CHAR(10),
PATIENT_ID CHAR(10),
CARE_LEVEL CHAR(10),
BEGIN_DT DATETIME,
END_DT DATETIME)
Sample Data
PROVIDER_IDPATIENT _ID CARE_LEVELBEGIN_DTEND_DT
D461624 M00001114LVL35507 2016-01-05 2016-03-05
D461624 M00001114LVL35507 2016-03-05 2016-04-04
D461624 M00001164LVL35507 2016-04-042016-05-04
D145578 M50022333LVL35507 2015-07-012015-08-30
D145578 M50022333LVL35507 2015-08-302015-09-29
D145578 M00220133LVL33507 2015-09-302015-10-30
D461624 M10045215LVL33507 2015-07-012015-08-30
D461624 M20017820LVL33507 2015-04-102015-09-29
Provider_ID is the hospital id.
I need to get the number of patients in a day, listing by each date in the span between the begin_dt and end_dt and reported by care level and provider id.
Result expected as follows:
Provider_ID Service_Date Care_Level Patient_Count
D461624 7/1/2015 LVL33507 2
D461624 7/2/2015 LVL33507 2
D461624 7/3/2015 LVL33507 2
& SO ON..

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Sjey (6/1/2016)
DDL
(PROVIDER_ID CHAR(10),
PATIENT_ID CHAR(10),
CARE_LEVEL CHAR(10),
BEGIN_DT DATETIME,
END_DT DATETIME)
Sample Data
PROVIDER_IDPATIENT _ID CARE_LEVELBEGIN_DTEND_DT
D461624 M00001114LVL35507 2016-01-05 2016-03-05
D461624 M00001114LVL35507 2016-03-05 2016-04-04
D461624 M00001164LVL35507 2016-04-042016-05-04
D145578 M50022333LVL35507 2015-07-012015-08-30
D145578 M50022333LVL35507 2015-08-302015-09-29
D145578 M00220133LVL33507 2015-09-302015-10-30
D461624 M10045215LVL33507 2015-07-012015-08-30
D461624 M20017820LVL33507 2015-04-102015-09-29
Provider_ID is the hospital id.
I need to get the number of patients in a day, listing by each date in the span between the begin_dt and end_dt and reported by care level and provider id.
Result expected as follows:
Provider_ID Service_Date Care_Level Patient_Count
D461624 7/1/2015 LVL33507 2
D461624 7/2/2015 LVL33507 2
D461624 7/3/2015 LVL33507 2
& SO ON..

Can you try to explain your desired results? Are you trying to get a row for each provider and care_level for each date?

If you could turn your sample data into something consumable it would be a lot easier to help here. Take a look at the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

pietlinden SSC Guru Points: 63503 More actions · Answer 2

Since you're kinda new here, I fixed your SQL so that it works...

use tempdb;

go

CREATE TABLE ProviderData (PROVIDER_ID CHAR(10),

PATIENT_ID CHAR(10),

CARE_LEVEL CHAR(10),

BEGIN_DT DATETIME,

END_DT DATETIME);

GO

INSERT INTO ProviderData(Provider_ID, Patient_ID, Care_level, Begin_Dt, End_Dt) VALUES (

'D461624','M00001114','LVL35507','2016-01-05','2016-03-05'),

('D461624','M00001114','LVL35507','2016-03-05','2016-04-04'),

('D461624','M00001164','LVL35507','2016-04-04','2016-05-04'),

('D145578','M50022333','LVL35507','2015-07-01','2015-08-30'),

('D145578','M50022333','LVL35507','2015-08-30','2015-09-29'),

('D145578','M00220133','LVL33507','2015-09-30','2015-10-30'),

('D461624','M10045215','LVL33507','2015-07-01','2015-08-30'),

('D461624','M20017820','LVL33507','2015-04-10','2015-09-29');

Do you mean number of patients in the hospital in a day (like a head count)? If you want something like that, I would probably use a Calendar table and "explode" the dates that way... If you dig around there are several articles here that show how to create a Calendar table.

Assuming you had a Calendar table handy, your code might look something like this... but at this point, it's conjecture.

SELECT pd.Provider_ID, pd.Patient_ID, pd.Care_Level, pd.Begin_dt, pd.End_Dt, c.TheDate

FROM ProviderData pd INNER JOIN Calendar c ON

c.TheDate>=pd.Begin_Dt AND c.TheDate<=pd.End_Dt;

Then it's just a matter of doing a count per day.

Sjey SSC-Addicted Points: 468 More actions · Answer 3

Yes, I was trying to get a head count by date.

I will use the calendar table and try this.

Appreciate your help.

BLOB EATER SSChampion Points: 14609 More actions · Answer 4

I remember this question, had it few years ago at an interview.