How to establish a relationship in SQL Server
-
Good morning and Merry Christmas everyone! I'm not sure if anyone has posted something like this before, and if they have, I couldn't find it. And I apologize for the repeat.
I'm looking to take the minimum value of a relationship between customers and accounts and apply it to all accounts, thereby establishing a relationship between all the related customers and accounts (this process is also known as 'Householding'). For example, Joe & Betty have accounts at a bank. Joe has 2 and Betty has 3. They have different CustomerIDs but are signors on each others accounts. So a total of 5 accounts between 2 customers gives us 10 total accounts in this relationship. Joe has his 2 + 3 from Betty and vice versa.
I'm working thru Business Objects in a 3rd-party hosted Oracle data warehouse. I have VERY limited control on what I can do. But if I can figure it out in SQL Server, then I can translate it over to an Oracle query. Here's the rub: 1) I can't use temp tables 2) I can't build a stored procedure 3) I'm pretty sure I can't use a cursor. So I'm trying to find out how to do it dynamically thru either subqueries or CTEs or both. My boss has done it in Excel using the MINIFS function. So a relationship exists if: you have the same Account# as another Customer, or if the Account# is within the same Customer, you have multiple accounts. So for the example data below, Customers 50 & 75 have a relationship because they have the same Account#s and that gives them a total of 10 Accounts for their relationship.
The results should look like this (I cut the screenshot off to make the image smaller, but you get the jist):
CREATE TABLE [dbo].[Relationships](
[CustNbr] [bigint] NULL,
[AcctNbr] [varchar](50) NULL
) ON [PRIMARY]
INSERT INTO Relationships
VALUES
(50, 11111),
(50, 22222),
(50, 33333),
(50, 44444),
(50, 55555),
(75, 11111),
(75, 22222),
(75, 33333),
(75, 44444),
(75, 55555),
(100, 10000),
(100, 20000),
(100, 30000),
(100, 40000),
(100, 50000),
(125, 10000),
(125, 20000),
(125, 30000),
(125, 40000),
(125, 50000),
(150, 12345),
(150, 23456),
(150, 34567),
(150, 45678),
(150, 56789),
(200, 12345),
(200, 23456),
(200, 34567),
(200, 45678),
(200, 56789)
I appreciate any help given. This has kept me up several nights.
-
Create Foreign Key Relationships
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
The following will do it without anything "fancy" that might not work in Oracle, which I've almost totally forgotten about.
WITH
cteMinCustNbr AS
(--==== Calculate the minimum CustNbr for each AcctNmb
SELECT MinCustNbr = MIN(CustNbr)
,AcctNbr
FROM dbo.Relationships
GROUP BY AcctNbr
)--==== Then just do a simple join.
-- The LEFT JOIN gets rid of a couple of nasty table spools
-- and a sort, which is more expensive than the 2 table scans.
SELECT rel.CustNbr
,rel.AcctNbr
,CustAcct = CONCAT(mcn.MinCustNbr,'-',mcn.AcctNbr)
FROM dbo.Relationships rel
LEFT JOIN cteMinCustNbr mcn ON rel.AcctNbr = mcn.AcctNbr
ORDER BY rel.CustNbr, rel.AcctNbr
;Here are the run results:
As a bit of a sidebar, I would only list the "relational" CustNbr rather than the concatenation to make it easier to join back to the original (minimum) CustNbr for any row if you decided to materialize this result set as a lookup table or to add a column to the original table.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply