Provide a list of subtypes from Main group with range numbers

I have a table named Items containing a list of items both at grouped level (G) and sub level (L). However I want to see only the sub level (L) data but with the respective Group Name attached to each sublevel. The Grouped item has a start and end number range. The numbers are not +1 for the sub level list, but incremental in no particular fashion. Also for each level row the start and end number is same.

I am using Microsoft SQL-Server Management Studio-2018

Main table: Items

Code	Start_No	End_No	Type
Group 1	1001	1035	G
AA	1001	1001	L
BB	1005	1005	L
CC	1009	1009	L
DD	1020	1020	L
EE	1035	1035	L
Group 2	1051	1090	G
FF	1051	1051	L
GG	1060	1060	L
HH	1075	1075	L
JJ	1090	1090	L
Group 3	1095	1200	G
LL	1095	1095	L
OO	1120	1120	L
PP	1200	1200	L
Group 4	1300	1800	G
QQ	1300	1300	L
TU	1500	1500	L
WC	1600	1600	L
ZA	1800	1800	L

I would like for the final output to be:

Desired outcome

Code Group	Code	Start_No	End_No
Group 1	AA	1001	1001
Group 1	BB	1005	1005
Group 1	CC	1009	1009
Group 1	DD	1020	1020
Group 1	EE	1035	1035
Group 2	FF	1051	1051
Group 2	GG	1060	1060
Group 2	HH	1075	1075
Group 2	JJ	1090	1090
Group 3	LL	1095	1095
Group 3	OO	1120	1120
Group 3	PP	1200	1200
Group 4	QQ	1300	1300
Group 4	TU	1500	1500
Group 4	WC	1600	1600
Group 4	ZA	1800	1800

This is the code I have written but not getting desired result.

    Select i.Code, c.Start_No, c.End_No 
    into #temp
    FROM items i
    Where i.Type = 'L' 
    
    Select  i2.Code, i2.Start_No, i2.End_No 
    FROM GLM_CHART i2
    WHERE
        EXISTS (SELECT * FROM #temp t where t.Start_No BETWEEN i2.Start_No AND i2.End_No)

Thanks

Answer

You can use a join:

select i.*, ig.code
from items i join
     items ig
     on i.start_no >= ig.start_no and
        i.end_no <= ig.end_no and
        ig.type = 'G'
where i.type = 'L';

Advertisement

Answer