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.
Go to Top of Page

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)
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -