August 10, 2015 at 3:02 pm
I hope I can explain this issue I am having well enough for everyone to understand it. Here it goes:
I am trying to SELECT data based on custom groups of that data. For example and in its simplest form:
SELECT COUNT(*)
FROMdbo.People
WHERE Current_Status = ‘A’
GROUP BY People_Code
The People_Code is the difficult part. The code represents the building that they work in. However, some buildings have multiple People_Codes. Kind of like multiple departments within a building.
For example:
Building NamePeople_CodeEmployee Count
Building A617535
Building B985665
Building C529212
Building C529932
Building C419816
Building D326974
Building D781024
Building E25365
Each building has a main People_code which, for this example, could be any one of the codes for the building. For example: Main code for building C can be 5292 and for building D it can be 7810.
Applying a variation (which is what I cannot figure out) of the SELECT statement above to this table, the result set for Building C must be the combined employee count of all three People_codes and must be represented by the main code of 5292 as a single row. Building D would have a row using code 7810 but will combine the employee count of codes 7810 and 3269.
I built a conversion table that would match up the main code with all of its related codes but just couldn’t seem to make it dance the way I want it to.
People_CodeNameGroupNameGroupPeopleCode
6175Building ABuilding A6175
9856Building BBuilding B9856
5292Building CBuildingCGroup5292
5299Building C AnnexBuildingCGroup5292
4198Building C Floor6BuildingCGroup5292
Etc…
The whole query is much more involved than just the simple SELECT statement used here, but if I can get this to work, I’m sure I can apply it to the full query.
Any Thoughts? Thank you.
August 10, 2015 at 3:22 pm
August 11, 2015 at 6:23 am
dan.tuma (8/10/2015)
I hope I can explain this issue I am having well enough for everyone to understand it. Here it goes:I am trying to SELECT data based on custom groups of that data. For example and in its simplest form:
SELECT COUNT(*)
FROMdbo.People
WHERE Current_Status = ‘A’
GROUP BY People_Code
The People_Code is the difficult part. The code represents the building that they work in. However, some buildings have multiple People_Codes. Kind of like multiple departments within a building.
For example:
Building NamePeople_CodeEmployee Count
Building A617535
Building B985665
Building C529212
Building C529932
Building C419816
Building D326974
Building D781024
Building E25365
Each building has a main People_code which, for this example, could be any one of the codes for the building. For example: Main code for building C can be 5292 and for building D it can be 7810.
Applying a variation (which is what I cannot figure out) of the SELECT statement above to this table, the result set for Building C must be the combined employee count of all three People_codes and must be represented by the main code of 5292 as a single row. Building D would have a row using code 7810 but will combine the employee count of codes 7810 and 3269.
I built a conversion table that would match up the main code with all of its related codes but just couldn’t seem to make it dance the way I want it to.
People_CodeNameGroupNameGroupPeopleCode
6175Building ABuilding A6175
9856Building BBuilding B9856
5292Building CBuildingCGroup5292
5299Building C AnnexBuildingCGroup5292
4198Building C Floor6BuildingCGroup5292
Etc…
The whole query is much more involved than just the simple SELECT statement used here, but if I can get this to work, I’m sure I can apply it to the full query.
Any Thoughts? Thank you.
I get the feeling this is kind of in the design phase. Here's a quick sample setup of tables to illustrate one way of going about this. It can easily be expanded.
DECLARE @BUILDINGS AS TABLE (
[Building Name] varchar(30),
People_Code int,
[Employee Count] int,
IS_Master bit
);
INSERT INTO @BUILDINGS ([Building Name], People_Code, [Employee Count], IS_Master)
VALUES ('Building A', 6175, 35, 1),
('Building B', 9856, 65, 1),
('Building C', 5292, 12, 1),
('Building C', 5299, 32, 0),
('Building C', 4198, 16, 0),
('Building D', 3269, 74, 0),
('Building D', 7810, 24, 1),
('Building E', 2536, 5, 1);
WITH MASTER_BUILDINGS AS (
SELECT B.[Building Name], B.People_Code
FROM @BUILDINGS AS B
WHERE B.IS_Master = 1
)
SELECT MB.People_Code AS Master_Code, MB.[Building Name],
SUM(B.[Employee Count]) AS [Employee Count]
FROM MASTER_BUILDINGS AS MB
INNER JOIN @BUILDINGS AS B
ON MB.[Building Name] = B.[Building Name]
GROUP BY MB.People_Code, MB.[Building Name]
ORDER BY MB.[Building Name];
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 21, 2015 at 1:10 pm
Thank you SSCrazy.
I was able to adapt your code to what I was attempting to accomplish and it worked. I've been fighting with this for quite some time now and it has been driving me nuts. I have about 6 more, very detailed aggregate scripts to apply this solution to yet, but I'm at least moving forward now.
Thank you once again,
Dan Tuma
September 5, 2015 at 1:48 pm
dan.tuma (8/21/2015)
Thank you SSCrazy.I was able to adapt your code to what I was attempting to accomplish and it worked. I've been fighting with this for quite some time now and it has been driving me nuts. I have about 6 more, very detailed aggregate scripts to apply this solution to yet, but I'm at least moving forward now.
Thank you once again,
Dan Tuma
You're welcome, Forum Newbie 😀 ... <<tongue held firmly in cheek>>
My member name is sgmunson, not SSCrazy. That's just a "title" that the forum provides to someone who has posted as often as I have. I'm guessing you just weren't aware of that "feature"...
Let us know if you need more assistance with any of those other scripts.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 6, 2015 at 10:53 am
If this is in the design phase, then anticipate the worst and that would be them adding another level to the mix. Don't say it won't happen because you have no control over what happens.
My recommendation is that you convert the idea of PeopleCode to a real Adjacency List (Parent/Child) hierarchy instead of messing around with flags. Believe it or not, you were on your way to doing that in your original post above.
Once that's done, there are some absolutely remarkable things that you can do very quickly with the hierarchy to make your queries against such information just absolutely fly.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply