Help on grouping to sets of rows in my sql data

  • Hello I need help on writing this query. This is what i need. I have the following data, which represents serialnumbers and the difference in between them is the word '_NOTAPPLICABLE' but is the same serialnumber different type of box. I am writing a validation in my system which the user needs to scan first the unit without the _NOTAPPLICABLE and right after the one with the _NOTAPPLICABLE For example if user is scanning TST0000006 need to scan right after the TST0000006_NOTAPPLICABLE :

    Data

    Im using the Skid status to know which serial numbers have been received and wich ones no. On this sample the next serialnumber to scan will be 'TST0000003_NOTAPPLICABLE' .

    Im thinking to group the 2 serialnumbers together Means:

    TST0000006_NOTAPPLICABLE  Group 1

    TST0000006

    TST0000005_NOTAPPLICABLE  Group 2

    TST0000005

    TST0000004_NOTAPPLICABLE Group 3

    TST0000004

    TST0000003_NOTAPPLICABLE Group 4

    TST0000003

    and then compare the staus of each one and find wich ones are different for example if i compare the status of all of them and find this one:

    TST0000003_NOTAPPLICABLE Status--->Received

    TST0000003 Status --> Finished

    I know this is the one is needed next to recieve.

    Could you help me to write this query ?

     

    Here is the table:

    CREATE TABLE #SKIDRACKTABLE (ID INT IDENTITY(1,1), SKIDID INT, SKIDNUMBER NVARCHAR(200), SkidStatus NVARCHAR(100),OrderS int)

    INSERT INTO #SKIDRACKTABLE

    Select '19179','TST0000006_NOTAPPLICABLE','Finished', 2'

    Select '19177','TST0000006','Finished',1

    Select '19178','TST0000005_NOTAPPLICABLE','Received', 2

    Select '19176','TST0000005', Received,1

    Select '19183',TST0000004_NOTAPPLICABLE,Received,2

    Select '19181',TST0000004, Received,1

    Select '19182',TST0000003_NOTAPPLICABLE,Received,2

    Select '19180',TST0000003, Finished,1

    Select '19185',TST0000002, Received,1

     

  • The issue here is although there is an apparent pattern there are no constraints on uniqueness other than the surrogate key ID.   The query you're suggesting to write creates a common row key by lopping off _NOTAPPLICABLE from the SKIDNUMBER and JOIN'ing (or maybe ranking) to the non-_NOTAPPLICABLE SKIDNUMBER's.   Is SKIDNUMBER unique in the SKIDRACKTABLE table?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • hello is unique the skid the only difference is the non applicable word.

  • Maybe you're looking for rows WHERE the SkidStatus and the LEAD SkidStatus are unequal within a common row key partition.  In this case left(SKIDNUMBER, 10) effectively truncates the _NOTAPPLICABLE suffixes

    DROP TABLE if exists #SKIDRACKTABLE;
    go
    CREATE TABLE #SKIDRACKTABLE (
    ID INT IDENTITY(1,1) primary key not null,
    SKIDID INT not null,
    SKIDNUMBER NVARCHAR(200) unique not null,
    SkidStatus NVARCHAR(100) not null,
    OrderS int not null);

    INSERT INTO #SKIDRACKTABLE(SKIDID, SKIDNUMBER, SkidStatus, OrderS)
    Select '19179','TST0000006_NOTAPPLICABLE','Finished', 2 union all
    Select '19177','TST0000006','Finished',1 union all
    Select '19178','TST0000005_NOTAPPLICABLE','Received', 2 union all
    Select '19176','TST0000005', 'Received',1 union all
    Select '19183','TST0000004_NOTAPPLICABLE','Received',2 union all
    Select '19181','TST0000004', 'Received',1 union all
    Select '19182','TST0000003_NOTAPPLICABLE','Received',2 union all
    Select '19180','TST0000003', 'Finished',1 union all
    Select '19185','TST0000002', 'Received',1;

    with lead_cte as (
    select *, lead(SkidStatus) over (partition by left(SKIDNUMBER, 10) order by SKIDNUMBER desc) lead_status
    from #SKIDRACKTABLE)
    select *
    from lead_cte
    where SkidStatus<>lead_status;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply