Sequencing Tables

Question

I have a table (Sequence) which determines the order of operations and based on this table we have to pull the corresponding operations from a summary table (Summary) and display the rest of the attributes.

Sequence

PART    OP1 OP2 OP3 OP4 OP5 OP6 OP7 OP8 
ABC      2   3   4   5   1   0   0   0

Summary

Part    Serail  OP  Status        Date Accomplished
ABC     2196    1   OPEN          18-Jul-14
ABC     2196    2   PREV ACCO     21-Jan-11
ABC     2196    3   PREV ACCO     21-Jan-11
ABC     2196    4   PREV ACCO     21-Jan-11
ABC     2196    5   COMPLETE      19-Jan-11
ABC     5596    1   COMPLETE      30-Jun-16
ABC     5596    5   COMPLETE      30-Jun-16
ABC     5845    1   COMPLETE      25-Nov-17
ABC     5845    2   PREV ACCO     27-Aug-09
ABC     5845    5   PREV ACCO     27-Aug-09
ABC     6076    1   OPEN          18-Jul-14
ABC     6076    2   PREV ACCO     2-Apr-12
ABC     6076    5   COMPLETE      29-Mar-12
ABC     6076    10  DELETED       10-MAR-19

get the value for OP1, and see for what all serail its associated with and display the corresponding Status and Date Accomplished. If its doesnt exist a particular serial then display N/A
Example - Serial 5596 has only operations 1 and 5 in the summary, but in the sequence table the part has operations 1,2,3,4,5. So the first 3 operations will be N/A
If there are operations that only exist in the summary but not in the sequence tables then display them at the bottom for the serial that its tied with. Ex - Serial 6076 has operation 10 with status Deleted that only exist in Summary but not in the sequence table, in that case we display that at the end as operation 6 after we process everything from the sequence table.

My Result should be as below

Part    Serial  OP  Status          Date
ABC     2196    1   PREV ACCO   21-Jan-11
ABC     2196    2   PREV ACCO   21-Jan-11
ABC     2196    3   PREV ACCO   21-Jan-11
ABC     2196    4   COMPLETE    19-Jan-11
ABC     2196    5   OPEN        18-Jul-14

ABC     5596    1   N/A         N/A
ABC     5596    2   N/A         N/A
ABC     5596    3   N/A         N/A
ABC     5596    4   COMPLETE    30-Jun-16
ABC     5596    5   COMPLETE    30-Jun-16

ABC     5845    1   PREV ACCO   27-Aug-09
ABC     5845    2   N/A         N/A
ABC     5845    3   N/A         N/A
ABC     5845    4   PREV ACCO   27-Aug-09
ABC     5845    5   COMPLETE    25-Nov-17

ABC     6076    1   PREV ACC    2-Apr-12
ABC     6076    2   N/A         N/A
ABC     6076    3   N/A         N/A
ABC     6076    4   COMPLETE    29-Mar-12
ABC     6076    5   OPEN        18-Jul-14
ABC     6076    6   DELETED     10-MAR-19

I'm not really sure what the point of the sequence table is ? — Caius Jard, May 15 '19 at 19:46

score 0 · Answer 1 · answered May 15 '19 at 19:52

This requires you to have a numbers table - see What is the best way to create and populate a numbers table? for options on creating that.

There are probably more elegant solutions to this, but here's an option for you:

set transaction isolation level read uncommitted;

declare @Summary as table
(
    [Part] nvarchar(3),
    [Serial] int,
    [OP] smallint,
    [Status] nvarchar(30),
    [DateAccomplished] date
);
insert into @Summary
(
    [Part],
    [Serial],
    [OP],
    [Status],
    [DateAccomplished]
)
values
('ABC', 2196, 1, 'OPEN', '18-Jul-14'),
('ABC', 2196, 2, 'PREV ACCO', '21-Jan-11'),
('ABC', 2196, 3, 'PREV ACCO', '21-Jan-11'),
('ABC', 2196, 4, 'PREV ACCO', '21-Jan-11'),
('ABC', 2196, 5, 'COMPLETE', '19-Jan-11'),
('ABC', 5596, 1, 'COMPLETE', '30-Jun-16'),
('ABC', 5596, 5, 'COMPLETE', '30-Jun-16'),
('ABC', 5845, 1, 'COMPLETE', ' 25-Nov-17'),
('ABC', 5845, 2, 'PREV ACCO', '27-Aug-09'),
('ABC', 5845, 5, 'PREV ACCO', '27-Aug-09'),
('ABC', 6076, 1, 'OPEN', '18-Jul-14'),
('ABC', 6076, 2, 'PREV ACCO', '2-Apr-12'),
('ABC', 6076, 5, 'COMPLETE', '29-Mar-12'),
('ABC', 6076, 10, 'DELETED', '10-MAR-19 ');

with [InitialRows] as
(
    select
        s.Part,
        s.Serial,
        n.Number as [OP]
    from @Summary as [s]
        cross apply
        (
            select 
                n.Number
            from dbo.Numbers as [n]
            where n.Number between 1 and 6
        ) as [n]
    group by
        s.Part,
        s.Serial,
        n.Number
    having n.Number <= max(s.OP)
)
select 
    ir.Part,
    ir.Serial,
    ir.OP,
    coalesce(s.[Status], sMax.[Status], 'N/A') as [Status],
    coalesce(cast(s.DateAccomplished as nvarchar(10)), cast(sMax.DateAccomplished as nvarchar(10)), 'N/A') as [DateAccomplished]
from [InitialRows] as [ir]
    left join @Summary as [s] on s.Part = ir.Part
        and s.Serial = ir.Serial
        and s.OP = ir.OP
    left join
    (
        select
            s.Part,
            s.Serial,
            iif(max(s.OP) > 5, 6, max(s.OP)) as [OP],
            s.[Status],
            s.[DateAccomplished]
        from @Summary as [s]
        group by
            s.Part,
            s.Serial,
            s.[Status],
            s.[DateAccomplished]
    ) as [sMax] on sMax.Part = ir.Part
        and sMax.Serial = ir.Serial
        and sMax.OP = ir.OP
order by
    ir.Part,
    ir.Serial,
    ir.OP

Sequencing Tables

1 Answers1