Order of a task path
-
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.
-
Can you provide me a screenshot of the sample output?
-
When you say screen shot...a screen shot of what exactly .what do you mean?
-
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 -
can you put expected output in an excel and send that over. So that I would have a better understanding of your requirement.
-
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....
-
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;If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
>> 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.
-
This was removed by the editor as SPAM
-
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 ascThis 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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply