Post reply

SELECT statement help needed - grouping by month-year

  • July 4, 2017 at 5:43 am

    #343371

    Hi,

    I am struggling with a select statement because the data structure needs to change a bit.  Currently the table is approximately as per below and grouped on Patient/Booking/Bed.

    Patient_No, Booking_Ref, Bed_ID, Admission_Date, Discharge_Date

    From this I can easily see for a specific patient / booking how long they stayed in hospital, however, what I need to evaluate is how many Occupied bed days there were
    over a whole ward, by month. The problem I face is that a single booking will often span the end of a month (or multiple months) and I can't figure out how to script
    that part. I've put a basic example below, can anyone help?

    Source:
    Patient_No, Booking_Ref, Bed_ID, Admission_Date, Discharge_Date
    1 , x1  , 1 , 2/4/2017 , 3/5/2017
    2 , x2 , 1 , 4/5/2017 , 16/7/2017
    3 , x3 , 2 , 6/4/2017 , 20/5/2017

    Required Output:
    Month-Year, Occupied_Days
    04-2017, 54
    05-2017, 51
    06-2017, 30
    07-2017, 16

    Many thanks in advance!
    Emma

  • July 4, 2017 at 6:13 am

    #1949310

    Here's one way. It uses an in-line dates table (thanks, Rob Farley), joins that to your data and finally groups it.
    Be sure to set the start and end dates to be before and after the range of dates you are querying.
    CREATE TABLE #Booking
    (
      BookingStart DATE
    , BookingEnd DATE
    );

    INSERT #Booking
    (
      BookingStart
    , BookingEnd
    )
    VALUES
    (
      '20170402', '20170503'
    )
    ,(
      '20170504', '20170716'
    )
    ,(
      '20170406', '20170520'
    );

    DECLARE @StartDate DATE = '20170101';
    DECLARE @EndDate DATE = '20171231';

    WITH
    N0 as (SELECT 1 as n UNION ALL SELECT 1)
    ,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
    ,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
    ,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
    ,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
    ,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
    ,N6 as (SELECT 1 as n FROM N5 t1, N5 t2)
    ,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as num FROM N6)
    SELECT Yr = YEAR(dates.DateSerial), Mth = MONTH(dates.DateSerial), ct = COUNT(1)
    FROM nums JOIN #Booking b ON DATEADD(day,nums.num-1,@StartDate) BETWEEN b.BookingStart AND b.BookingEnd
    CROSS APPLY (SELECT DateSerial = DATEADD(day,nums.num-1,@StartDate)) dates
    WHERE nums.num <= DATEDIFF(day,@StartDate,@EndDate) + 1
    GROUP BY YEAR(dates.DateSerial)
      , MONTH(dates.DateSerial)

Viewing 2 posts - 1 through 2 (of 2 total)

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