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 :
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
-
thank you this works.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply