Number of patients in a day

  • 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..

  • 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/

  • 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.

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

    I will use the calendar table and try this.

    Appreciate your help.

  • 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