SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 There must be a simple way
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Crow
Starting Member

3 Posts

Posted - 03/07/2014 :  10:53:46  Show Profile  Reply with Quote


I have a problem where in I'm trying to get the maximum progression number.

All I will know to start will be one of the Id numbers.

I have to get the RelatedJob
Find the JobId that matches the RelatedJob
do the above step until the RelatedJob is null and at that point get the Progression number.

I have been attempting a while loop that follows with little success. I have tried using SETs but nothing seems to work.


DECLARE @TaskId uniqueidentifier = 'EE1153EE-B513-4E98-8A46-738D21441537'

DECLARE @RelatedJob uniqueidentifier

DECLARE @Progression int

SELECT @RelatedJob = [RelatedJob] FROM dbo.[vw_Progression] WHERE [Id] = @TaskId
SELECT @Progression = [Progression] FROM dbo.[vw_Progression] WHERE [Id] = @TaskId


WHILE @RelatedJob is not null
BEGIN

SELECT @RelatedJob = [RelatedJob] FROM dbo.[vw_Progression] WHERE [JobId] = @RelatedJob
SELECT @Progression = [Progression] FROM dbo.[vw_Progression] WHERE [JobId] = @RelatedJob

END
print @Progression

Any help would be most appreciated

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 03/07/2014 :  11:05:52  Show Profile  Reply with Quote
Can you post sample data in a consumable format (i.e. Create Table and Insert)?

This sounds like a recursive CTE type solution.
Go to Top of Page

Crow
Starting Member

3 Posts

Posted - 03/07/2014 :  11:13:08  Show Profile  Reply with Quote
thx for looking here's some create table script

CREATE TABLE [dbo].[Progression](
[Id] [uniqueidentifier] NOT NULL,
[JobId] [uniqueidentifier] NOT NULL,
[RelatedJob] [uniqueidentifier],
[Progression] [INT]
)

INSERT INTO [dbo].[Progression] ([Id],[JobId],[RelatedJob],[Progression])
VALUES('A74B59A5-0A7C-42E0-9E02-89EDD737811C', '9460934F-26FA-4242-B78D-F927473AE2F8', NULL, 3)

INSERT INTO [dbo].[Progression] ([Id],[JobId],[RelatedJob],[Progression])
VALUES('04033A53-4A31-4844-8705-1527A3DDA5B1', '78391F5B-3B78-47D6-95BD-6C3DF5F1C970', '9460934F-26FA-4242-B78D-F927473AE2F8', 2)

INSERT INTO [dbo].[Progression] ([Id],[JobId],[RelatedJob],[Progression])
VALUES('EE1153EE-B513-4E98-8A46-738D21441537', 'C0ABF4AE-FAF2-4DDC-8DE1-8EE48461A508', '78391F5B-3B78-47D6-95BD-6C3DF5F1C970', 1)
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 03/07/2014 :  16:31:12  Show Profile  Reply with Quote
Here is one way:
DECLARE @TaskId uniqueidentifier = 'EE1153EE-B513-4E98-8A46-738D21441537';

WITH Cte AS
(
	SELECT 
		*
	FROM 
		dbo.Progression
	WHERE 
		ID = @TaskId

	UNION ALL

	SELECT 
		Progression.*
	FROM 
		dbo.Progression
	INNER JOIN
		Cte AS C
		ON C.RelatedJob = Progression.JobID 
)

SELECT MAX(Progression)
FROM Cte
Go to Top of Page

Crow
Starting Member

3 Posts

Posted - 03/10/2014 :  04:52:11  Show Profile  Reply with Quote
Lamprey

Thank you very much!

I always shy away from Union All but your solution works perfectly and over the small dataset that is being unioned the performance loss is negligible.

There are so many ways to do things that sometimes you can completely overlook methods.

Again thank you
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000