T-SQL: Creating a hierarchical structure from relational data

Transact-SQL
1

Hi Guys,

I have a requirements to look at the dependancies between group Customer table and Customers table ( Parent and Child relationships. The current tables are not scalable and difficult to extract information. There is no foreign key between group customer and Customer table.

@CustomerGrp tbl
The Master Client is recorded in the @CustomerGrp table above
• Master client contains a range of sub clients. Eg. (1-52 ranges)
• Sub-Clients cannot be associated with a contract
• Only Master Client will be associated to contract

@Customers tbl
• Associated Clients are renamed or capture to parent table (@Customer tbl)
• The Clients will have their contracts

Could you guys help me either to create a link between the two tables for better reporting. Please see my code below:

SQL Scripts for sample data below:
--create sample data
IF OBJECT_ID('tempdb.dbo.#CustomerGrp', 'U') IS NOT NULL DROP TABLE #CustomerGrp;
IF OBJECT_ID('tempdb.dbo.#Customers', 'U') IS NOT NULL DROP TABLE #Customers;

Create table #CustomerGrp
(
[GroupID] [int] NOT NULL,
[GroupMasterCustKey] varchar NOT NULL,
[GroupFromCustKey] varchar NOT NULL ,
[GroupToCustKey] varchar NOT NULL

)

INSERT #CustomerGrp ([GroupID],[GroupMasterCustKey],[GroupFromCustKey], [GroupToCustKey])

Select '1082', 'XLV001', 'XLVET001', 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1082', 'XLV001', ' XLVET001' , 'XLVET048'
union all
Select '1146', 'XLV001', ' XLVET050' , 'XLVET057'
union all
Select '1146', 'XLV001', ' XLVET050' , 'XLVET057'
union all
Select '1146', 'XLV001', ' XLVET050' , 'XLVET057'
union all
Select '1146', 'XLV001', ' XLVET050' , 'XLVET057'
union all
Select '1146', 'XLV001', ' XLVET050' , 'XLVET057'
union all
Select '1146', 'XLV001', ' XLVET050' , 'XLVET057'
union all
Select '1146', 'XLV001', ' XLVET050' , 'XLVET057'
union all
Select'1146', 'XLV001', ' XLVET050' , 'XLVET057'

--create sample data
Create table #Customers
(
CustomerID int NOT NULL,
CustomerKey varchar(100) NOT NULL PRIMARY KEY,
[CreatedDate] datetime null,
AssociateClient varchar(20) NULL

)

INSERT #Customers (CustomerID,CustomerKey, CreatedDate, AssociateClient, CompanyName)
Select 104506, 'XLV001', '2015-10-09', 'XLVET001-XLVET052'

Below is currently how we query the table:

SELECT CG.GroupId,C.CustomerKey
,CG.GroupMasterCustKey AS MasterCustomerKey
,C.CustomerID AS ClientID
,[GroupFromCustKey]
,[GroupToCustKey]
,C.AssociateClient

FROM #CustomerGrp AS CG WITH (NOLOCK)
INNER JOIN #Customers AS C WITH (NOLOCK)
ON C.CustomerKey >= CG.GroupFromCustKey
AND C.CustomerKey <= CG.GroupToCustKey

2

Hmmm. Habit? :frowning:

Beware of default size on varchar definition. Rarely is it the best choice, and SQL will allocate a default size of 1 in some circumstances, which is usually unexpected and leads to bugs.

Is this "hierarchical" in the sense that you have have Parent, Child, Grandchild, Great-grandchild etc. hiercarchy?

Or is it just that a given Master Client can have 0,1,many Sub-Clients (and no sub-sub-clients)?

That would be prudent, anyway, if only to ensure No Goofy Data gets stored in the table :slight_smile:

Its probably just me, but I'm not understanding the purpose of the query. Seems to be finding who the Master Customer(s) is for a range of From/To Customer Keys.

This suggests that the Customer Keys have "embedded" attributes that naturally group them together; either that, or they have some sort of 01, 02, 03 ... suffix that ties them together - which I can understand, but its a bit fragile as data changes and the codes no longer associate with the name (but that may not be how the codes are structured?).

I'm obviously not familiar with your data etc., but i would prefer to see Attributes in the Record that defined such things, rather than a composite code as it appears to be. So one attribute that defined whatever "XLVET' "means", and another for the "050" ... "057" part (assuming that that group is different to, say, the "040" to "049" group)

Presumably there is some significance to those numbers falling within a range (because your are selecting FROM ... TO on range), either a Range (i.e. low-to-high) is always going to be valid, if so fair enough :), or there should be a means of selecting multiple customers, based on one/many common attributes, so that the numbers do not have to be consecutive. That may be irrelevant within the context of your data though?

Either way, my description of "attributes" doesn't sound like it fits with your question of "hierarchy"?

3

Many thanks for your prompt reply.

The query is just to illustrate how we currently queried the parent and child "hierarchy.

The bottom line and area of help is best practice to redesign the table for effective reporting. I also think it is a case of Master Client can have 0,1,many Sub-Clients (and no sub-sub-clients)?

Thanks for your help.

Ziggy

4

Hello Kirsten.

Any suggestion and answer to my previous reply please.

Thanks

Z