There must be a simple way - 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 2012 Forums

Transact-SQL (2012)

There must be a simple way

Author

Topic

Crow
Starting Member

3 Posts

Posted - 2014-03-07 : 10:53:46

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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-07 : 11:05:52

Can you post sample data in a consumable format (i.e. Create Table and Insert)?

This sounds like a recursive CTE type solution.

Crow
Starting Member

3 Posts

Posted - 2014-03-07 : 11:13:08

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)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-07 : 16:31:12

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

Crow
Starting Member

3 Posts

Posted - 2014-03-10 : 04:52:11

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources