Click here to Skip to main content
14,659,729 members
Home » Discussions » Database
   

Database

 
AnswerRe: OLE DB Data Types to C# Data Type Mapping Pin
Richard MacCutchan25-Dec-17 20:55
mveRichard MacCutchan25-Dec-17 20:55 
AnswerRe: OLE DB Data Types to C# Data Type Mapping Pin
Mycroft Holmes25-Dec-17 21:33
professionalMycroft Holmes25-Dec-17 21:33 
GeneralRe: OLE DB Data Types to C# Data Type Mapping Pin
User9874325-Dec-17 22:57
professionalUser9874325-Dec-17 22:57 
GeneralRe: OLE DB Data Types to C# Data Type Mapping Pin
jschell26-Dec-17 7:39
Memberjschell26-Dec-17 7:39 
GeneralRe: OLE DB Data Types to C# Data Type Mapping Pin
User9874314-Jan-18 10:44
professionalUser9874314-Jan-18 10:44 
SuggestionRe: OLE DB Data Types to C# Data Type Mapping Pin
Richard Deeming8-Jan-18 7:38
mveRichard Deeming8-Jan-18 7:38 
GeneralRe: OLE DB Data Types to C# Data Type Mapping Pin
User9874314-Jan-18 10:50
professionalUser9874314-Jan-18 10:50 
QuestionT-SQL and denomalization of Parent and Child hierarchy Tables Pin
Member 1358789920-Dec-17 21:41
MemberMember 1358789920-Dec-17 21:41 
The Problem:
Parent & child relationship currently captured in tblCGs and tbcs tables. The MasterClient are held in tblCGs table while Children are stored in tblCs tables using tblCGs. GroupMasterCustKey as key and CustomerKey as Child key.
• Currently a tblCGs table allows the linking of Parent using (GroupMasterCustKey) to child tblcs - (CustomerKey) table which may or may not exist.
AssociatedClient Field in the tblcs table: These are related clients without any hierarchical relationship.
• Currently the AssociatedClient on the tblCs table is too limiting varchar(20) and
can have implications or cause issues.

Proposed Solution:
• To Implement a new table structure where individual rows such as MIND227 - MIND227(customer ranges) are retrieved from the table rather than IND227 - IND229
• Move the Associatedclients Field in tblcs into a separate table with the following fields (ID, ClientID1, ClientID2..... , ClientRelationshipReasonID, and CreatedDate)

Please see the sample code below

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

The desired output should like this and not output of the above query
ID ClientID1 ClientID2 ReasonTypeID
1 201 301 1
2 201 302 1
3 201 303 1
4 303 401 1
5 601 701 3

Thanks for your help
AnswerRe: T-SQL and denomalization of Parent and Child hierarchy Tables Pin
Victor Nijegorodov21-Dec-17 1:54
MemberVictor Nijegorodov21-Dec-17 1:54 
QuestionTo convert an Excel file to CSV and then move that data from CSV to oracle database. Pin
sai.201218-Dec-17 8:23
Membersai.201218-Dec-17 8:23 
AnswerRe: To convert an Excel file to CSV and then move that data from CSV to oracle database. Pin
jschell18-Dec-17 15:51
Memberjschell18-Dec-17 15:51 
QuestionRe: To convert an Excel file to CSV and then move that data from CSV to oracle database. Pin
Richard MacCutchan18-Dec-17 21:35
mveRichard MacCutchan18-Dec-17 21:35 
QuestionMicrosoft SQL Server VS PostgresSQL Pin
Zeyad Jalil18-Dec-17 0:02
professionalZeyad Jalil18-Dec-17 0:02 
AnswerRe: Microsoft SQL Server VS PostgresSQL Pin
Nathan Minier18-Dec-17 1:13
professionalNathan Minier18-Dec-17 1:13 
AnswerRe: Microsoft SQL Server VS PostgresSQL Pin
jschell18-Dec-17 15:52
Memberjschell18-Dec-17 15:52 
QuestionOverwrite from DB as windows Services Pin
Member 1204258517-Dec-17 18:46
MemberMember 1204258517-Dec-17 18:46 
AnswerRe: Overwrite from DB as windows Services Pin
Victor Nijegorodov17-Dec-17 20:54
MemberVictor Nijegorodov17-Dec-17 20:54 
QuestionBuying of referrals Pin
Member 1357942916-Dec-17 0:13
MemberMember 1357942916-Dec-17 0:13 
AnswerRe: Buying of referrals Pin
Richard MacCutchan16-Dec-17 0:49
mveRichard MacCutchan16-Dec-17 0:49 
GeneralRe: Buying of referrals Pin
Member 1357942916-Dec-17 2:41
MemberMember 1357942916-Dec-17 2:41 
GeneralRe: Buying of referrals Pin
OriginalGriff16-Dec-17 2:43
mveOriginalGriff16-Dec-17 2:43 
QuestionSybase query returns blank/empty for char fields Pin
Member 1354244115-Dec-17 6:56
MemberMember 1354244115-Dec-17 6:56 
AnswerRe: Sybase query returns blank/empty for char fields Pin
jschell16-Dec-17 9:06
Memberjschell16-Dec-17 9:06 
GeneralRe: Sybase query returns blank/empty for char fields Pin
Member 1354244117-Dec-17 1:33
MemberMember 1354244117-Dec-17 1:33 
GeneralRe: Sybase query returns blank/empty for char fields Pin
Member 1354244119-Dec-17 13:36
MemberMember 1354244119-Dec-17 13:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.