I have two SQL Server tables:
Request table:
Request_no | Request_ver_no | Sketch_no | Sketch_rev
------------ ------------------ ------------- -------------
1000 1 11-111
1001 1 22-222
1001 2 22-555
1002 1 33-333
TFC table:
Request_no | Request_ver_no | TFC_no | Sketch_no rev
------------ ------------------ ---------- ----------------
1000 1 1 11-111
1000 1 2 11-111A
1000 1 2 11-111B
1001 1 1 22-222
1001 1 2 22-222A
1001 2 1 22-555
1001 2 1 22-555A
1002 1 1 33-333B
My problem is that I need to fill in the sketch revision into the Request table, but there is more than one entries with unique sketch revision per unique Request_no and Request_ver_no PK.
A possible solution was to duplicate the rows in Request which has more than one unique sketch revision connected to it from TFC, and then implement a new index to differentiate these duplicates (Requests with only one unique sketch connected is set at 1 otherwise). For each duplicated Request entry the sketch revision (the last letter of Sketch_no rev in TFC) is copied into Sketch_rev in Request. The index is also iterated and set for every duplicate Request entry.
For all the other cases without more than one sketch revision connected to the same Request entry, the revision is just copied over normally.
Ideally the end result would look like this:
Request table(not query result, but modified table):
Request_no | Request_ver_no | Sketch_no | Sketch_rev | Index
------------ ------------------ ------------- -------------- --------
1000 1 11-111 1
1000 1 11-111 A 2
1000 1 11-111 B 3
1001 1 22-222 1
1001 1 22-222 A 2
1001 2 22-555 1
1001 2 22-555 A 2
1002 1 33-333 B 1
(Blank is regarded as first revision)
TFC_no is not reliable as an index as there exists duplicates as well.
Don't know quite where to begin.
Can someone please help me out with this query?
CodePudding user response：
select r.Request_No, r.Request_ver_no, r.Sketch_no,
replace(t.Sketch_no_rev, r.Sketch_no, '') Sketch_rev, t.TFC_no [Index]
from Request r
inner join TFC t on t.Request_no = r.Request_no and t.Request_ver_no = r.Request_ver_no;
Here it is as a DbFiddle demo.
EDIT: I am not sure if this might be what you meant, this one doesn't have a robust ordering column, but that maybe exactly what you want:
select r.Request_No, r.Request_ver_no, r.Sketch_no,
replace(t.Sketch_no_rev, r.Sketch_no, '') Sketch_rev,
row_number() over (partition by r.Request_no, r.Request_ver_no
order by t.Sketch_no_rev) [Index]
from Request r
inner join TFC t on t.Request_no = r.Request_no and t.Request_ver_no = r.Request_ver_no;
CodePudding user response：
would go with splitting column Sketch_no rev with left and right, using the length (len) of the shortest record as indication of size of no review
(so that it can generalize to the case of having more than 1 character indicating rev version)
select Request_no
, Request_ver_no
, left([Sketch_no rev], (select min(len([Sketch_no rev])) from TFC)) as Sketch_no
, right([Sketch_no rev], len([Sketch_no rev]) - (select min(len([Sketch_no rev])) from TFC)) as Sketch_rev
, TFC_no as [Index]
from TFC;
select * from TFC;
SQL Fiddle on your data here