ORDER By Based on Company then SubCompany
- Lynn Pettis - Thursday, December 28, 2017 3:09 PMjslist - Thursday, December 28, 2017 3:06 PM
Hello All,
Sorry all for not getting back sooner. Year end and all of a sudden there's a million things to do. Thank you very much for all the ideas. . Also thanks for the tips on how to post the correctly. Will use them.
Tried use SSChampion's answer. Works pretty well. 90% Correct. The Companies/Groups never have the same name. And the Sub-Company codes are always different. There's only about 1,500 records so not worried so much about how much time it takes to pull the data.
BUT just found out there are a few Companies that are Groups who have Sub-Groups. And some of those Sub-Groups are Groups of another set of Sub-Groups.Here's a sample of the data.
CREATE TABLE #companies
(
CompanyName varchar(20),
CompanyStatus varchar(20),
CompanyNumber varchar(10),
GroupNumber varchar(10)
);INSERT INTO #companies
VALUES
('Blackwell', 'group','A123G','A123'),
('Symphony','subCompany','s678','A123'),
('Blends','subCompany', 't123','A123'),
('Tiger', 'group','T123G','T123'),
('Pepper', 'group','Z987G','Z987'),
('Great Falls','subCompany','f545','T123'),
('BusinessA','group', 'b123G','b123'),
('BusA_SubGroup', 'subcompany','j123','b123'),
('BusA_SubGroup_AS_Group', group','A075G','b123'),
('BusB_SubGroup_of_BusA_SubGroup_Group','subCompany','A017','A075'),
('BusC_SubGroup_of_BusA_SubGroup_Group','subCompany','A180','A075');
This is what I've been working with.
WITH ordered_parent_companies AS
(
SELECT GroupNumber, rn=ROW_NUMBER() OVER (ORDER BY CompanyName)
FROM #companies
WHERE CompanyStatus='group'
)SELECT c.*
FROM #companies c
INNER JOIN
ordered_parent_companies opc ON c.GroupNumber=opc.GroupNumber
ORDER BY rn ASC,
CASE WHEN CompanyStatus='group' THEN 0 ELSE 1 END ASC,
--The second sort could simply be on CompanyStatus if only 'group' and 'subCompany' are possible values
--but I wanted to make sure if there were other possible values for CompanyStatus that the 'group'
--company sorted first
CompanyName ASC;
Any suggestions on handling the
Group with SubGroups That are Groups of SubGroups.
Right now the data is coming out like this:
Any ideas are greatly appreciated.
Thanks
JSAnd what is the expected output supposed to be? It helps to know what we are trying to accomplish/
The Output should be like this:
A123 Blackwell
T123 Blends
S678 SymphonyA075 BusA_SubGroup_AS_Group
A017 BusB_SubGroup_of_BusA_SubGroup_Group
A180 BusC_SubGroup_of_BusA_SubGroup_GroupB123 BusinessA
J123 BusA_SubGroup T123 Tiger
T123 Great Falls - jslist - Friday, December 29, 2017 7:07 AMLynn Pettis - Thursday, December 28, 2017 3:09 PMjslist - Thursday, December 28, 2017 3:06 PM
Hello All,
Sorry all for not getting back sooner. Year end and all of a sudden there's a million things to do. Thank you very much for all the ideas. . Also thanks for the tips on how to post the correctly. Will use them.
Tried use SSChampion's answer. Works pretty well. 90% Correct. The Companies/Groups never have the same name. And the Sub-Company codes are always different. There's only about 1,500 records so not worried so much about how much time it takes to pull the data.
BUT just found out there are a few Companies that are Groups who have Sub-Groups. And some of those Sub-Groups are Groups of another set of Sub-Groups.Here's a sample of the data.
CREATE TABLE #companies
(
CompanyName varchar(20),
CompanyStatus varchar(20),
CompanyNumber varchar(10),
GroupNumber varchar(10)
);INSERT INTO #companies
VALUES
('Blackwell', 'group','A123G','A123'),
('Symphony','subCompany','s678','A123'),
('Blends','subCompany', 't123','A123'),
('Tiger', 'group','T123G','T123'),
('Pepper', 'group','Z987G','Z987'),
('Great Falls','subCompany','f545','T123'),
('BusinessA','group', 'b123G','b123'),
('BusA_SubGroup', 'subcompany','j123','b123'),
('BusA_SubGroup_AS_Group', group','A075G','b123'),
('BusB_SubGroup_of_BusA_SubGroup_Group','subCompany','A017','A075'),
('BusC_SubGroup_of_BusA_SubGroup_Group','subCompany','A180','A075');
This is what I've been working with.
WITH ordered_parent_companies AS
(
SELECT GroupNumber, rn=ROW_NUMBER() OVER (ORDER BY CompanyName)
FROM #companies
WHERE CompanyStatus='group'
)SELECT c.*
FROM #companies c
INNER JOIN
ordered_parent_companies opc ON c.GroupNumber=opc.GroupNumber
ORDER BY rn ASC,
CASE WHEN CompanyStatus='group' THEN 0 ELSE 1 END ASC,
--The second sort could simply be on CompanyStatus if only 'group' and 'subCompany' are possible values
--but I wanted to make sure if there were other possible values for CompanyStatus that the 'group'
--company sorted first
CompanyName ASC;
Any suggestions on handling the
Group with SubGroups That are Groups of SubGroups.
Right now the data is coming out like this:Any ideas are greatly appreciated.
Thanks
JSAnd what is the expected output supposed to be? It helps to know what we are trying to accomplish/
The Output should be like this:
A123 Blackwell
T123 Blends
S678 SymphonyA075 BusA_SubGroup_AS_Group
A017 BusB_SubGroup_of_BusA_SubGroup_Group
A180 BusC_SubGroup_of_BusA_SubGroup_GroupB123 BusinessA
J123 BusA_SubGroup T123 Tiger
T123 Great FallsMade a few minor changes, hope this works for you. Of course you need to be sure to understand what the query is doing as you will be the one supporting it, not us.
DROP TABLE [#companies]
CREATE TABLE #companies
(
CompanyName varchar(120),
CompanyStatus varchar(20),
CompanyNumber varchar(10),
GroupNumber varchar(10)
);INSERT INTO #companies
VALUES
('Blackwell', 'group','A123G','A123'),
('Symphony','subCompany','s678','A123'),
('Blends','subCompany', 't123','A123'),
('Tiger', 'group','T123G','T123'),
('Pepper', 'group','Z987G','Z987'),
('Great Falls','subCompany','f545','T123'),
('BusinessA','group', 'b123G','b123'),
('BusA_SubGroup', 'subcompany','j123','b123'),
('BusA_SubGroup_AS_Group', 'group','A075G','b123'),
('BusB_SubGroup_of_BusA_SubGroup_Group','subCompany','A017','A075'),
('BusC_SubGroup_of_BusA_SubGroup_Group','subCompany','A180','A075');WITH rCTE AS (
SELECT
[c].[CompanyName]
, [c].[CompanyStatus]
, [c].[CompanyNumber]
, [c].[GroupNumber]
, [SortKey] = CAST([c].[CompanyName] + [c].[CompanyNumber] + [c].[GroupNumber] AS VARCHAR(MAX))
FROM
[#companies] AS [c]
WHERE
[c].[CompanyStatus] = 'group'
UNION ALL
SELECT
[c].[CompanyName]
, [c].[CompanyStatus]
, [c].[CompanyNumber]
, [c].[GroupNumber]
, [SortKey] = [r].[SortKey] + CAST([c].[CompanyName] + [c].[CompanyNumber] + [c].[GroupNumber] AS VARCHAR(MAX))
FROM
[#companies] AS [c]
INNER JOIN [rCTE] [r]
ON [r].[CompanyNumber] = [c].[GroupNumber] + 'G'
AND [c].[CompanyStatus] = 'subCompany'
)
SELECT
[rCTE].[CompanyName]
, [rCTE].[CompanyStatus]
, [rCTE].[CompanyNumber]
, [rCTE].[GroupNumber]
FROM
[rCTE]
ORDER BY
[rCTE].[SortKey]; -
This also gives the expected results.
;
WITH Company_Groups AS
(
SELECT c.*, FIRST_VALUE(c.CompanyName) OVER(PARTITION BY g.GroupNumber ORDER BY c.CompanyStatus ROWS UNBOUNDED PRECEDING) AS GroupName
FROM #companies c
CROSS APPLY ( VALUES(ISNULL(STUFF(CompanyNumber, PATINDEX('%G', CompanyNumber), 1, ''), GroupNumber)) ) g(GroupNumber)
)
SELECT CompanyName, CompanyStatus, CompanyNumber, GroupNumber
FROM Company_Groups
ORDER BY GroupName, CompanyStatus, CompanyName
;Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Friday, December 29, 2017 9:03 AM
I really need to start working with SQL Server 2012 and newer features/ Unfortunately we just recently moved to SQL Server 2012 so I usually fall back to 2008 R2 tactics as that is what I know.
- Lynn Pettis - Friday, December 29, 2017 9:06 AM
And here I thought I was behind, because I haven't downloaded SQL 2017 and started playing around with graph tables.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Friday, December 29, 2017 9:11 AM
I have a 2016 server to play with and waiting for a 2017 server.
- Lynn Pettis - Friday, December 29, 2017 9:06 AM
Thanks Drew this worked.🙂
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply