asked on

Sort - parent records but keep child records "under" them

I have the below results - a list of child records under each associative parent - what I would like to so is sort the parents first by alpha ASC, but still keep all of the children under their associated parent

tbaseflug

ASKER

Here is my current query:

SELECT [contractMasterID], [Contract ID],[Contract],[Contract Parent], sortID, totalCharge  FROM (
SELECT 
contractID AS [Contract ID]
,CASE 
	WHEN isParent = 1 THEN InsuranceCompanyName + ' - Parent'
	ELSE contractNumber + ' - ' + InsuranceCompanyName END AS [Contract],
CASE WHEN ContractMasterID IS NOT NULL AND isParent IS NULL or isParent = 0 
	 THEN (SELECT '('+ CAST(contractID AS varchar(30))+ ') ' + InsuranceCompanyName + ' - Parent' FROM cm_tblContractSummary x WHERE x.contractID = c.contractMasterID)
	 WHEN isParent = 1 THEN 'Parent' ELSE 'No Parent Assigned'END AS [Contract Parent]
,contractMasterID
,1 AS sortID
,SUM(ISNULL(defaultINpatientCharge,0) + ISNULL(defaultOutpatientCharge,0) + ISNULL(defaultEmergencyCharge,0)
	+ ISNULL(defaultUrgentCareCharge,0) + ISNULL(defaultNonPatientCharge,0) + ISNULL(defaultAmbulatoryCharge,0)) AS [totalCharge]

FROM cm_tblContractSummary c
WHERE db = 'dbAlaskaNativePhysicn'
GROUP BY contractID, isParent, insuranceCompanyName, contractNumber, contractMasterID

) z
WHERE [Contract Parent] <> 'No Parent Assigned'
ORDER BY [contractMasterID],  
CASE WHEN [Contract Parent] <> 'Parent' THEN Contract ELSE CAST([Contract ID] AS varchar(20)) END

Open in new window

volking

You'll need to artificially create a sort field that can be sorted properly

Using your above example,

select
contractMasterID,
ContractID,
Contract,
[Contract Parent],
SortID,
Cast(Contract as varchar(500)) + cast(ContractMasterID as varchar(50)) + cast(ContractID as varchar(50)) as MyCompositeKey
Order by MyCompositKey

Depending on data min/max you may need to push contractMasterID and ContractID into a space filled right set condition to get what you need.

volking

This might help

CREATE FUNCTION [dbo].[ZeroFillRightSet] (
	@Valu int,
	@CharsWide int
)
RETURNS varchar(20)
AS
BEGIN
	DECLARE @Ret varchar(20),
			@BigInt bigint 
	select @BigInt= (@Valu + 100000000000000000)
	select @Ret = cast(@BigInt as varchar(20))
	select @Ret = RIGHT(@Ret, @CharsWide)
	RETURN @Ret
END

Open in new window

volking

so ...
select
contractMasterID,
ContractID,
Contract,
[Contract Parent],
SortID,
Cast(Contract as varchar(500)) + cast(ContractMasterID as varchar(50)) + cast(ContractID as varchar(50)) as MyCompositeKey
Order by MyCompositKey

becomes

select
contractMasterID,
ContractID,
Contract,
[Contract Parent],
SortID,
Cast(Contract as varchar(500)) + dbo.ZeroFillRightSet(ContractMasterID, 10) + dbo.ZeroFillRightSet(ContractID, 10) as MyCompositeKey
Order by MyCompositKey

volking

And using your snippet ...

SELECT [contractMasterID], [Contract ID],[Contract],[Contract Parent], sortID, totalCharge,
	Cast(Contract as varchar(500)) + dbo.ZeroFillRightSet(ContractMasterID, 10) + dbo.ZeroFillRightSet(ContractID, 10) as MyCompositeKey
			FROM (
			SELECT 
			contractID AS [Contract ID]
			,CASE 
				WHEN isParent = 1 THEN InsuranceCompanyName + ' - Parent'
				ELSE contractNumber + ' - ' + InsuranceCompanyName END AS [Contract],
			CASE WHEN ContractMasterID IS NOT NULL AND isParent IS NULL or isParent = 0 
				 THEN (SELECT '('+ CAST(contractID AS varchar(30))+ ') ' + InsuranceCompanyName + ' - Parent' FROM cm_tblContractSummary x WHERE x.contractID = c.contractMasterID)
				 WHEN isParent = 1 THEN 'Parent' ELSE 'No Parent Assigned'END AS [Contract Parent]
			,contractMasterID
			,1 AS sortID
			,SUM(ISNULL(defaultINpatientCharge,0) + ISNULL(defaultOutpatientCharge,0) + ISNULL(defaultEmergencyCharge,0)
				+ ISNULL(defaultUrgentCareCharge,0) + ISNULL(defaultNonPatientCharge,0) + ISNULL(defaultAmbulatoryCharge,0)) AS [totalCharge]

			FROM cm_tblContractSummary c
			WHERE db = 'dbAlaskaNativePhysicn'
			GROUP BY contractID, isParent, insuranceCompanyName, contractNumber, contractMasterID

			) z
WHERE [Contract Parent] <> 'No Parent Assigned'
ORDER BY MyCompositeKey

Open in new window

tbaseflug

ASKER

Using that approach - you can see from the below - the parents are sorted, but the children, sharing the same contractMasterID, are not under each parent respectively

volking

Including ContractMasterID and ContractID in the sort field prevents two like named contracts from sorting as a single. For example, if you had two AETNA's, one with ContractMasterID = 123 and ther other with ContractMasterID = 345 the resultant sort fields would be ...

AETNA00000123###ContractID###
and
AETNA00000345###ContractID###

volking

May I see the TSQL which produced above result?

tbaseflug

ASKER

SELECT [contractMasterID], [Contract ID],[Contract],[Contract Parent], sortID, totalCharge,
      Cast(Contract as varchar(500)) + dbo.ZeroFillRightSet(ContractMasterID, 10) + dbo.ZeroFillRightSet([Contract ID], 10) as MyCompositeKey
                  FROM (
                  SELECT
                  contractID AS [Contract ID]
                  ,CASE
                        WHEN isParent = 1 THEN InsuranceCompanyName + ' - Parent'
                        ELSE contractNumber + ' - ' + InsuranceCompanyName END AS [Contract],
                  CASE WHEN ContractMasterID IS NOT NULL AND isParent IS NULL or isParent = 0
                         THEN (SELECT '('+ CAST(contractID AS varchar(30))+ ') ' + InsuranceCompanyName + ' - Parent' FROM cm_tblContractSummary x WHERE x.contractID = c.contractMasterID)
                         WHEN isParent = 1 THEN 'Parent' ELSE 'No Parent Assigned'END AS [Contract Parent]
                  ,contractMasterID
                  ,1 AS sortID
                  ,SUM(ISNULL(defaultINpatientCharge,0) + ISNULL(defaultOutpatientCharge,0) + ISNULL(defaultEmergencyCharge,0)
                        + ISNULL(defaultUrgentCareCharge,0) + ISNULL(defaultNonPatientCharge,0) + ISNULL(defaultAmbulatoryCharge,0)) AS [totalCharge]

                  FROM cm_tblContractSummary c
                  WHERE db = 'dbAlaskaNativePhysicn'
                  GROUP BY contractID, isParent, insuranceCompanyName, contractNumber, contractMasterID

                  ) z
WHERE [Contract Parent] <> 'No Parent Assigned'
ORDER BY MyCompositeKey

volking

hmmmmm got it
how many rows does this query return? 10 million? I hope not, because you're going to need a query that references itself.

Give me a couple of minutes to work up a sample.

tbaseflug

ASKER

usually less than 1000 total rows

volking

First off ... remember ... I do NOT have an underlying DB to test this on (grin) meaning there may be syntax errors.

	CREATE TABLE #LocTable 
	(
		[contractMasterID] int,
		[Contract ID] int,
		[Contract] varchar(500),
		[Contract Parent] varchar(500), 
		sortID int, 
		totalCharge money, -- not sure this is right type
		CompositeKey varchar(1000)
	)
	CREATE UNIQUE CLUSTERED INDEX IX_1 on #LocTable (contractMasterID)

insert ([contractMasterID], [Contract ID], [Contract], [Contract Parent], sortID, totalCharge)
	SELECT [contractMasterID], [Contract ID],[Contract],[Contract Parent], sortID, totalCharge,
		FROM (
		SELECT 
		contractID AS [Contract ID]
		,CASE 
			WHEN isParent = 1 THEN InsuranceCompanyName + ' - Parent'
			ELSE contractNumber + ' - ' + InsuranceCompanyName END AS [Contract],
		CASE WHEN ContractMasterID IS NOT NULL AND isParent IS NULL or isParent = 0 
			 THEN (SELECT '('+ CAST(contractID AS varchar(30))+ ') ' + InsuranceCompanyName + ' - Parent' FROM cm_tblContractSummary x WHERE x.contractID = c.contractMasterID)
			 WHEN isParent = 1 THEN 'Parent' ELSE 'No Parent Assigned'END AS [Contract Parent]
		,contractMasterID
		,1 AS sortID
		,SUM(ISNULL(defaultINpatientCharge,0) + ISNULL(defaultOutpatientCharge,0) + ISNULL(defaultEmergencyCharge,0)
			+ ISNULL(defaultUrgentCareCharge,0) + ISNULL(defaultNonPatientCharge,0) + ISNULL(defaultAmbulatoryCharge,0)) AS [totalCharge]

		FROM cm_tblContractSummary c
		WHERE db = 'dbAlaskaNativePhysicn'
		GROUP BY contractID, isParent, insuranceCompanyName, contractNumber, contractMasterID

		) z
	WHERE [Contract Parent] <> 'No Parent Assigned'

update a
	set   a.CompositeKey = 
		Cast(b.Contract as varchar(500)) + 
		dbo.ZeroFillRightSet(a.ContractMasterID, 10) + 
		dbo.ZeroFillRightSet(a.ContractID, 10)
	from #LocTable a
		inner join #LocTable b 
		on a.contractMasterID = b.contractMasterID
		and b.[Contract Parent]='Parent'

Select * from #LocTable order by CompositeKey

Open in new window

volking

Told ya! (smile)
Line 13 should read

insert #LocTable ([contractMasterID], [Contract ID], [Contract], [Contract Parent], sortID, totalCharge)

tbaseflug

ASKER

I think that is really close - but is there any way to get the "parent" at the top of the list - seems to be sorting ok - but the parent is at the bottom of the childre

volking

and ...
line 11 may need to read

CREATE INDEX IX_1 on #LocTable (contractMasterID)

volking

Wait one ...

volking

Try this

	CREATE TABLE #LocTable 
	(
		[contractMasterID] int,
		[Contract ID] int,
		[Contract] varchar(500),
		[Contract Parent] varchar(500), 
		sortID int, 
		totalCharge money, -- not sure this is right type
		CompositeKey varchar(1000),
		IsParent bit
	)
	CREATE INDEX IX_1 on #LocTable (contractMasterID)

insert #LocTable([contractMasterID], [Contract ID], [Contract], [Contract Parent], sortID, totalCharge)
	SELECT [contractMasterID], [Contract ID],[Contract],[Contract Parent], sortID, totalCharge,
		FROM (
		SELECT 
		contractID AS [Contract ID]
		,CASE 
			WHEN isParent = 1 THEN InsuranceCompanyName + ' - Parent'
			ELSE contractNumber + ' - ' + InsuranceCompanyName END AS [Contract],
		CASE WHEN ContractMasterID IS NOT NULL AND isParent IS NULL or isParent = 0 
			 THEN (SELECT '('+ CAST(contractID AS varchar(30))+ ') ' + InsuranceCompanyName + ' - Parent' FROM cm_tblContractSummary x WHERE x.contractID = c.contractMasterID)
			 WHEN isParent = 1 THEN 'Parent' ELSE 'No Parent Assigned'END AS [Contract Parent]
		,contractMasterID
		,1 AS sortID
		,SUM(ISNULL(defaultINpatientCharge,0) + ISNULL(defaultOutpatientCharge,0) + ISNULL(defaultEmergencyCharge,0)
			+ ISNULL(defaultUrgentCareCharge,0) + ISNULL(defaultNonPatientCharge,0) + ISNULL(defaultAmbulatoryCharge,0)) AS [totalCharge]

		FROM cm_tblContractSummary c
		WHERE db = 'dbAlaskaNativePhysicn'
		GROUP BY contractID, isParent, insuranceCompanyName, contractNumber, contractMasterID

		) z
	WHERE [Contract Parent] <> 'No Parent Assigned'

update a
	set   a.CompositeKey = 
		Cast(b.Contract as varchar(500)) + 
		dbo.ZeroFillRightSet(a.ContractMasterID, 10) + 
		dbo.ZeroFillRightSet(a.ContractID, 10)
	from #LocTable a
		inner join #LocTable b 
		on a.contractMasterID = b.contractMasterID
		and b.[Contract Parent]='Parent'

Update #LocTable set 
	IsParent = CASE 
     WHEN [Contract Parent]='Parent' THEN 1
     ELSE 0
	END

Select * from #LocTable order by IsParent desc, CompositeKey

Open in new window

volking

opps - line 53 should be reversed

Select * from #LocTable order by CompositeKey, IsParent desc

tbaseflug

ASKER

Whoops - not sure if I was clear - instead of (in previous post) - which essentially works - but instead of:

child
child
parent

I wanted it:

parent
child
child

volking

By The Way ... looks like you work for an insurance company - What da HECK you doing working at THIS time of night?

volking

Yeah - try my post

opps - line 53 should be reversed

Select * from #LocTable order by CompositeKey, IsParent desc

tbaseflug

ASKER

slaving away on a Friday night for a healthcare company actually - tons of fun....

the last query - even with the updated last line - produces the following - as you can see - parents at the bottom of the children - but the parents are sorting as expected

volking

Okay ... question
If the PARENT is moved to the TOP, what ORDER should the underlying children take?
numeric [Contract ID] or
alpha [Contract]

tbaseflug

ASKER

alpha [Contract]

ASKER CERTIFIED SOLUTION

volking

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

volking

Oh, also reversed IsParent update from 1-0 to 0-1 to make sure it sorts right

volking

Opps - line 12 is missing a paren

cast(a.[Contract] as varchar(500))

tbaseflug

ASKER

WOW! Awesome! Thanks so much! Works perfectly! Saved my bacon and lets me go home finally... Thanks!

volking

Welcome ...
I use this technique a lot, create an intermediate table, then slice-n-dice the intermediate table a dozen ways to sunday.

Later
I'm gonna go eat dinner!

tbaseflug

ASKER

Learned something new tonight - much thanks!

Sort - parent records but keep child records &quot;under&quot; them

Sort - parent records but keep child records "under" them