Solved

Add sort order to recursive query

Posted on 2014-10-27
4
156 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 32

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 32

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 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

10 Experts available now in Live!

Get 1:1 Help Now