Sql-server – Parent-child Relationship with data scattered between these records

cterecursivesql server

I have a 60 column query that fills in a card on an application. The problem I am running into is gathering all the parent-child relationship data into one record per child.

As a quick example: The card goes through a bunch of processes, which have their own tables and hits two critical points, i will call it Point A and Point B. Say i have Card 1 that hits Point A. At this point the card splits to Card 1 and Card 2 creating a new record for Card 2, but Card 2's data up until this point is in Card A's record. The two cards are now being processed separately. Then at Point B Card 2 splits to Card 3 creating a new record for Card 3, but Card 3's data is now on Card 2 and Card 1. And this can go on for an unforeseen amount of times (for example, at Point B, Card 1 splits to Card 4.

Each Card's record in the query should have data from each record as follows:

Card 1 has data from Card 1
Card 2 has data from Card 1 + Card 2
Card 3 has data from Card 1 + Card 2 + Card 3    
Card 4 has data from Card 1 + Card 4

So now the question, will a recursive CTE do the job? I have tried it a few times, but couldn't quite get it to work all the way through. Should we try another method or should we just replicate the data from each card onto the next card's record when producing the entire card in the application?

Here was the failed attempt at the CTE (I reduced the amount of columns since there are 60)

;WITH originalQry AS (

    -- create the original query here since there are LEFT JOINS
    -- this query has the 60 columns plus a bunch of tables from all
    -- the different processes      

)
,recurseCTE AS (
    SELECT 
        a.CardNumber
        ,a.ParentCardNumber
        ,a.PriorToPointAData
        ,a.PointAData
        ,a.PriorToPointBData
        ,a.PointBData
        -- 60 or so columns that show the data actually on each child record
        -- so each record is not the complete picture
    FROM 
        originalQry AS a
    WHERE 
        a.ParentCardNumber IS NOT NULL

    UNION ALL

    SELECT 
        actual.CardNumber
        ,actual.ParentCardNumber
        ,COALESCE(actual.PriorToPointAData, looper.PriorToPointAData)
        ,COALESCE(actual.PointAData, looper.PointAData)
        ,COALESCE(actual.PriorToPointBData, looper.PriorToPointBData)
        ,COALESCE(actual.PointBData, looper.PointBData)
    FROM 
        originalQry AS actual 
        INNER JOIN recurseCTE AS looper
            ON actual.CardNumber = looper.ParentCardNumber
    )

    -- try to group the records, this does not really work
    select 
        a.CardNumber
        ,a.CardNumber AS OriginalCardNumber
        ,a.ParentCardNumber
        ,MAX(COALESCE(a.PriorToPointAData,0))
        ,MAX(COALESCE(a.PointAData,0))
        ,MAX(COALESCE(a.PriorToPointBData,0))
        ,MAX(COALESCE(a.PointBData,0))
    from recurseCTE
    GROUP BY
        a.CardNumber
        ,a.CardNumber
        ,a.ParentCardNumber

UPDATE – Sample of desired results

This is what the query gives me 

+------------+-----------+--------+--------+-----------+------+-----------+------------+------------+-------+-------+------------+-----------+----------+
| CardNumber |  Written  | Source | Design |  Started  | Qty  | PartATime | PartASpeed | PartASplit | Press | Color | PartBSplit | PartBTime | FinalQty |
+------------+-----------+--------+--------+-----------+------+-----------+------------+------------+-------+-------+------------+-----------+----------+
| A07AA      | 2/15/2015 | NA1243 | OD     | 2/16/2015 | 7.76 | 4         | 1          | ABC        | 2     | Black | AAA        |         3 |        9 |
| A07AB      | 2/15/2015 | NA1243 | OD     | 2/16/2015 | 7.76 | NULL      | NULL       | DEF        | 3     | Gray  | BBB        |         4 |        8 |
| A07AC      | 2/15/2015 | NA1243 | OD     | 2/16/2015 | 7.76 | NULL      | NULL       | NULL       | NULL  | NULL  | CCC        |         5 |        7 |
+------------+-----------+--------+--------+-----------+------+-----------+------------+------------+-------+-------+------------+-----------+----------+

this is my desired result – as you can see the previous card number just fills in the blanks from the parent

   +------------+-----------+--------+--------+-----------+------+-----------+------------+------------+-------+-------+------------+-----------+----------+
| CardNumber |  Written  | Source | Design |  Started  | Qty  | PartATime | PartASpeed | PartASplit | Press | Color | PartBSplit | PartBTime | FinalQty |
+------------+-----------+--------+--------+-----------+------+-----------+------------+------------+-------+-------+------------+-----------+----------+
| A07AA      | 2/15/2015 | NA1243 | OD     | 2/16/2015 | 7.76 |         4 |          1 | ABC        |     2 | Black | AAA        |         3 |        9 |
| A07AB      | 2/15/2015 | NA1243 | OD     | 2/16/2015 | 7.76 |         4 |          1 | DEF        |     3 | Gray  | BBB        |         4 |        8 |
| A07AC      | 2/15/2015 | NA1243 | OD     | 2/16/2015 | 7.76 |         4 |          1 | DEF        |     3 | Gray  | CCC        |         5 |        7 |
+------------+-----------+--------+--------+-----------+------+-----------+------------+------------+-------+-------+------------+-----------+----------+

Best Answer

so after some more thought and reading some of the posts i broke up my query into two parts, i think that was the main confusion because there were so many moving parts

  1. I created a view that performs the recursion on the target table (see below), thereby removing the need for the other columns
  2. I used this table in my 60 column query.

Here is the recursion that ended up working. Part of my problem was that I needed that parent and all children in the final result. I set the OPTION(MAXRECURSION 676) because this is the most that can be done after further research

WITH recursion AS (

    SELECT 
        CardNumber
        ,CardNumber AS ParentSerialNumber
    FROM 
        dbo.Card

    UNION ALL

    SELECT 
      p.CardNumber
      ,c.ParentSerialNumber
    FROM 
        recursion AS c
        INNER JOIN  dbo.Card AS p
            ON p.ParentSerialNumber = c.SerialNumber
)

SELECT 
        SerialNumber
        ,ParentSerialNumber
FROM
    recursion
Related Question