Search code examples
sqlsql-servermintransitivity

SQL Server Query to find min date in a transitive relationship w/ possible cyclic loops

Running into a bit of a headache with this one query and some hints/suggestions would be greatly appreciated. I couldn't find anything really related to my problem -I found some stuff on transitive closures which isn't quite what I need since my data can possibly create a loop/cycle which, I think, would cause a recursive call to infinitely loop.

Say I have two basic tables with the data displayed below them. Full disclosure: the Memberships table a CTE which has already done a fair bit of logic to calcualte the CServiceDate column value. The Transfers table is an actual table which doesn't contain a whole lot other than a PK and FromMembershipID and ToMembershipID relationships.

Memberships

 
+==========+==============+================+==============+
| PersonID | MemberShipID | MembershipDate | CServiceDate |
+==========+==============+================+==============+
|        1 |           15 | Aug-01-2016    | Aug-27-2017  |
+----------+--------------+----------------+--------------+
|        1 |           16 | Mar-25-2016    | Sep-01-2000  |
+----------+--------------+----------------+--------------+
|        1 |           17 | Dec-06-2011    | May-15-1995  |
+----------+--------------+----------------+--------------+
|        1 |           18 | Jan-12-2009    | Feb-28-1998  |
+----------+--------------+----------------+--------------+
|        1 |           19 | Apr-08-2016    | Jul-10-1994  |
+----------+--------------+----------------+--------------+
|        1 |           20 | Jun-11-2010    | Nov-12-1997  |
+----------+--------------+----------------+--------------+

Transfer

+=====+==================+================+
| TID | FromMembershipID | ToMembershipID |
+=====+==================+================+
|   1 |               16 |             15 |
+-----+------------------+----------------+
|   2 |               18 |             17 |
+-----+------------------+----------------+
|   3 |               19 |             17 |
+-----+------------------+----------------+
|   4 |               20 |             18 |
+-----+------------------+----------------+
|   5 |               20 |             19 |
+-----+------------------+----------------+

Problem What I need from a query is for each row in the Memberships CTE (i.e. for each MembershipID), I want to return the MIN CServiceDate for all related MembershipIDs. I will call this MIN value the ECSD (Expected Credited Service Date). The calculation of the ECSD has only two conditions:

  • A membership record is considered "related" to the current MembershipID if it somehow transitively tied to the current MembershipID via the Transfers table by looking at the FromMembershipID and ToMembershipID columns. e.g. For MembershipID 20, if we look at the Transfers table, we can see that MembershipIDs 20, 19, 18, and 17 are all related through transitivity (aside: via the Transfers table, we can see that MembershipIDs 15 and 16 are related to one another, but not to [20,19,18,17] )
  • Of the transitively-related membership listing derived directly above, the only memberships that can be considered in the transitive relationship listing when calculating the ECSD are the Memberships with a MembershipDate EARLIER than the current MembershipID. e.g. Based on the MembershipDates for a given MembershipID and the Transfers table, if looking at MembershipID 17, MembershipID 19 cannot be considered when calculating the ECSD for MembershiID 17 since MembershipID 19 has a MembershipDate (Apr-08-2016) that is NOT earlier than MembershipID 17's (Dec-06-2011)

Expected Output of the ECSD column

+==========+==============+================+==============+=============+
| PersonID | MemberShipID | MembershipDate | CServiceDate |    ECSD     |
+==========+==============+================+==============+=============+
|        1 |           15 | Aug-01-2016    | Aug-27-2017  | Sep-01-2000 |
+----------+--------------+----------------+--------------+-------------+
|        1 |           16 | Mar-25-2016    | Sep-01-2000  | Sep-01-2000 |
+----------+--------------+----------------+--------------+-------------+
|        1 |           17 | Dec-06-2011    | May-15-1995  | May-15-1995 |
+----------+--------------+----------------+--------------+-------------+
|        1 |           18 | Jan-12-2009    | Feb-28-1998  | Feb-28-1998 |
+----------+--------------+----------------+--------------+-------------+
|        1 |           19 | Apr-08-2016    | Jul-10-1994  | Jul-10-1994 |
+----------+--------------+----------------+--------------+-------------+
|        1 |           20 | Jun-11-2010    | Nov-12-1997  | Nov-12-1997 |
+----------+--------------+----------------+--------------+-------------+

Please Note:

  • A MembershipID can show up multiples times in the FromMembershipID and ToMembershipID columns. These column values do not have to be unique.
  • (add after edit) I am looking to apply this solution against larger datasets where the transfers are more than 2-tiers deep. Please see below for an example:

Example 2

Memberships

+==========+==============+================+==============+
| personid | membershipid | membershipdate | CServiceDate |
+==========+==============+================+==============+
|   499510 |       548426 | 2014-09-29     | 2014-09-29   |
+----------+--------------+----------------+--------------+
|   499510 |       548428 | 2014-01-29     | 2014-01-29   |
+----------+--------------+----------------+--------------+
|   499510 |       548425 | 2012-05-28     | 2012-05-28   |
+----------+--------------+----------------+--------------+
|   499510 |       548429 | 2011-11-23     | 2011-11-23   |
+----------+--------------+----------------+--------------+
|   499510 |       548427 | 2008-07-03     | 2008-07-03   |
+----------+--------------+----------------+--------------+
|   499510 |       548431 | 2001-05-01     | 1976-01-01   |
+----------+--------------+----------------+--------------+
|   499510 |       548430 | 1998-10-08     | 1998-10-08   |
+----------+--------------+----------------+--------------+

Transfers

+=======+========+==================+================+
|  tid  |  pid   | FromMembershipID | ToMembershipID |
+=======+========+==================+================+
| 10664 | 499510 |           548430 |         548431 |
+-------+--------+------------------+----------------+
| 10665 | 499510 |           548431 |         548427 |
+-------+--------+------------------+----------------+
| 10666 | 499510 |           548427 |         548429 |
+-------+--------+------------------+----------------+
| 10667 | 499510 |           548429 |         548425 |
+-------+--------+------------------+----------------+
| 10668 | 499510 |           548425 |         548428 |
+-------+--------+------------------+----------------+
| 10669 | 499510 |           548428 |         548426 |
+-------+--------+------------------+----------------+
| 13085 | 499510 |           548430 |         548427 |
+-------+--------+------------------+----------------+
| 13086 | 499510 |           548427 |         548425 |
+-------+--------+------------------+----------------+
| 13087 | 499510 |           548425 |         548426 |
+-------+--------+------------------+----------------+
| 13088 | 499510 |           548431 |         548429 |
+-------+--------+------------------+----------------+
| 13089 | 499510 |           548429 |         548428 |
+-------+--------+------------------+----------------+

Expected Outcome

+==========+==============+================+==============+============+
| personid | membershipid | membershipdate | CServiceDate |    ECSD    |
+==========+==============+================+==============+============+
|   499510 |       548426 | 2014-09-29     | 2014-09-29   | 1976-01-01 |
+----------+--------------+----------------+--------------+------------+
|   499510 |       548428 | 2014-01-29     | 2014-01-29   | 1976-01-01 |
+----------+--------------+----------------+--------------+------------+
|   499510 |       548425 | 2012-05-28     | 2012-05-28   | 1976-01-01 |
+----------+--------------+----------------+--------------+------------+
|   499510 |       548429 | 2011-11-23     | 2011-11-23   | 1976-01-01 |
+----------+--------------+----------------+--------------+------------+
|   499510 |       548427 | 2008-07-03     | 2008-07-03   | 1976-01-01 |
+----------+--------------+----------------+--------------+------------+
|   499510 |       548431 | 2001-05-01     | 1976-01-01   | 1976-01-01 |
+----------+--------------+----------------+--------------+------------+
|   499510 |       548430 | 1998-10-08     | 1998-10-08   | 1998-10-08 |
+----------+--------------+----------------+--------------+------------+

Please note: I've added a pid column to the transfers table. Forgot to include that the first time around.

Thanks!

Solution

  • I think this is the accurate result... though it differed from your expected. See my comments above.

    Since the first table is a CTE, you can either keep it a CTE and continue on with this CTE or store those results into a temp table which could be beneficial since we will query it multiple times. 

    declare @Memberships table (PersonID int, MemberShipID int, MembershipDate varchar(16), CServiceDate varchar(16))
insert into @Memberships
values
(1,15,'Aug-01-2016','Aug-27-2017'),
(1,16,'Mar-25-2016','Sep-01-2000'),
(1,17,'Dec-06-2011','May-15-1995'),
(1,18,'Jan-12-2009','Feb-28-1998'),
(1,19,'Apr-08-2016','Jul-10-1994'),
(1,20,'Jun-11-2010','Nov-12-1997') 

declare @Transfer table (TID int, FromMembershipID int, ToMembershipID int)
insert into @Transfer
values
(1,16,15),
(2,18,17),
(3,19,17),
(4,20,18),
(5,20,19)

;with cte as(
select
    m.PersonID
    ,m.MemberShipID
    ,m.MembershipDate
    ,m.CServiceDate
    ,case when t.FromMembershipID <> m.MemberShipID then t.FromMembershipID else t.ToMembershipID end RelatedMemberShips
from
    @Memberships m
    left join
    @Transfer t on
    t.FromMembershipID = m.MemberShipID or t.ToMembershipID = m.MemberShipID)

select distinct
    cte.PersonID
    ,cte.MemberShipID
    ,cte.MembershipDate
    ,cte.CServiceDate
    --,cte.RelatedMemberShips
    --,m.MembershipDate
    --,m.CServiceDate
    ,case when min(convert(date, replace(m.CServiceDate, '-', ' '), 0)) over (partition by cte.MemberShipID) < convert(date, replace(cte.CServiceDate, '-', ' '), 0) then min(convert(date, replace(m.CServiceDate, '-', ' '), 0)) over (partition by cte.MemberShipID)  else convert(date, replace(cte.CServiceDate, '-', ' '), 0) end
from 
    cte
    left join 
        @Memberships m on m.MemberShipID = cte.RelatedMemberShips
        and convert(date, replace(m.MembershipDate, '-', ' '), 0) <= convert(date, replace(cte.MembershipDate, '-', ' '), 0)

    Or, you can write this IN LINE and skip the CTE all together...

    select distinct
    m.PersonID
    ,m.MemberShipID
    ,m.MembershipDate
    ,m.CServiceDate
    --,case when t.FromMembershipID <> m.MemberShipID then t.FromMembershipID else t.ToMembershipID end RelatedMemberShips
    ,case when min(convert(date, replace(m2.CServiceDate, '-', ' '), 0)) over (partition by m.MemberShipID) < convert(date, replace(m.CServiceDate, '-', ' '), 0) then min(convert(date, replace(m2.CServiceDate, '-', ' '), 0)) over (partition by m.MemberShipID)  else convert(date, replace(m.CServiceDate, '-', ' '), 0) end
from
    @Memberships m
    left join
    @Transfer t on
    t.FromMembershipID = m.MemberShipID or t.ToMembershipID = m.MemberShipID
    left join 
        @Memberships m2 on m2.MemberShipID = case when t.FromMembershipID <> m.MemberShipID then t.FromMembershipID else t.ToMembershipID end
        and convert(date, replace(m2.MembershipDate, '-', ' '), 0) <= convert(date, replace(m.MembershipDate, '-', ' '), 0)