June 1, 2016 at 8:01 am
DDL
(PROVIDER_ID CHAR(10),
PATIENT_ID CHAR(10),
CARE_LEVEL CHAR(10),
BEGIN_DT DATETIME,
END_DT DATETIME)
Sample Data
PROVIDER_ID PATIENT _ID CARE_LEVEL BEGIN_DT END_DT
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
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..
June 1, 2016 at 8:18 am
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_ID PATIENT _ID CARE_LEVEL BEGIN_DT END_DT
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
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/
June 1, 2016 at 9:49 pm
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.
June 2, 2016 at 8:33 am
Yes, I was trying to get a head count by date.
I will use the calendar table and try this.
Appreciate your help.
June 2, 2016 at 8:50 am
I remember this question, had it few years ago at an interview.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply