0

I have 2 tables:

  • Appointments
  • Patient Visit.

I would like to join the two tables on the basis of dates. though the dates are completely different in each table. What I would like is to join the vital date with appt date that comes right after it.

Like I want appt-date '22-07-2020' to be joined with '23-07-2020' and '22-09-2020' with '25-09-2020'.

Simple joining on patient id is not enough.

Solution should not contain cursors.

Ramsha Zameer
  • 1
  • 1
  • What if there's an additional `'21-07-2020'`, do you want to join it to the same `'23-07-2020' row`? – dnoeth Jul 22 '20 at 14:50
  • Can you show how the columns are defined and exactly what you want? The image isn't showing and text is much better anyway. – Jason Goemaat Jul 22 '20 at 14:51
  • Why should `22/9` be matched with `25/9`? Is this the next working day perhaps? Explain the *actual* problem, not how you thing it would be solved. In any case, calendar queries are a *lot* easier when you use a [Calendar table](https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/) - a table with eg 10-20 years of dates in the future with the date and any extra columns you may need like year, month, semester, day of month, *working day indicator*. Instead of performing date arithmetic you'd only have to find the next "working day" row – Panagiotis Kanavos Jul 22 '20 at 14:59
  • @dnoeth Yes I can have duplicates! – Ramsha Zameer Jul 22 '20 at 15:00
  • In your case you seem to want the next working day. Once you join with a Calendar table you could use eg `LEAD` or `LAG` to get the next or previous row in a set, in this case, the next or previous row form the calendar table – Panagiotis Kanavos Jul 22 '20 at 15:00
  • What version of SQL Server are you running? – Jason Jul 22 '20 at 15:03
  • @PanagiotisKanavos Actually the vital table gets updated after a few days of appointments. that is why I want to join 22-07-20 to 23-07-20 (whatever date is just after it). ofcourse i'll be joining it with patientid too. – Ramsha Zameer Jul 22 '20 at 15:03
  • @RamshaZameer I posted the comment before the edit that changed the question *completely*. You're looking for the next appointment. You can still use `LEAD` or `LAG` but a Calendar table won't help – Panagiotis Kanavos Jul 22 '20 at 15:04
  • 1
    @Jason 2008 sql server – Ramsha Zameer Jul 22 '20 at 15:06
  • @PanagiotisKanavos I would really appreciate an sql solution. – Ramsha Zameer Jul 22 '20 at 15:10
  • Does this answer your question? [SQL join against date ranges?](https://stackoverflow.com/questions/2306462/sql-join-against-date-ranges) – fuggerjaki61 Jul 22 '20 at 15:18

2 Answers2

0

You can select by getting the top(1) from Visits table where vitalDate >= apptDate. ie:

select a.PatientId, a.ApptDate, 
    v.VitalDate, v.vitalInfo, v.VitalId
from appointments a
   outer apply (select top(1) VitalDate, vitalInfo, VitalId 
                from Visits v 
                where a.PatientId = v.PatientId 
                  and v.VitalDate >= a.apptDate
                order by VitalDate) v(VitalDate, vitalInfo, VitalId);

EDIT: Later I saw you are saying you could have duplicates. In reality that is not something that happens with "appointed" patient visits. Anyway, for covering the duplicates I am adding another one:

select a.PatientId, a.ApptDate, 
    v.VitalDate, v.vitalInfo, v.VitalId
from appointments a
   outer apply (select VitalDate, vitalInfo, VitalId 
                from Visits v1 
                where a.PatientId = v1.PatientId 
                  and v1.VitalDate = (select min(Vitaldate) from 
                    Visits v2 
                    where v1.patientId = v2.patientId
                         and v2.VitalDate >= a.apptDate)) v(VitalDate, vitalInfo, VitalId);

PS: Here is DBFiddle demo

Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39
  • This works! Only issue is that it picks up all the appt dates that is smaller to my visit date. I just want the latest appt date that is smaller than my vital date. – Ramsha Zameer Jul 22 '20 at 16:17
  • @RamshaZameer, sorry but probably you modified it wrong. Check the DBFiddle demo. – Cetin Basoz Jul 22 '20 at 17:38
0

Here's an example you can try ...

IF OBJECT_ID('tempdb..#Appointments') IS NOT NULL DROP TABLE #Appointments
IF OBJECT_ID('tempdb..#Visits') IS NOT NULL DROP TABLE #Visits

SELECT * INTO #Appointments FROM (VALUES
    (6, 'Fred', CAST('2020-07-22' as datetime)),
    (6, 'Fred', '2020-09-22'),
    (6, 'Fred', '2015-05-14'),
    (7, 'Barney', '2020-07-25'),
    (8, 'Wilma', '2020-08-15'),
    (9, 'Betty', '2020-09-10')
) S(patient_id, patient_name, appt_date)

