Hello.
I need your help with problem below:
Definitions:
Parent & child relationship currently captured in the tblCGs table. (Linked accounts – these are hierarchical)
• Currently a tblCGs table allows the linking of Parent(GroupMasterCustKey) to child (CustomerKey) which may or may not exist
• This allows access between a parent and children relationship and access to shared agreements between them.
• Clients that part of the same group, the child clients don’t have their own agreements.
AssociatedClient Field: These are related clients without a hierarchical relationship.
• Currently a field on the tblCs table is too limiting and
• Clients that are related for non-SLA reasons, each client has their own agreement
• Can have implications with service retention or other issues.
Solution:
- To Implement a new structure where individual rows such as MIND227 - MIND227 are retrieved from the db rather than IND227 - IND229
2.Change the structure of the tbcg
3.Move the capture of Associatedclients Field into a separate table [ID, ClientID1 , ClientID2..... , ClientRelationshipReasonID, CreatedDate]
What I should have should like:
ID ClientID1 ClientID2 ReasonTypeID
1 201 301 1
2 201 302 1
3 201 303 1
4 303 401 1
5 601 701 3
IF OBJECT_ID('tempdb..#tblCGs') IS NOT NULL DROP TABLE #tblCGs
IF OBJECT_ID('tempdb..#tblCs') IS NOT NULL DROP TABLE #tblCs
--Create ClientGroups table.
CREATE TABLE #tblCGs
(
[GroupID] int NOT NULL, ---- IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[GroupMasterCustKey] varchar(100) NOT NULL, ---[MasterCAN]
[GroupFromCustKey] varchar(100) NOT NULL, --- CONSTRAINT [DF_tblCGs_GroupFromCustKey] DEFAULT (' '),
[GroupToCustKey] varchar(100) NOT NULL -- CONSTRAINT [DF_tblCGs_GroupToCustKey] DEFAULT (' '),
)
-- Populate the table with values.
INSERT INTO #tblCGs VALUES
(988, N'MIN036', N'MIND227', N'MIND229')
, (668, N'08035635', N'31036422', N'31036422')
,(669, N'08035635', N'31035623', N'31035623')
-- Create Customer table.
CREATE TABLE #tblCs ---Customers
([CustomerID] int not null ------IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
,[CustomerKey] varchar(100) NOT NULL --- [ChildCAN]
,[CompanyName] varchar(300) NULL
,[AssociatedClient] varchar(20)
,[CreatedOn] Datetime
)
--Populate the table with values.
INSERT INTO #tblCs VALUES
(72894, N'MIND227', N'MIND Mind in Haringey', N'MIN036','2011-11-16 00:00:00.000')
,(93469, N'MIND228', N'Caerphilly Borough Mind', N'MIN036','2014-03-08 00:00:00.000')
,(98199, N'MIND229', N'Brecon and District Mind', N'MIN036,BDC004,POW044','2014-11-26 00:00:00.000')
,(61705, N'31036422', N'Revive', N'Null','2010-04-01 09:13:10.550')
,(61729, N'31035623', N'Jysk Ltd', N'Null','2010-04-01 12:56:16.837')
--- Current Queries
SELECT *
FROM #tblCGs AS CG
INNER JOIN #tblCs AS C
ON C.CustomerKey >= CG.GroupFromCustKey
AND C.CustomerKey <= CG.GroupToCustKey
This is just an example desired result. Demoralization of parts of the hierarch - Like Adjacency list ornested sets I think
Thanks
Ziggy