Recursive T-SQL query Using Dates and Tank names

Question

Ok, here’s the story: I work for a company that recycles hazardous liquid chemicals into useful chemicals. Part of this process involves moving these materials from one tank to another as each part of the recycling process is complete. These tanks are either in our tank farm, part of the distillation system, in rail cars, or tanker trucks. The task I was assigned was to write a query that will allow the manager to pick up with any tank and follow the movement of that material through every tank until it is disposed of or sold. The database was built some 10 years ago, before my time, so the data is all I have to work with (i.e. can’t change it to make it easier to work with).

Now the table structure: the columns that lend themselves to this endeavor are:

SouceTank
OpenDate (The day the empty tank starts receiving material)
CosedDate (The date the tank is closed, emptied, and made ready for new material)
DestinationTank (The tank the material was moved to)
ProductName (I add this to validate movements. Many materials can’t be mixed. Some are. The ones that can’t be mixed could show a problem if the algorithm is not working correctly.)

All tanks will have an OpenDate but tanks still being filled or waiting to be shipped out would not have a closed date. Mostly, tanks are chemical agnostic -as in, any empty tank can be used for any chemical. Anywhere from 1 to 10 movements can be expected.

I tried using a recursive CTE T-SQL algorithm but quickly got lost trying to match the SourceTank to the matching DestinationTank where the CosedDate of the SourceTank was between the OpenDate and CosedDate of the DestinationTank, or at least after or equal to the OpenDate of the DestinationTank if it has yet to be closed.

Sample data:

SourceTank  StartDate    ClosedDate   DestinationTank   ProductName  
------------------------------------------------------------------------
TNK01-5     08/03/2017   08/10/2017   TNK30-6           Fuels  
TNK01-5     08/07/2017   08/10/2017   TNK40-6           Fuels  
TNK01-5     07/20/2017   07/31/2017   TNK01-5           Incin  
TNK01-5     08/10/2017   08/17/2017   TNK30-6           Incin  
TNK01-5     08/12/2017   08/17/2017   TNK30-6           Fuels  
TNK03-5     08/13/2017   08/22/2017   TNK30-6           IBAC feed  
TNK07-5     08/11/2017   08/17/2017   TNK40-6           Incin  
TNK07-5     08/14/2017   08/29/2017   TNK40-6           Fuels  
TNK07-5     07/15/2017   08/10/2017   TNK30-6           Picoline Cut  
TNK07-5     08/03/2017   08/10/2017   TNK02-5           Pico 2nd Pass  
TNK07-5     08/06/2017   08/17/2017   TNK40-6           Fuels  
TNK08-5     08/05/2017   08/10/2017   TNK40-6           Fuels  
TNK08-5     08/07/2017   08/08/2017   TNK30-6           Fuels  
TNK08-5     08/10/2017   08/22/2017   TNK40-6           Water  
TNK08-5     07/24/2017   08/10/2017   TNK02-5           Picoline Cut  
TNK09-10    07/20/2017   NULL         TNK30-6           Picoline Crude  
TNK09-10    07/21/2017   08/04/2017   TNK30-6           Picoline Crude  
TNK09-10    08/02/2017   08/10/2017   TNK30-6           Cyclo Waste  
TNK09-10    08/05/2017   08/10/2017   TNK30-6           Cyclo Waste  
TNK09-10    08/07/2017   08/10/2017   TNK30-6           Cyclo Waste  
TNK09-10    08/04/2017   08/10/2017   TNK30-6           Cyclo Waste  
TNK09-10    08/15/2017   08/22/2017   TNK30-6           IBAC feed  
TNK09-10    08/11/2017   08/17/2017   TNK30-6           IBAC feed  
TNK09-10    08/12/2017   08/17/2017   TNK30-6           IBAC feed  
TNK30-6     08/08/2017   08/29/2017   TNK30-6           Cyclo Waste  
TNK40-6     08/13/2017   08/22/2017   TNK30-6           IBAC:PRODUCT  
TNK41-6     08/14/2017   09/27/2017   NATX25496         IBAC:PRODUCT  
TNK51-12    07/26/2017   09/15/2017   TNK30-6           CYCLO Product  
TNK62-12    07/28/2017   09/12/2017   TNK30-6           NON-RCRA NMP  
TNK74-12    07/29/2017   NULL         TNK30-6           Picoline Crude  
TNK91-8     08/03/2017   08/22/2017   TNK08-5           Picoline Prod

Here's what I tried. Didn't know how to handle the NULL ClosingDate..

WITH TanksCTE AS (
SELECT [TrackingNum]
      ,[TblTankTracking].[TankID]
      ,[StartDate]
      ,[TblTankTracking].[ProductID]
      ,[ClosedDate]
      ,[Destination]
  FROM [MAFTS].[Laboratory].[TblTankTracking]

UNION ALL  

SELECT TCTE.[TrackingNum]
      ,TRACK.[TankID]
      ,TRACK.[StartDate]
      ,TRACK.[ProductID]
      ,TRACK.[ClosedDate]
      ,TRACK.[Destination]
  FROM TanksCTE AS TCTE INNER JOIN 
       [MAFTS].[Laboratory].[TblTankTracking]  TRACK
       ON  TCTE.[TankID] = TRACK.[Destination] 
       AND TRACK.[ClosedDate] BETWEEN  TCTE.[StartDate] AND TCTE.[ClosedDate]
)

SELECT * FROM TanksCTE
    option (maxrecursion 0)

Paul Maxwell · Accepted Answer

I'm not sure how useful this will be as I don't know what the expected result should look like, but it might assist. I was trying to make sense of the dates so that's why you will see them concatenated into a pathway columns. I also wasn't sure where to start so I guessed at the earliest startdate for each tank.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE Table1
    ([SourceTank] varchar(8), [StartDate] datetime, [ClosedDate] datetime, [DestinationTank] varchar(9), [ProductName] varchar(14))
;

INSERT INTO Table1
    ([SourceTank], [StartDate], [ClosedDate], [DestinationTank], [ProductName])
VALUES
    ('TNK01-5', '2017-08-03 00:00:00', '08/10/2017', 'TNK30-6', 'Fuels'),
    ('TNK01-5', '2017-08-07 00:00:00', '08/10/2017', 'TNK40-6', 'Fuels'),
    ('TNK01-5', '2017-07-20 00:00:00', '07/31/2017', 'TNK01-5', 'Incin'),
    ('TNK01-5', '2017-08-10 00:00:00', '08/17/2017', 'TNK30-6', 'Incin'),
    ('TNK01-5', '2017-08-12 00:00:00', '08/17/2017', 'TNK30-6', 'Fuels'),
    ('TNK03-5', '2017-08-13 00:00:00', '08/22/2017', 'TNK30-6', 'IBAC feed'),
    ('TNK07-5', '2017-08-11 00:00:00', '08/17/2017', 'TNK40-6', 'Incin'),
    ('TNK07-5', '2017-08-14 00:00:00', '08/29/2017', 'TNK40-6', 'Fuels'),
    ('TNK07-5', '2017-07-15 00:00:00', '08/10/2017', 'TNK30-6', 'Picoline Cut'),
    ('TNK07-5', '2017-08-03 00:00:00', '08/10/2017', 'TNK02-5', 'Pico 2nd Pass'),
    ('TNK07-5', '2017-08-06 00:00:00', '08/17/2017', 'TNK40-6', 'Fuels'),
    ('TNK08-5', '2017-08-05 00:00:00', '08/10/2017', 'TNK40-6', 'Fuels'),
    ('TNK08-5', '2017-08-07 00:00:00', '08/08/2017', 'TNK30-6', 'Fuels'),
    ('TNK08-5', '2017-08-10 00:00:00', '08/22/2017', 'TNK40-6', 'Water'),
    ('TNK08-5', '2017-07-24 00:00:00', '08/10/2017', 'TNK02-5', 'Picoline Cut'),
    ('TNK09-10', '2017-07-20 00:00:00', NULL, 'TNK30-6', 'Picoline Crude'),
    ('TNK09-10', '2017-07-21 00:00:00', '08/04/2017', 'TNK30-6', 'Picoline Crude'),
    ('TNK09-10', '2017-08-02 00:00:00', '08/10/2017', 'TNK30-6', 'Cyclo Waste'),
    ('TNK09-10', '2017-08-05 00:00:00', '08/10/2017', 'TNK30-6', 'Cyclo Waste'),
    ('TNK09-10', '2017-08-07 00:00:00', '08/10/2017', 'TNK30-6', 'Cyclo Waste'),
    ('TNK09-10', '2017-08-04 00:00:00', '08/10/2017', 'TNK30-6', 'Cyclo Waste'),
    ('TNK09-10', '2017-08-15 00:00:00', '08/22/2017', 'TNK30-6', 'IBAC feed'),
    ('TNK09-10', '2017-08-11 00:00:00', '08/17/2017', 'TNK30-6', 'IBAC feed'),
    ('TNK09-10', '2017-08-12 00:00:00', '08/17/2017', 'TNK30-6', 'IBAC feed'),
    ('TNK30-6', '2017-08-08 00:00:00', '08/29/2017', 'TNK30-6', 'Cyclo Waste'),
    ('TNK40-6', '2017-08-13 00:00:00', '08/22/2017', 'TNK30-6', 'IBAC:PRODUCT'),
    ('TNK41-6', '2017-08-14 00:00:00', '09/27/2017', 'NATX25496', 'IBAC:PRODUCT'),
    ('TNK51-12', '2017-07-26 00:00:00', '09/15/2017', 'TNK30-6', 'CYCLO Product'),
    ('TNK62-12', '2017-07-28 00:00:00', '09/12/2017', 'TNK30-6', 'NON-RCRA NMP'),
    ('TNK74-12', '2017-07-29 00:00:00', NULL, 'TNK30-6', 'Picoline Crude'),
    ('TNK91-8', '2017-08-03 00:00:00', '08/22/2017', 'TNK08-5', 'Picoline Prod')
;

Query 1:

with TankStart as (
    select 
           *
         , row_number() over(partition by SourceTank order by StartDate ASC) as rn
    from Table1
    )