SELECT * INTO #Visits FROM (VALUES
    (6, CAST('2020-07-23' as datetime), 'BP: 51, HR: 56', 1),
    (6, '2020-09-25', 'BP: 52, HR: 56', 2),
    (6, '2015-05-22', 'BP: 53, HR: 56', 3),
    (7, '2020-07-27', 'BP: 54, HR: 56', 4),
    (7, '2020-08-22', 'BP: 55, HR: 56', 5),
    (8, '2020-08-22', 'BP: 56, HR: 56', 6),
    (8, '2020-09-02', 'BP: 57, HR: 56', 7),
    (8, '2020-10-31', 'BP: 58, HR: 56', 8),
    (9, '2020-09-11', 'BP: 59, HR: 56', 9),
    (9, '2020-10-21', 'BP: 60, HR: 56', 10)
) S(patient_id, vitals_date, vitals_info, visit_id)

SELECT * FROM #Appointments
SELECT * FROM #Visits

SELECT
    A.*,
    V.*
FROM
    #Appointments A
    LEFT JOIN #Visits V ON
        V.visit_id = (
            SELECT TOP 1 V2.visit_id FROM #Visits V2 WHERE V2.patient_id = A.patient_id AND V2.vitals_date >= A.appt_date ORDER BY V2.vitals_date
        )

-- updated to change date format

Jason
  • 711
  • 6
  • 14
  • 1
    Such casting would likely error on most SQL server installations. Instead, use ODBC canonical style, 'yyyyMMdd'. That works, whatever the SQL server settings are. – Cetin Basoz Jul 22 '20 at 15:19
  • @CetinBasoz Fair enough ... ODBC cannonical would be yyyy-mm-dd hh:mi:ss ... However the date part is all that's required. Changing it to yyyy-mm-dd which is also ISO format as well and should work universally. The OP didn't specify the regional settings, but it's reasonable to assume they are different than mine based on the post. It's a quick edit. – Jason Jul 22 '20 at 15:28
  • In that case *don't* cast and use `date` instead of `datetime`. In `datetime`, the `yyyy-MM-dd` format is still affected by the `dateformat` setting and may get interpreted as `yyyy-dd-mm`. With `date` on the other hand, `yyyy-mm-dd` and `yyyyMMdd` are both treated as an unambiguous date literal – Panagiotis Kanavos Jul 22 '20 at 15:45
  • 1
    Aren't we kind of missing the point here ... it's an example. The OP can change it to whatever format they like. – Jason Jul 22 '20 at 15:51
  • I tried this solution. There is a problem with it. It joins one vital date with all the appt dates there is. Not just the one before it. which is what I want. – Ramsha Zameer Jul 22 '20 at 16:02
  • Not sure what you are looking for @RamshaZameer. It joins the 'next' vitals date with each appointment. What do you want each result row to be? One row for each appointment, vitals record, patient .. etc.? If you are looking for a specific set of Appointments you could add a WHERE clause, but I'm not sure based on your response what rows you are looking for. – Jason Jul 22 '20 at 16:10