Dynamically Return Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamically Return Data

Author

Topic

priestlm
Starting Member

13 Posts

Posted - 2010-01-04 : 10:32:43

Hey guys, wonder if you can help with the following - ive spent 4 days on this and keep getting the wrong results....

I need to return data from the orders current step to a specified step description for the product in question... lets say i want to return everything up to & including "Pack"

Im using:


Select	Orders.Order,
		ProcessSteps.StepDescription
From	Orders Full Join 
		ProcessSteps.Product On Orders.Product like ProcessSteps.Product
Where	ProcessSteps.Step >= Order.CurrentStep and
		ProcessSteps.Step <=
			(Select Top 1 Min(ProcessSteps) As Step
				From	Orders Inner Join ProcessSteps on
						Orders.Product like ProcessSteps.Product
				Where	ProcessSteps.StepDescription like 'Pack' and 
						ProcessSteps.Step >= Orders.CurrentStep
			Group by Orders.Order)
Order By Orders.Order

The result set i want to return is:
001 Weld
001 Pack
002 Start
002 Trace
002 Pack

But its returning
001 Weld
001 Pack
001 End
002 Start
002 Trace
002 Pack
002 Weld
002 End

It doesnt seem to be working with the subquery, although if i run the subquery separatly, it returns which step pack is for each order...

Any help you can offer will be greatfully received....


Table 1 - Orders
Order	Product	CurrentStep
001	A	2
002	B	1

Table 2 - ProcessSteps
Product	Step	StepDescription
A	1	Start
A	2	Weld   
A	3	Pack
A	4	End
B	1	Start
B	2	Trace
B	3	Pack
B	4	Weld
B	5	End

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-04 : 11:35:38

This?
Sample data

declare @Orders table 
(Orderno int,	Product varchar(1),	CurrentStep int)
insert @Orders
select 001,	'A',	2
union all select 002,	'B',	1

declare @ProcessSteps table
(Product varchar(1),	Step int,	StepDescription varchar(10))
insert @ProcessSteps
select 'A',	1,	'Start'
union all select 'A',	2,	'Weld'
union all select 'A', 3, 'test'   
union all select 'A',	4,	'Pack'
union all select 'A',	5,	'End'
union all select 'B',	1,	'Start'
union all select 'B',	2,	'Trace'
union all select 'B',	3,	'Pack'
union all select 'B',	4,	'Weld'
union all select 'B',	5,	'End'

Query

select t.Orderno,t.StepDescription from 
(
select a.Orderno
,a.Product
,b.StepDescription
,b.Step
from @Orders a
left join @ProcessSteps b
on a.Product = b.Product
) t
inner join
(
select Product,Step,StepDescription from @ProcessSteps where StepDescription like 'Pack%'
) t1
on t.Product = t1.Product and t.Step <= t1.Step

balaganapathy.n
Starting Member

18 Posts

Posted - 2010-01-05 : 01:46:31

A slightly modified version of the above query, if you like to try.

declare @Orders table (Orderno int, Product varchar(1), CurrentStep int)
insert @Orders
select 1, 'A', 2 union all
select 2, 'B', 1

declare @ProcessSteps table (Product varchar(1), Step int, StepDescription varchar(10))
insert @ProcessSteps
select 'A', 1, 'Start'
union all select 'A', 2, 'Weld'
union all select 'A', 3, 'test'
union all select 'A', 4, 'Pack'
union all select 'A', 5, 'End'
union all select 'B', 1, 'Start'
union all select 'B', 2, 'Trace'
union all select 'B', 3, 'Pack'
union all select 'B', 4, 'Weld'
union all select 'B', 5, 'End'

SELECT A.Orderno, B.StepDescription
FROM @Orders AS A
INNER JOIN (SELECT * FROM @ProcessSteps O
WHERE EXISTS(SELECT 1 FROM @ProcessSteps I
WHERE O.Product = I.Product
AND O.Step <= I.Step
AND I.StepDescription = 'Pack')
) AS B
ON A.Product = B.Product

balaganapathy n.

priestlm
Starting Member

13 Posts

Posted - 2010-01-05 : 10:36:22

Thanks for the help guys, both sets of code are great... ive used the first sample from balaganapathy.n & there is a slight problem ...

