Mixing
SQL - Make an assumption for a field based on the value of that field for other members of a group
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a SQL question and I'm unsure about how to phrase this for a Google search. I am hoping that if I post it here, maybe somebody can either help me, or point me in the right direction.
We have an Account entity, which is the physical store/warehouse. The Account is owned by a Parent Account, which we use for billinginvoicing. Some of our larger Customers do not have a central place for billing, like a headquarters or corporate Accounts Payable. So we group these larger Accounts (since they don't share the same ParentAccountId) using an Organization entity.
So for example, Joe's Furniture has 10 locations/stores, so we have 10 Accounts in our system. We don't bill corporate for the shipments, but instead, invoice the stores individually. So we have 10 Parent Accounts for Joe's Furniture. However, they all fall under one Joe's Furniture Organization.
Having said that, we also use this Organization entity for larger Customers that only have 1 billing Account. The Parent Account and Organization are at the same level, but offer a different method of grouping.
Now that I've given the details about the structure of our Accounts, this is what I need to accomplish:
In my first set of Accounts, I want to group them by ParentAccountId. For any Account that has a different OrganizationId, I want to make an assumption for that OrganizationId based on the OrganizationId of all the Accounts in this group.
So if you look at AccountId's A01 - A10, they all have the same Parent Account, but A10 has a different OrganizationId than A01 - A09. The probability that Account A10 falls under ORG01 is pretty high considering that the rest of the Accounts owned by P01 fall under that OrganizationId.
+-----------+-----------------+----------------+-----------------+
| AccountId | ParentAccountId | OrganizationId | AccountName |
+-----------+-----------------+----------------+-----------------+
| A01 | P01 | ORG01 | Joe's Furniture |
| A02 | P01 | ORG01 | Joe's Furniture |
| A03 | P01 | ORG01 | Joe's Furniture |
| A04 | P01 | ORG01 | Joe's Furniture |
| A05 | P01 | ORG01 | Joe's Furniture |
| A06 | P01 | ORG01 | Joe's Furniture |
| A07 | P01 | ORG01 | Joe's Furniture |
| A08 | P01 | ORG01 | Joe's Furniture |
| A09 | P01 | ORG01 | Joe's Furniture |
| A10 | P01 | GROUP01 | Joe's Furniture |
+-----------+-----------------+----------------+-----------------+
Being that not all the Accounts share the same Parent Account, my 2nd set of Accounts would be grouped by the AccountName.
+-----------+-----------------+----------------+------------------+
| AccountId | ParentAccountId | OrganizationId | AccountName |
+-----------+-----------------+----------------+------------------+
| A11 | P02 | ORG02 | Tom's Mattresses |
| A12 | P03 | ORG02 | Tom's Mattresses |
| A13 | P04 | ORG02 | Tom's Mattresses |
| A14 | P05 | ORG02 | Tom's Mattresses |
| A15 | P06 | GROUP02 | Tom's Mattresses |
| A16 | P07 | GROUP02 | Tom's Mattresses |
+-----------+-----------------+----------------+------------------+
In the example above, I would want to be able to make an assumption for the OrganizationId for A15 and A16. To complicate things more, I would need to have some sort of ranking for the OrganizationId, where the preference is ORG% rather than GROUP%. But I can figure that out later. For now, I just need to find a way to accomplish this.
Does anyone know if there is a special name for what I'm trying to accomplish? I really just need somebody to point me in the right direction, and I'll go and do the research. But it would be greatly appreciated if someone could help me.
Thanks!
sql
asked Jan 3 at 2:57
SuperNovaSuperNova
1
add a comment |
I have a SQL question and I'm unsure about how to phrase this for a Google search. I am hoping that if I post it here, maybe somebody can either help me, or point me in the right direction.
We have an Account entity, which is the physical store/warehouse. The Account is owned by a Parent Account, which we use for billinginvoicing. Some of our larger Customers do not have a central place for billing, like a headquarters or corporate Accounts Payable. So we group these larger Accounts (since they don't share the same ParentAccountId) using an Organization entity.
So for example, Joe's Furniture has 10 locations/stores, so we have 10 Accounts in our system. We don't bill corporate for the shipments, but instead, invoice the stores individually. So we have 10 Parent Accounts for Joe's Furniture. However, they all fall under one Joe's Furniture Organization.
Having said that, we also use this Organization entity for larger Customers that only have 1 billing Account. The Parent Account and Organization are at the same level, but offer a different method of grouping.
Now that I've given the details about the structure of our Accounts, this is what I need to accomplish:
In my first set of Accounts, I want to group them by ParentAccountId. For any Account that has a different OrganizationId, I want to make an assumption for that OrganizationId based on the OrganizationId of all the Accounts in this group.
So if you look at AccountId's A01 - A10, they all have the same Parent Account, but A10 has a different OrganizationId than A01 - A09. The probability that Account A10 falls under ORG01 is pretty high considering that the rest of the Accounts owned by P01 fall under that OrganizationId.
+-----------+-----------------+----------------+-----------------+
| AccountId | ParentAccountId | OrganizationId | AccountName |
+-----------+-----------------+----------------+-----------------+
| A01 | P01 | ORG01 | Joe's Furniture |
| A02 | P01 | ORG01 | Joe's Furniture |
| A03 | P01 | ORG01 | Joe's Furniture |
| A04 | P01 | ORG01 | Joe's Furniture |
| A05 | P01 | ORG01 | Joe's Furniture |
| A06 | P01 | ORG01 | Joe's Furniture |
| A07 | P01 | ORG01 | Joe's Furniture |
| A08 | P01 | ORG01 | Joe's Furniture |
| A09 | P01 | ORG01 | Joe's Furniture |
| A10 | P01 | GROUP01 | Joe's Furniture |
+-----------+-----------------+----------------+-----------------+
Being that not all the Accounts share the same Parent Account, my 2nd set of Accounts would be grouped by the AccountName.
+-----------+-----------------+----------------+------------------+
| AccountId | ParentAccountId | OrganizationId | AccountName |
+-----------+-----------------+----------------+------------------+
| A11 | P02 | ORG02 | Tom's Mattresses |
| A12 | P03 | ORG02 | Tom's Mattresses |
| A13 | P04 | ORG02 | Tom's Mattresses |
| A14 | P05 | ORG02 | Tom's Mattresses |
| A15 | P06 | GROUP02 | Tom's Mattresses |
| A16 | P07 | GROUP02 | Tom's Mattresses |
+-----------+-----------------+----------------+------------------+
In the example above, I would want to be able to make an assumption for the OrganizationId for A15 and A16. To complicate things more, I would need to have some sort of ranking for the OrganizationId, where the preference is ORG% rather than GROUP%. But I can figure that out later. For now, I just need to find a way to accomplish this.
Does anyone know if there is a special name for what I'm trying to accomplish? I really just need somebody to point me in the right direction, and I'll go and do the research. But it would be greatly appreciated if someone could help me.
Thanks!
sql
asked Jan 3 at 2:57
SuperNovaSuperNova
1
4 hours and no answers... Consider reading stackoverflow.com/help/mcve.
– jarlh
Jan 3 at 7:25
add a comment |
I have a SQL question and I'm unsure about how to phrase this for a Google search. I am hoping that if I post it here, maybe somebody can either help me, or point me in the right direction.
We have an Account entity, which is the physical store/warehouse. The Account is owned by a Parent Account, which we use for billinginvoicing. Some of our larger Customers do not have a central place for billing, like a headquarters or corporate Accounts Payable. So we group these larger Accounts (since they don't share the same ParentAccountId) using an Organization entity.
So for example, Joe's Furniture has 10 locations/stores, so we have 10 Accounts in our system. We don't bill corporate for the shipments, but instead, invoice the stores individually. So we have 10 Parent Accounts for Joe's Furniture. However, they all fall under one Joe's Furniture Organization.
Having said that, we also use this Organization entity for larger Customers that only have 1 billing Account. The Parent Account and Organization are at the same level, but offer a different method of grouping.
Now that I've given the details about the structure of our Accounts, this is what I need to accomplish:
In my first set of Accounts, I want to group them by ParentAccountId. For any Account that has a different OrganizationId, I want to make an assumption for that OrganizationId based on the OrganizationId of all the Accounts in this group.
So if you look at AccountId's A01 - A10, they all have the same Parent Account, but A10 has a different OrganizationId than A01 - A09. The probability that Account A10 falls under ORG01 is pretty high considering that the rest of the Accounts owned by P01 fall under that OrganizationId.
+-----------+-----------------+----------------+-----------------+
| AccountId | ParentAccountId | OrganizationId | AccountName |
+-----------+-----------------+----------------+-----------------+
| A01 | P01 | ORG01 | Joe's Furniture |
| A02 | P01 | ORG01 | Joe's Furniture |
| A03 | P01 | ORG01 | Joe's Furniture |
| A04 | P01 | ORG01 | Joe's Furniture |
| A05 | P01 | ORG01 | Joe's Furniture |
| A06 | P01 | ORG01 | Joe's Furniture |
| A07 | P01 | ORG01 | Joe's Furniture |
| A08 | P01 | ORG01 | Joe's Furniture |
| A09 | P01 | ORG01 | Joe's Furniture |
| A10 | P01 | GROUP01 | Joe's Furniture |
+-----------+-----------------+----------------+-----------------+
Being that not all the Accounts share the same Parent Account, my 2nd set of Accounts would be grouped by the AccountName.
+-----------+-----------------+----------------+------------------+
| AccountId | ParentAccountId | OrganizationId | AccountName |
+-----------+-----------------+----------------+------------------+
| A11 | P02 | ORG02 | Tom's Mattresses |
| A12 | P03 | ORG02 | Tom's Mattresses |
| A13 | P04 | ORG02 | Tom's Mattresses |
| A14 | P05 | ORG02 | Tom's Mattresses |
| A15 | P06 | GROUP02 | Tom's Mattresses |
| A16 | P07 | GROUP02 | Tom's Mattresses |
+-----------+-----------------+----------------+------------------+
In the example above, I would want to be able to make an assumption for the OrganizationId for A15 and A16. To complicate things more, I would need to have some sort of ranking for the OrganizationId, where the preference is ORG% rather than GROUP%. But I can figure that out later. For now, I just need to find a way to accomplish this.
Does anyone know if there is a special name for what I'm trying to accomplish? I really just need somebody to point me in the right direction, and I'll go and do the research. But it would be greatly appreciated if someone could help me.
Thanks!
sql
asked Jan 3 at 2:57
SuperNovaSuperNova
1
I have a SQL question and I'm unsure about how to phrase this for a Google search. I am hoping that if I post it here, maybe somebody can either help me, or point me in the right direction.
We have an Account entity, which is the physical store/warehouse. The Account is owned by a Parent Account, which we use for billinginvoicing. Some of our larger Customers do not have a central place for billing, like a headquarters or corporate Accounts Payable. So we group these larger Accounts (since they don't share the same ParentAccountId) using an Organization entity.
So for example, Joe's Furniture has 10 locations/stores, so we have 10 Accounts in our system. We don't bill corporate for the shipments, but instead, invoice the stores individually. So we have 10 Parent Accounts for Joe's Furniture. However, they all fall under one Joe's Furniture Organization.
Having said that, we also use this Organization entity for larger Customers that only have 1 billing Account. The Parent Account and Organization are at the same level, but offer a different method of grouping.
Now that I've given the details about the structure of our Accounts, this is what I need to accomplish:
In my first set of Accounts, I want to group them by ParentAccountId. For any Account that has a different OrganizationId, I want to make an assumption for that OrganizationId based on the OrganizationId of all the Accounts in this group.
So if you look at AccountId's A01 - A10, they all have the same Parent Account, but A10 has a different OrganizationId than A01 - A09. The probability that Account A10 falls under ORG01 is pretty high considering that the rest of the Accounts owned by P01 fall under that OrganizationId.
+-----------+-----------------+----------------+-----------------+
| AccountId | ParentAccountId | OrganizationId | AccountName |
+-----------+-----------------+----------------+-----------------+
| A01 | P01 | ORG01 | Joe's Furniture |
| A02 | P01 | ORG01 | Joe's Furniture |
| A03 | P01 | ORG01 | Joe's Furniture |
| A04 | P01 | ORG01 | Joe's Furniture |
| A05 | P01 | ORG01 | Joe's Furniture |
| A06 | P01 | ORG01 | Joe's Furniture |
| A07 | P01 | ORG01 | Joe's Furniture |
| A08 | P01 | ORG01 | Joe's Furniture |
| A09 | P01 | ORG01 | Joe's Furniture |
| A10 | P01 | GROUP01 | Joe's Furniture |
+-----------+-----------------+----------------+-----------------+
Being that not all the Accounts share the same Parent Account, my 2nd set of Accounts would be grouped by the AccountName.
+-----------+-----------------+----------------+------------------+
| AccountId | ParentAccountId | OrganizationId | AccountName |
+-----------+-----------------+----------------+------------------+
| A11 | P02 | ORG02 | Tom's Mattresses |
| A12 | P03 | ORG02 | Tom's Mattresses |
| A13 | P04 | ORG02 | Tom's Mattresses |
| A14 | P05 | ORG02 | Tom's Mattresses |
| A15 | P06 | GROUP02 | Tom's Mattresses |
| A16 | P07 | GROUP02 | Tom's Mattresses |
+-----------+-----------------+----------------+------------------+
In the example above, I would want to be able to make an assumption for the OrganizationId for A15 and A16. To complicate things more, I would need to have some sort of ranking for the OrganizationId, where the preference is ORG% rather than GROUP%. But I can figure that out later. For now, I just need to find a way to accomplish this.
Does anyone know if there is a special name for what I'm trying to accomplish? I really just need somebody to point me in the right direction, and I'll go and do the research. But it would be greatly appreciated if someone could help me.
Thanks!
sql
sql
asked Jan 3 at 2:57
SuperNovaSuperNova
1
asked Jan 3 at 2:57
SuperNovaSuperNova
1
asked Jan 3 at 2:57
SuperNovaSuperNova
1
asked Jan 3 at 2:57
SuperNovaSuperNova
1
asked Jan 3 at 2:57
SuperNovaSuperNova
1
1
4 hours and no answers... Consider reading stackoverflow.com/help/mcve.
– jarlh
Jan 3 at 7:25
add a comment |
4 hours and no answers... Consider reading stackoverflow.com/help/mcve.
– jarlh
Jan 3 at 7:25
4 hours and no answers... Consider reading stackoverflow.com/help/mcve.
– jarlh
Jan 3 at 7:25
4 hours and no answers... Consider reading stackoverflow.com/help/mcve.
– jarlh
Jan 3 at 7:25
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54015765%2fsql-make-an-assumption-for-a-field-based-on-the-value-of-that-field-for-other%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54015765%2fsql-make-an-assumption-for-a-field-based-on-the-value-of-that-field-for-other%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
4 hours and no answers... Consider reading stackoverflow.com/help/mcve.
– jarlh
Jan 3 at 7:25