, TankPaths as (
      SELECT
            T.SourceTank
          , T.DestinationTank
          , T.StartDate
          , T.ClosedDate
          , T.ProductName
          , CAST(T.DestinationTank AS varchar(max)) 
            + ' '  
            + convert(varchar(10),T.ClosedDate,120)
            AS Pathway
      FROM TankStart T
      WHERE T.rn = 1
      union all
      SELECT
            T1.SourceTank
          , T1.DestinationTank
          , T1.StartDate
          , T1.ClosedDate
          , T1.ProductName
          , M.Pathway 
            + ' '  
            + convert(varchar(10),T1.StartDate,120)
            + ', ' 
            + CAST(T1.DestinationTank AS varchar(max)) 
            + ', ' 
            + convert(varchar(10),T1.ClosedDate,120)

      FROM Table1 T1
      INNER JOIN TankPaths M ON M.DestinationTank = T1.SourceTank
                       AND M.ClosedDate <= T1.StartDate
      where T1.SourceTank <> T1.DestinationTank
    )
select * 
from TankPaths
order by 1, 2, Pathway

Results:

| SourceTank | DestinationTank |            StartDate |           ClosedDate |    ProductName |                                                                            Pathway |
|------------|-----------------|----------------------|----------------------|----------------|------------------------------------------------------------------------------------|
|    TNK01-5 |         TNK01-5 | 2017-07-20T00:00:00Z | 2017-07-31T00:00:00Z |          Incin |                                                                 TNK01-5 2017-07-31 |
|    TNK01-5 |         TNK30-6 | 2017-08-03T00:00:00Z | 2017-08-10T00:00:00Z |          Fuels |                                 TNK01-5 2017-07-31 2017-08-03, TNK30-6, 2017-08-10 |
|    TNK01-5 |         TNK30-6 | 2017-08-10T00:00:00Z | 2017-08-17T00:00:00Z |          Incin |                                 TNK01-5 2017-07-31 2017-08-10, TNK30-6, 2017-08-17 |
|    TNK01-5 |         TNK30-6 | 2017-08-12T00:00:00Z | 2017-08-17T00:00:00Z |          Fuels |                                 TNK01-5 2017-07-31 2017-08-12, TNK30-6, 2017-08-17 |
|    TNK01-5 |         TNK40-6 | 2017-08-07T00:00:00Z | 2017-08-10T00:00:00Z |          Fuels |                                 TNK01-5 2017-07-31 2017-08-07, TNK40-6, 2017-08-10 |
|    TNK03-5 |         TNK30-6 | 2017-08-13T00:00:00Z | 2017-08-22T00:00:00Z |      IBAC feed |                                                                 TNK30-6 2017-08-22 |
|    TNK07-5 |         TNK30-6 | 2017-07-15T00:00:00Z | 2017-08-10T00:00:00Z |   Picoline Cut |                                                                 TNK30-6 2017-08-10 |
|    TNK08-5 |         TNK02-5 | 2017-07-24T00:00:00Z | 2017-08-10T00:00:00Z |   Picoline Cut |                                                                 TNK02-5 2017-08-10 |
|   TNK09-10 |         TNK30-6 | 2017-07-20T00:00:00Z |               (null) | Picoline Crude |                                                                             (null) |
|    TNK30-6 |         TNK30-6 | 2017-08-08T00:00:00Z | 2017-08-29T00:00:00Z |    Cyclo Waste |                                                                 TNK30-6 2017-08-29 |
|    TNK40-6 |         TNK30-6 | 2017-08-13T00:00:00Z | 2017-08-22T00:00:00Z |   IBAC:PRODUCT | TNK01-5 2017-07-31 2017-08-07, TNK40-6, 2017-08-10 2017-08-13, TNK30-6, 2017-08-22 |
|    TNK40-6 |         TNK30-6 | 2017-08-13T00:00:00Z | 2017-08-22T00:00:00Z |   IBAC:PRODUCT |                                                                 TNK30-6 2017-08-22 |
|    TNK41-6 |       NATX25496 | 2017-08-14T00:00:00Z | 2017-09-27T00:00:00Z |   IBAC:PRODUCT |                                                               NATX25496 2017-09-27 |
|   TNK51-12 |         TNK30-6 | 2017-07-26T00:00:00Z | 2017-09-15T00:00:00Z |  CYCLO Product |                                                                 TNK30-6 2017-09-15 |
|   TNK62-12 |         TNK30-6 | 2017-07-28T00:00:00Z | 2017-09-12T00:00:00Z |   NON-RCRA NMP |                                                                 TNK30-6 2017-09-12 |
|   TNK74-12 |         TNK30-6 | 2017-07-29T00:00:00Z |               (null) | Picoline Crude |                                                                             (null) |
|    TNK91-8 |         TNK08-5 | 2017-08-03T00:00:00Z | 2017-08-22T00:00:00Z |  Picoline Prod |                                                                 TNK08-5 2017-08-22 |

Recursive T-SQL query Using Dates and Tank names

Answers (1)

Related Questions