If my Processsteps have multiple steps of the same description the code is returning the all process steps to the last instance, i need the code to return the process step to the the first instance, have you any ideas how to do that?

Thanks alot for you help



declare @Orders table (Orderno int, Product varchar(1), CurrentStep int)
insert @Orders 
select 1, 'A', 2 union all 
select 2, 'B', 1 union all 
select 3, 'A', 1

declare @ProcessSteps table (Product varchar(1), Step int, StepDescription varchar(10))
insert @ProcessSteps
select 'A', 1, 'Start'
union all select 'A', 1, 'Start'
union all select 'A', 2, 'Pack'
union all select 'A', 3, 'Weld'
union all select 'A', 4, 'Pack'
union all select 'A', 5, 'test' 
union all select 'A', 6, 'Pack'
union all select 'A', 7, 'End'
union all select 'B', 1, 'Start'
union all select 'B', 2, 'Pack'
union all select 'B', 3, 'Trace'
union all select 'B', 4, 'Pack'
union all select 'B', 5, 'Weld'
union all select 'B', 6, 'Pack'
union all select 'B', 7, 'End'

SELECT A.Orderno, B.StepDescription 
FROM @Orders AS A
INNER JOIN (SELECT * FROM @ProcessSteps O 
WHERE EXISTS(SELECT 1 FROM @ProcessSteps I, @Orders Z
WHERE O.Product = I.Product 
AND O.Step > Z.CurrentStep + 1
AND O.Step <= I.Step 
AND I.StepDescription like 'Pack')
) AS B
ON A.Product = B.Product

Order by Orderno, step

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-05 : 11:06:38

What is your expected output for this new set of sample data?

priestlm
Starting Member

13 Posts

Posted - 2010-01-05 : 11:29:26

At the moment the code returns:
1 Weld
1 Pack
1 test
1 Pack
2 Trace
2 Pack
2 Weld
2 Pack
3 Weld
3 Pack
3 test
3 Pack

But i want see
1 Weld
1 Pack
2 Trace
2 Pack
3 Weld
3 Pack

So the code only returns the data up until the first instance of 'Pack' for the given order

Thanks
Mark

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 03:54:20

can you explain how order 2 started with Trace?since current step is 1 shouldnt it start with Pack?

priestlm
Starting Member

13 Posts

Posted - 2010-01-06 : 04:00:04

Sorry - yes you are right, the output i want to see is:

1 Pack
2 Start
2 Pack
3 Start
3 Pack

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 04:09:23

[code]
SELECT o.Orderno,sd.StepDescription
FROM Orders o
CROSS APPLY (SELECT TOP 1 Step
FROM ProcessSteps
WHERE Product=o.Product
AND Step>=o.CurrentStep
AND StepDescription='Pack'
ORDER BY Step) t
CROSS APPLY (SELECT StepDescription
FROM ProcessSteps
WHERE Product=o.Product
AND Step>=o.CurrentStep
AND Step <= t.Step
) sd
[/code]

balaganapathy.n
Starting Member

18 Posts

Posted - 2010-01-06 : 05:18:12

Here find the modified script, check and let me know.

declare @Orders table (Orderno int, Product varchar(1), CurrentStep int)
insert @Orders
select 1, 'A', 2 union all
select 2, 'B', 1

declare @ProcessSteps table (Product varchar(1), Step int, StepDescription varchar(10))
insert @ProcessSteps
select 'A', 1, 'Start'
union all select 'A', 2, 'Weld'
union all select 'A', 3, 'test'
union all select 'A', 4, 'Pack'
union all select 'A', 5, 'End'
union all select 'A', 6, 'Pack'
union all select 'A', 7, 'End2'
union all select 'B', 1, 'Start'
union all select 'B', 2, 'Trace'
union all select 'B', 3, 'Pack'
union all select 'B', 4, 'Weld'
union all select 'B', 5, 'End'

SELECT A.Orderno, B.StepDescription
FROM @Orders AS A
INNER JOIN (
(SELECT O.* FROM @ProcessSteps O
INNER JOIN (SELECT Product, MIN(Step) AS Step, StepDescription FROM @ProcessSteps WHERE StepDescription = 'Pack' GROUP BY Product, StepDescription) I
ON O.Product = I.Product
AND O.Step <= I.Step)
) AS B
ON A.Product = B.Product
AND A.CurrentStep <= B.Step

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources