Skip to content
Advertisement

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

Advertisement

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';

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement