Solved

Add sort order to recursive query

Posted on 2014-10-27
4
158 Views
Last Modified: 2014-10-29
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.
0
Comment
Question by:Koen Van Wielink
  • 2
  • 2
4 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 40406213
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
 
LVL 12

Author Comment

by:Koen Van Wielink
ID: 40407739
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
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 40408056
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
 
LVL 12

Author Closing Comment

by:Koen Van Wielink
ID: 40412620
Thanks a million! Works exactly as required.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
A short film showing how OnPage and Connectwise integration works.

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now