Add sort order to recursive query

I have a recursive MSSQL query which generates a hierarchy of batches in our system. What happens is that if we receive, for example, batch # 1, and we process this, the batch number changes to the next available number, say # 4. This batch could be further processed. Sometimes these processes result in multiple output batches. Batch # 4 could be split into batch # 11 and # 12. And so on and so forth.
The query extracts each batch and indicates of which batch it is a child by setting a level. So batch # 1 would be level 0, batch # 4 would be level 1, batch # 11 and 12 would both be level 2. This all works as expected, albeit a bit slow due to the sheer number of batches.
However, in the report (SSRS 2005) I need to display this parent - child relationship correctly. The issue here is that if I sort on the "level" column, it would not correctly show the relationships as it would first show all batches with level 0, then level 1, then level 2, and so on. If I have batch # 2 and batch # 3 with level 0 as well, it would display batch number 1, 2 and 3 first before showing batch # 4, which does not show that 4 is a child of 1.
Unfortunately It is not necessarily the case that the output batch numbers are always higher than the input batch numbers, as these numbers are taken from different ranges depending on the circumstances. So I could have an input batch of # 4002134 and an output batch of # 1014324 for example. So sorting by batch number is also not an option.
Ideally I'd like to introduce a sorting order into the table, but I don't know how I can do this other than using a cursor and going through it line by line and comparing it to the previous entry. If I do this, the query becomes too slow to handle.

The recursive part of the procedure looks like this:
With A

([Level],
Product_number,
Original_batch_number,
Previous_batch_number,
Batch_number,
Germ
)

AS

(
select	0 as 'Level',
		ld.Article_number as 'Product_number',
		ld.Original_batch_number,
		ld.Previous_batch_number,
		ld.batch_number,
		ld.Determination_1 as 'Germ'
from	REP_ABS_DATA.rs.Lots_detail ld
		inner join REP_ABS_DATA.rs.Products p
			on ld.Article_number = p.Product_number
where	ld.Batch_number = ld.Original_batch_number
and		p.Parentline_indicator = 0
and		ld.Crop_code IN (Select Val From fn_Split(@Crop_code))

union all

Select	A.[Level] + 1,
		ld.Article_number as 'Product_number',
		ld.original_batch_number,
		ld.Previous_batch_number,
		ld.batch_number,
		ld.determination_1 as 'Germ'
from	REP_ABS_DATA.rs.Lots_detail ld
		inner join A
			on ld.Previous_batch_number = A.batch_number
)

Open in new window


My expected output would be something like this:
Batch #:
1
    4
        11
        12
2
3
   16
   17
        19
5

etc.

I'm not sure if I'm explaining this clearly, if not feel free to ask for more specific details. I'm hoping someone here can help me with this.
Thanks.
LVL 13
Koen Van WielinkBusiness Intelligence SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Please post a concise and complete example. This includes table DDL and sample data INSERT statements. btw, I would guess the slow performance is caused by your usage of a user defined function. So this may at least make it faster:

DECLARE @Crop_Code TABLE ( Crop_Code INT );

INSERT  INTO @Crop_Code
        SELECT  DISTINCT
                Val
        FROM    fn_Split(@Crop_Code);

WITH    A ( [Level], Product_number, Original_batch_number, Previous_batch_number, Batch_number, Germ )
          AS ( SELECT   0 AS 'Level' ,
                        ld.Article_number AS 'Product_number' ,
                        ld.Original_batch_number ,
                        ld.Previous_batch_number ,
                        ld.batch_number ,
                        ld.Determination_1 AS 'Germ'
               FROM     REP_ABS_DATA.rs.Lots_detail ld
                        INNER JOIN REP_ABS_DATA.rs.Products p ON ld.Article_number = p.Product_number
                        INNER JOIN @Crop_Code CC ON CC.Crop_Code = ld.Crop_Code
               WHERE    ld.Batch_number = ld.Original_batch_number
                        AND p.Parentline_indicator = 0
               UNION ALL
               SELECT   A.[Level] + 1 ,
                        ld.Article_number AS 'Product_number' ,
                        ld.Original_batch_number ,
                        ld.Previous_batch_number ,
                        ld.Batch_number ,
                        ld.determination_1 AS 'Germ'
               FROM     REP_ABS_DATA.rs.Lots_detail ld
                        INNER JOIN A ON ld.Previous_batch_number = A.Batch_number
             )
    SELECT  *
    FROM    A;

Open in new window

0
Koen Van WielinkBusiness Intelligence SpecialistAuthor Commented:
Hi Ste5an,

Thanks for your reply. The statement below should contain enough sample data to illustrate  what I'm working with I think:

create table lots_detail
(	Article_number int
,	Original_batch_number int
,	Previous_batch_number int
,	batch_number int
,	Determination_1 numeric(5,2));

insert into lots_detail
values		(7055,	4077607	,0			,4077607	,79.00)
		,	(7055,  4077612	,4086720	,4089813	,96.00)
		,	(7055,	2247862	,1249536	,1249543	,NULL)
		,	(7055,	2247862	,1249536	,1249542	,97.50)
		,	(7055,	2247862	,2247862	,1249537	,0.00)
		,	(7055,	2247862	,2247862	,1249536	,97.50)
		,	(7055,	2247862	,0			,2247862	,0.00)
		,	(7055,	4077614	,4077614	,4086715	,NULL)
		,	(7055,	4077614	,4077614	,4086714	,96.00)
		,	(7055,	4077614	,0			,4077614	,82.00)
		,	(7055,	4077612	,4077612	,4086721	,NULL)
		,	(7055,	4077612	,4077612	,4086720	,96.50)
		,	(7055,	4077612	,0			,4077612	,87.00)
		,	(7055,	4077607	,4077607	,4086711	,NULL)
		,	(7055,	4077607	,4077607	,4086710	,90.00);
		
With A

([Level],
Product_number,
Original_batch_number,
Previous_batch_number,
Batch_number,
Germ
)

AS

(
select	0 as 'Level',
		ld.Article_number as 'Product_number',
		ld.Original_batch_number,
		ld.Previous_batch_number,
		ld.batch_number,
		ld.Determination_1 as 'Germ'
from	dbo.Lots_detail ld
where	ld.Batch_number = ld.Original_batch_number

union all

Select	A.[Level] + 1,
		ld.Article_number as 'Product_number',
		ld.original_batch_number,
		ld.Previous_batch_number,
		ld.batch_number,
		ld.determination_1 as 'Germ'
from	dbo.Lots_detail ld
		inner join A
			on ld.Previous_batch_number = A.batch_number
)

Select *
from A
-- The order statement here is not giving me the desired result.
order by	a.Original_batch_number
		,	a.Previous_batch_number
		,	a.Level;

drop table lots_detail;

Open in new window


Attached is an Excel file with the desired sorting order of this data. I don't think this can be achieved with the existing fields alone, and the only way to get it done is to add an extra sorting sequence. But like I said, other than going through the resulting data set line by line I'm not sure how this can be achieved.
Desired-sort-order.xlsx
0
ste5anSenior DeveloperCommented:
You need a path to order your data, e.g.

WITH    Hierarchy ( [Level], Product_number, Original_batch_number, Previous_batch_number, Batch_number, Germ, [Path] )
          AS ( SELECT   0 ,
                        ld.Article_number ,
                        ld.Original_batch_number ,
                        ld.Previous_batch_number ,
                        ld.batch_number ,
                        ld.Determination_1 ,
                        '\\' + CAST(ld.batch_number AS VARCHAR(MAX))
               FROM     lots_detail ld
               WHERE    ld.batch_number = ld.Original_batch_number                        
               UNION ALL
               SELECT   P.[Level] + 1 ,
                        C.Article_number ,
                        C.Original_batch_number ,
                        C.Previous_batch_number ,
                        C.batch_number ,
                        C.Determination_1 ,
                        P.[Path] + '\' + CAST(C.batch_number AS VARCHAR(MAX))
               FROM     lots_detail C
                        INNER JOIN Hierarchy P ON C.Previous_batch_number = P.Batch_number
             )
    SELECT  *
    FROM    Hierarchy A
    ORDER BY A.[Path];

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Koen Van WielinkBusiness Intelligence SpecialistAuthor Commented:
Thanks a million! Works exactly as required.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.