Order of a task path

Question

Post reply

Order of a task path

TSQL Tryer

SSCrazy Eights

Points: 8869

June 7, 2019 at 11:21 am

#3650999

Hi There,

I'm trying to work out the best way to work out a workflow to a particular set of tasks I have in the database.

Basically in table1 I have the name of the task and the id.

In table2 I have the id and the previousid in a table.

What I am trying to work out is how do I order the results to give me the task flow.

So below is my dummy data.

/****** Object:  Table [dbo].[table1]    Script Date: 07/06/2019 12:00:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[table1](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[table2]    Script Date: 07/06/2019 12:00:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[table2](
[id] [int] NOT NULL,
[previousid] [int] NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[table1] ON 

INSERT [dbo].[table1] ([id], [Name]) VALUES (4403, N'Post Inspection Results')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4404, N'Start SLA - 28 days to collect keys')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4405, N'Complete')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4406, N'Arrears')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4407, N'Valid EPC')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4408, N'Request EPC Survey')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4409, N'Final Fix Post Inspection Result')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4410, N'5.1 - Check for a valid EPC')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4411, N'11 - Raise Final Fix (not to be sent)')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4412, N'15 - Final Fix Ringback')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4413, N'5.2 - Is clear out or dis-infestation required')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4414, N'8 - Asbestos check and removal')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4415, N'12 - Complete Post-inspection')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4416, N'13 - Property Ready to Let')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4417, N'12.1 - Recall failed SOR Order')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4418, N'1 - Create the Void Episode')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4419, N'2.1 - New Void Property Available')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4420, N'2.2 - Pre-Tenancy Termination Visit')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4421, N'3 - Terminate Tenancy with confirmed End Date')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4422, N'4 - Raise Health and Safety Checks and assign to Voids Team')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4423, N'3.1 - Refer to Former Tenant Arrears process')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4424, N'6 - Add Voids Survey details to Case and Doc Storage')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4425, N'7 - Raise Pre-inspection Works Order')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4426, N'9 - Raise SORs under Voids Repair Request')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4427, N'10 - Approve or Decline any Variations')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4428, N'16 - Recall failed Final Fix Order')
INSERT [dbo].[table1] ([id], [Name]) VALUES (4429, N'14 - Arrange Final Fix Appointment')
SET IDENTITY_INSERT [dbo].[table1] OFF
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4422, 4406)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4423, 4406)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4408, 4407)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4426, 4407)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4416, 4403)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4417, 4403)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4419, 4418)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4420, 4418)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4418, 4404)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4421, 4420)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4406, 4421)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4422, 4423)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4410, 4422)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4413, 4422)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4407, 4410)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4424, 4413)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4425, 4424)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4414, 4425)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4426, 4408)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4427, 4426)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4411, 4427)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4415, 4411)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4403, 4415)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4416, 4417)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4426, 4414)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4429, 4416)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4412, 4429)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4409, 4412)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4405, 4428)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4428, 4409)
INSERT [dbo].[table2] ([id], [previousid]) VALUES (4405, 4409)

If I join the two tables together I get the below results

Note the second row returns a NULL - this sets out that this is fact the first task in the path. That has an id of 4404.

I then look where Previousid is 4404 to tell me the next task in the path....and so on.

Not sure how to achieve this so any help would be greatly appreciated.

Attachments:

You must be logged in to view attached files.

June 8, 2019 at 7:23 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

sam-1083699 SSC Eights! Points: 873 More actions · Answer 1

Can you provide me a screenshot of the sample output?

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 2

When you say screen shot...a screen shot of what exactly .what do you mean?

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

What are your expected results?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

sam-1083699 SSC Eights! Points: 873 More actions · Answer 4

can you put expected output in an excel and send that over. So that I would have a better understanding of your requirement.

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 5

Sorry guys. Didn't make myself clear.

The screen shot is simply a SELECT of table1 and table2, joining on the ID.

What table2 shows is the previous id of all the tasks - what I am trying to return is just a list of Id's but based on the Previous Id.

So based on the screen shot ID 4404 has no entry in table2.....so that would be the first ID returned.

I then want to look and see what in table2 has 4404 set as previous id - in this case it is 4418. So that would be the second row returned and so on.

So based on that logic I would want something returned like below

4404

4418

4419

4420

4421

4406

4422

4423

4413

4424

and so on....

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

I think you'll need recursion to solve this. Try running the following:

DROP TABLE IF EXISTS #table2;

CREATE TABLE #table2
(
    id INT NOT NULL --PRIMARY KEY CLUSTERED
   ,previousid INT NOT NULL
);

INSERT #table2
(
    id
   ,previousid
)
VALUES
(4422, 4406)
,(4423, 4406)
,(4408, 4407)
,(4426, 4407)
,(4416, 4403)
,(4417, 4403)
,(4419, 4418)
,(4420, 4418)
,(4418, 4404)
,(4421, 4420)
,(4406, 4421)
,(4422, 4423)
,(4410, 4422)
,(4413, 4422)
,(4407, 4410)
,(4424, 4413)
,(4425, 4424)
,(4414, 4425)
,(4426, 4408)
,(4427, 4426)
,(4411, 4427)
,(4415, 4411)
,(4403, 4415)
,(4416, 4417)
,(4426, 4414)
,(4429, 4416)
,(4412, 4429)
,(4409, 4412)
,(4405, 4428)
,(4428, 4409)
,(4405, 4409);

WITH recurse
AS (SELECT t.id
          ,t.previousid
    FROM #table2 t
    WHERE t.id = 4418
    UNION ALL
    SELECT t.id
          ,t.previousid
    FROM recurse r
        JOIN #table2 t
            ON  t.previousid = r.id)
SELECT recurse.id
FROM recurse;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 7

>> Basically in table1 I have the name of the task and the id. <<

Why do you think that "table_1" and "table_2" are useful names? Did you know the IDENTITY property is never a valid key in RDBMS? It is a file system leftover that counts physical insertion attempts to physical storage. It looks like the tasks have an ISO style outline to locate them.

>>What I am trying to work out is how do I order the results to give me the task flow. <<

CREATE TABLE Tasks

(task_id CHAR('5) NOT NULL PRIMARY KEY,

task_name VARCHAR('100) NOT NULL);

INSERT INTO Tasks

VALUES

('4403', 'Post Inspection Results')

('4404', 'Start SLA - 28 days to collect keys'),

('4405', 'Complete'),

('4406', 'Arrears'),

('4407', 'Valid EPC'),

('4408', 'Request EPC Survey'),

('4409', 'Final Fix Post Inspection Result'),

('4410', '5.1 - Check for a valid EPC'),

('4411', '11 - Raise Final Fix ('not to be sent)'),

('4412', '15 - Final Fix Ringback'),

('4413', '5.2 - Is clear out or dis-infestation required'),

('4414', '8 - Asbestos check and removal'),

('4415', '12 - Complete Post-inspectio '),

('4416', '13 - Property Ready to Let'),

('4417', '12.1 - Recall failed SOR Order'),

('4418', '1 - Create the Void Episode'),

('4419', '2.1 - New Void Property Available'),

('4420', '2.2 - Pre-Tenancy Termination Visit'),

('4421', '3 - Terminate Tenancy with confirmed End Date'),

('4422', '4 - Raise Health and Safety Checks and assign to Voids Team'),

('4423', '3.1 - Refer to Former Tenant Arrears process'),

('4424', '6 - Add Voids Survey details to Case and Doc Storage'),

('4425', '7 - Raise Pre-inspection Works Order'),

('4426', '9 - Raise SORs under Voids Repair Request'),

('4427', '10 - Approve or Decline any Variations'),

('4428', '16 - Recall failed Final Fix Order'),

('4429', '14 - Arrange Final Fix Appointment'),

CREATE TABLE Workflow

(task_id CHAR(5) NOT NULL

REFERENCES Tasks,

previous_task_id CHAR(5), --- null is start of workflow

REFERENCES Tasks,

UNIQUE (task_id, previous_task_id)

CHECK (task_id <> previous_task_id));

See the use of referential integrity constraints.

INSERT INTO Workflow

VALUES

('4422', '4406'),

('4423', '4406'),

('4408', '4407'),

('4426', '4407'),

('4416', '4403'),

('4417', '4403'),

('4419', '4418'),

('4420', '4418'),

('4418', '4404'),

('4421', '4420'),

('4406', '4421'),

('4422', '4423'),

('4410', '4422'),

('4413', '4422'),

('4407', '4410'),

('4424', '4413'),

('4425', '4424'),

('4414', '4425'),

('4426', '4408'),

('4427', '4426'),

('4411', '4427'),

('4415', '4411'),

('4403', '4415'),

('4416', '4417'),

('4426', '4414'),

('4429', '4416'),

('4412', '4429'),

('4409', '4412'),

('4405', '4428'),

('4428', '4409'),

('4405', '4409'),

(NULL, '4404);

I did not check for loops.

>>The second row returns a NULL - this sets out that this is fact the first task in the path. That has a task_id of 4404. <<

That is fine. Just use UNIQUE and not PRIMARY KEY. Tables are never sorted by definition, so you this makes no sense. If t were in a nested set model tree, we could talk about pre-order, in-order or post-order traversals.

Please post DDL and follow ANSI/ISO standards when asking for help.

shane.green 1227 SSC Enthusiast Points: 173 More actions · Answer 8

WITH singleTab([id], [name], [previousid]) AS (
    SELECT t1.id, t1.Name, t2.previousid
    FROM table1 t1
left join table2 t2 on t1.id = t2.id
),
recusive(id, Name, previousid, step, ids) AS (
    SELECT id, Name, previousid, 0 step, cast(id as varchar(max)) ids
    FROM singleTab 
    WHERE previousid is null
    UNION ALL
    SELECT m.id, m.name, m.previousid, n.step + 1 step, n.ids + ', ' + cast(m.id as varchar(max)) ids
    FROM singleTab as m, recusive n
    WHERE m.previousid = n.id
)
SELECT distinct * FROM recusive ORDER BY ids asc

This recursively walks through your data and prints out the list of jobs. However you branch at multiple points, so showing the list of preceding jobs made the sorting easier.

Annotation 2019-06-15 095744