How to find the value with date closest to another date

Question

I work in healthcare, and need to produce a report that shows patient lab values at various timepoints. The timepoints are like the following:

Pre transplant:

1 year = 365 days +/- 30 days

3 months = 90 days +/- 14 days

1 month = 30 days +/- 7 days

Post transplant:

1 day = 24 hrs +/- 12 hrs

1 week = 7 days +/- 1 days

1 month = 30 days +/- 7 days

3 month = 90 days +/- 14 days

6 month = 180 days +/- 30 days

1 year = 365 days +/- 30 days

My data model has a lot of tables (results from SQL Server queries), but the main lab table looks like the following:

+-----------------------+-----------------+------------+-----------+
| Order ID | Episode ID | Transplant Date | Lab Date   | Lab Value |
+----------+------------+-----------------+------------+-----------+
| 111      | 222        | 5/2/2018        | 1/22/2018  | 23        |
| 112      | 222        | 5/2/2018        | 1/27/2018  | 15        |
| 113      | 222        | 5/2/2018        | 5/3/2018   | 14        |
| 114      | 222        | 5/2/2018        | 10/19/2018 | 12        |
| 115      | 223        | 1/23/2019       | 1/24/2019  | 20        |
| 116      | 223        | 1/23/2019       | 1/25/2019  | 25        |
| 117      | 223        | 1/23/2019       | 1/31/2019  | 29        |
| 118      | 223        | 1/23/2019       | 4/23/2019  | 30        |
| 119      | 223        | 1/23/2019       | 3/1/2019   | 35        |
| 120      | 224        | 7/19/2019       | 7/19/2018  | 5         |
| 121      | 224        | 7/19/2019       | 7/24/2018  | 13        |
+-----------------------+-----------------+------------+-----------+

Order ID is the unique identifier for the lab, Episode ID is the unique identifier for the patient, and we're looking for labs relative to the Transplant Date.

There's another table for patient data that looks something like this:

+------------+----------------+-----------------+
| Episode ID | Patient Name   | Transplant Date |
+------------+----------------+-----------------+
| 222        | Alphers, Ralph | 5/2/2018        |
| 223        | Bethe, Hans    | 1/23/2019       |
| 224        | Gammow, George | 7/19/2019       |
+------------+----------------+-----------------+

The resulting data should looke similar to this:

+------------+------------+--------------+-------------+------------+-------------+--------------+---------------+-------------+
| Episode ID | 1 year pre | 3 months pre | 1 month pre | 1 day post | 1 week post | 1 month post | 6 months post | 1 year post |
+------------+------------+--------------+-------------+------------+-------------+--------------+---------------+-------------+
| 222        |            | 15           |             | 14         |             |              | 12            |             |
| 223        |            |              |             | 20         | 29          | 35           |               |             |
| 224        | 5          |              |             |            |             |              |               |             |
+------------+------------+--------------+-------------+------------+-------------+--------------+---------------+-------------+

Is there a best way to do this, taking into account both processing time (user experience) and development complexity?

Right now, here's how I'm doing it.

First, I'm using Power Query (M) to create the time points (e.g. Table.AddColumn(#"Changed Type", "Minutes to One Year Before Transplant", each Number.Abs(Duration.TotalMinutes(([Lab Date] - DateTime.From(Date.AddYears([Transplant Date], -1))))))). Then, I use DAX to find the number of days for the record that's closest to the right target date:

Labs shortest minutes to one year before transplant = 
VAR EpisodeID = Patients[Episode ID]
VAR TargetDate = DATEADD(Patients[Transplant Date], 1, MONTH)
VAR WindowDays = 30
RETURN
CALCULATE(
    MIN(Labs[Minutes to One Month After Transplant]),
    FILTER(Labs, Labs[Episode ID] = EpisodeID),
    FILTER(Labs, Labs[Lab Date] >= DATEADD(TargetDate, -WindowDays, DAY)),
    FILTER(Labs, Labs[Lab Date] <= DATEADD(TargetDate, WindowDays, DAY))
)

Then, I use that number of minutes as an identifier to grab the Order ID

Lab Order ID closest to one year before transplant = 
VAR EpisodeID = Patients[Episode ID]
VAR TargetDate = DATEADD(Patients[Transplant Date], 1, MONTH)
VAR WindowDays = 30
VAR DaysFrom = Patients[Labs shortest minutes to one year before transplant]
RETURN
CALCULATE(
    MIN(Labs[Order ID]),
    FILTER(Labs, Labs[Episode ID] = EpisodeID),
    FILTER(Labs, Labs[Lab Date] >= DATEADD(TargetDate, -WindowDays, DAY)),
    FILTER(Labs, Labs[Lab Date] <= DATEADD(TargetDate, WindowDays, DAY))
)

Finally, I can use that Order ID to grab whatever I want from that lab, like the value:

Lab Value closest to one year before transplant = 
VAR EpisodeID = Patients[Episode ID]
VAR OrderID = Patients[Lab Order ID closest to one year before transplant]
RETURN
CALCULATE(
    MIN(Labs[Value]),
    FILTER(Labs, Labs[Episode ID] = EpisodeID),
    FILTER(Labs, Labs[Order ID] = OrderID)
)

And, I need to do this for 3 different labs, which means repeating this process like 30 times. And, the resulting report takes awhile to do the calculations. I can push a bunch of work back to SQL Server, but maybe that's not the best idea?

Answer 1

I would make a table with buckets that dates fall in. This way if someone requests different buckets it is simple to add.

CREATE TABLE [dbo].[table_Buckets](
    [Bucket] [varchar](50) NULL,
    [NumDaysLow] [int] NULL,
    [NumDaysHigh] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[table_Buckets] ([Bucket], [NumDaysLow], [NumDaysHigh]) VALUES (N'Pre-1Yr', -395, -335)
GO
INSERT [dbo].[table_Buckets] ([Bucket], [NumDaysLow], [NumDaysHigh]) VALUES (N'Pre-3Mth', -105, -75)
GO
INSERT [dbo].[table_Buckets] ([Bucket], [NumDaysLow], [NumDaysHigh]) VALUES (N'Pre-1Mth', -37, -21)
GO
INSERT [dbo].[table_Buckets] ([Bucket], [NumDaysLow], [NumDaysHigh]) VALUES (N'Post-1Day', 0, 2)
GO
INSERT [dbo].[table_Buckets] ([Bucket], [NumDaysLow], [NumDaysHigh]) VALUES (N'Post-1Wk', 6, 8)
GO
INSERT [dbo].[table_Buckets] ([Bucket], [NumDaysLow], [NumDaysHigh]) VALUES (N'Post-1Mth', 21, 37)
GO
INSERT [dbo].[table_Buckets] ([Bucket], [NumDaysLow], [NumDaysHigh]) VALUES (N'Post-3Mth', 76, 104)
GO
INSERT [dbo].[table_Buckets] ([Bucket], [NumDaysLow], [NumDaysHigh]) VALUES (N'Post-6Mth', 150, 210)
GO
INSERT [dbo].[table_Buckets] ([Bucket], [NumDaysLow], [NumDaysHigh]) VALUES (N'Post-1Yr', 335, 395)
GO

Now you can run the following sql query that will take the data, put a bucket date to the episode, take the lowest number per bucket and then pivot the table to the view that you desire. You will have to design your data around this structure.

select
   EpisodeID
  ,[Pre-1Yr]
  ,[Pre-3Mth]
  ,[Pre-1Mth]
  ,[Post-1Day]
  ,[Post-1Wk]
  ,[Post-1Mth]
  ,[Post-3Mth]
  ,[Post-6Mth]
  ,[Post-1Yr]

from 
(
  --this select statement takes the lowest value if there are more than one value per bucket
  select main.EpisodeID, main.Bucket, min(main.LabValue) as LabValue from
    (--this select statement assigns the episode to a buckets 
     select
        ml.EpisodeID
        , (select Bucket from
                table_Buckets
            where 
                    NumDaysLow  <= datediff(d,pd.TransplantDate, ml.LabDate)
                and NumDaysHigh >= datediff(d,pd.TransplantDate, ml.LabDate)
            ) AS Bucket
        , ml.LabValue as LabValue

    from 
        table_MainLab ML, 
        table_PatientData PD where ml.EpisodeID = pd.EpisodeID
    ) main
group by EpisodeID, Bucket) s


pivot
(avg(LabValue)
for [Bucket] in
  ([Pre-1Yr]
  ,[Pre-3Mth]
  ,[Pre-1Mth]
  ,[Post-1Day]
  ,[Post-1Wk]
  ,[Post-1Mth]
  ,[Post-3Mth]
  ,[Post-6Mth]
  ,[Post-1Yr])
 ) as pivottable

Answer 2

1 - You need to identify what to do when a lab does not fall in any of the buckets described above. For example, what would you do if the lab date is at 6 months. Where would you like a 6 month lab to be reported? In your example above you lost some data from EpisodeID 222. In my experience, you should report it somewhere - even if it is a catchall bucket that needs to be investigated.

2 - You will need to identify what you want to do when you have 2 reports for the same time period. With EpisodeID 222 you will see that you have 2 Labs in the pre-90 day period. Jan 22 and Jan 27 will both fall in that period.

3 - You have similar data in two tables. The TransplantDate should only be in your PatientTable

You're best bet is a simple pivot (crosstab) query. If you can define your data better by answering 1 and 2 above you will be further ahead to get this done.

Answer 3

All of your code is M, so I'm not sure why you tagged this with SQL. But here's [probably not the most elegant] SQL solution:

create table labs (
    OrderID int not null,
    EpisodeID int not null,
    TransplantDate date not null,
    LabDate date not null,
    LabValue int not null)

insert labs
values 
(111, 222, cast('5/2/2018'  as date), cast('1/22/2018'  as date), 23),
(112, 222, cast('5/2/2018'  as date), cast('1/27/2018'  as date), 15),
(113, 222, cast('5/2/2018'  as date), cast('5/3/2018'   as date), 14),
(114, 222, cast('5/2/2018'  as date), cast('10/19/2018' as date), 12),
(115, 223, cast('1/23/2019' as date), cast('1/24/2019'  as date), 20),
(116, 223, cast('1/23/2019' as date), cast('1/25/2019'  as date), 25),
(117, 223, cast('1/23/2019' as date), cast('1/31/2019'  as date), 29),
(118, 223, cast('1/23/2019' as date), cast('4/23/2019'  as date), 30),
(119, 223, cast('1/23/2019' as date), cast('3/1/2019'   as date), 35),
(120, 224, cast('7/19/2019' as date), cast('7/19/2018'  as date),  5),
(121, 224, cast('7/19/2019' as date), cast('7/24/2018'  as date), 13)

create table patient (
    EpisodeID int not null,
    PatientName varchar(128) not null,
    TransplantDate date not null
)

insert patient
values
(222, 'Alphers, Ralph', cast('5/2/2018'  as date)),
(223, 'Bethe, Hans',    cast('1/23/2019' as date)),
(224, 'Gammow, George', cast('7/19/2019' as date))


select q.EpisodeID
, min(q.[1YrPre]  ) as '1YrPre'
, min(q.[3MoPre]  ) as '3MoPre'
, min(q.[1MoPre]  ) as '1MoPre'
, min(q.[1DayPost]) as '1DayPost'
, min(q.[1WkPost] ) as '1WkPost'
, min(q.[1MoPost] ) as '1MoPost'
, min(q.[3MoPost] ) as '3MoPost'
, min(q.[6MoPost] ) as '6MoPost'
, min(q.[1YrPost] ) as '1YrPost'

from (
    select r.OrderID
    , r.EpisodeID
    , case when r.[1YrPreCheck]   = m.[1YrPreCheck]   and m.[1YrPreCheck]   <= 30 then r.LabValue end as '1YrPre'
    , case when r.[3MoPreCheck]   = m.[3MoPreCheck]   and m.[3MoPreCheck]   <= 14 then r.LabValue end as '3MoPre'
    , case when r.[1MoPreCheck]   = m.[1MoPreCheck]   and m.[1MoPreCheck]   <=  7 then r.LabValue end as '1MoPre'
    , case when r.[1DayPostCheck] = m.[1DayPostCheck] and m.[1DayPostCheck] <=  1 then r.LabValue end as '1DayPost'
    , case when r.[1WkPostCheck]  = m.[1WkPostCheck]  and m.[1WkPostCheck]  <=  1 then r.LabValue end as '1WkPost'
    , case when r.[1MoPostCheck]  = m.[1MoPostCheck]  and m.[1MoPostCheck]  <=  7 then r.LabValue end as '1MoPost'
    , case when r.[6MoPostCheck]  = m.[3MoPostCheck]  and m.[3MoPostCheck]  <= 14 then r.LabValue end as '3MoPost'
    , case when r.[6MoPostCheck]  = m.[6MoPostCheck]  and m.[6MoPostCheck]  <= 30 then r.LabValue end as '6MoPost'
    , case when r.[1YrPostCheck]  = m.[1YrPostCheck]  and m.[1YrPostCheck]  <= 30 then r.LabValue end as '1YrPost'

    from (
        select p.EpisodeID
        , min(abs(datediff(day, l.LabDate, dateadd(year,  -1, p.TransplantDate)))) as '1YrPreCheck'
        , min(abs(datediff(day, l.LabDate, dateadd(month, -3, p.TransplantDate)))) as '3MoPreCheck'
        , min(abs(datediff(day, l.LabDate, dateadd(month, -1, p.TransplantDate)))) as '1MoPreCheck'
        , min(abs(datediff(day, l.LabDate, dateadd(day,    1, p.TransplantDate)))) as '1DayPostCheck'
        , min(abs(datediff(day, l.LabDate, dateadd(day,    7, p.TransplantDate)))) as '1WkPostCheck'
        , min(abs(datediff(day, l.LabDate, dateadd(month,  1, p.TransplantDate)))) as '1MoPostCheck'
        , min(abs(datediff(day, l.LabDate, dateadd(month,  3, p.TransplantDate)))) as '3MoPostCheck'
        , min(abs(datediff(day, l.LabDate, dateadd(month,  6, p.TransplantDate)))) as '6MoPostCheck'
        , min(abs(datediff(day, l.LabDate, dateadd(year,   1, p.TransplantDate)))) as '1YrPostCheck'

        from labs l
          inner join patient p on p.EpisodeID = l.EpisodeID

        group by p.EpisodeID
    ) m
      inner join (
        select l.OrderID
        , p.EpisodeID
        , l.LabValue
        , abs(datediff(day, l.LabDate, dateadd(year,  -1, p.TransplantDate))) as '1YrPreCheck'
        , abs(datediff(day, l.LabDate, dateadd(month, -3, p.TransplantDate))) as '3MoPreCheck'
        , abs(datediff(day, l.LabDate, dateadd(month, -1, p.TransplantDate))) as '1MoPreCheck'
        , abs(datediff(day, l.LabDate, dateadd(day,    1, p.TransplantDate))) as '1DayPostCheck'
        , abs(datediff(day, l.LabDate, dateadd(day,    7, p.TransplantDate))) as '1WkPostCheck'
        , abs(datediff(day, l.LabDate, dateadd(month,  1, p.TransplantDate))) as '1MoPostCheck'
        , abs(datediff(day, l.LabDate, dateadd(month,  3, p.TransplantDate))) as '3MoPostCheck'
        , abs(datediff(day, l.LabDate, dateadd(month,  6, p.TransplantDate))) as '6MoPostCheck'
        , abs(datediff(day, l.LabDate, dateadd(year,   1, p.TransplantDate))) as '1YrPostCheck'

        from labs l
      inner join patient p on p.EpisodeID = l.EpisodeID
    ) r on r.EpisodeID = m.EpisodeID
)q 

group by q.EpisodeID

Answer 4

The easiest way I can think of, is to create calculated columns for each time period and then use them directly in whatever measure you want. For example, for 1 year pre:

1 Year Pre = IF('Table'[Lab Date]>='Table'[Transplant Date]-395 && 'Table'[Lab Date]<='Table'[Transplant Date]-335,'Table'[LabValue],BLANK())

for 3 months pre:

3 Months Pre = IF('Table'[Lab Date]>='Table'[Transplant Date]-104 && 'Table'[Lab Date]<='Table'[Transplant Date]-76,'Table'[LabValue],BLANK())

Similarly, you could create calculated columns for other time periods as well and use them to get the desired visual. Hope this helps.