August 27, 2013 at 2:38 pm
I have a table with two columns, DealID & NetOutDeals (consider this as foreign key to DealID). I am trying to find the Order in which I can execute my deals.
--Create Table
CREATE TABLE [DealExecutionOrder](
[DealID] [int] ,
[NetOutDeals] [varchar](30)
) ON [PRIMARY]
--Inserting Sample Data
INSERT INTO [DealExecutionOrder]
VALUES (1, '3,10,12'), (2, '3,5,7'), (3, '0'), (4, '6'), (5, '3'), (6, '0'), (7, '0'), (10,'0'), (12,'5')
Here are the business rules:
1. value 0 in NetOutDeals means no DealID dependencies. I can straight away execute DealID with 0 value in NetOutDeals first.
2. From here, I can execute a deal only if all deals in the corresponding NetOutDeals are executed. For eg: DealID 1 can be executed only after 3, 10, 12 (again 12 can be executed only after 5 (again 5 can be executed only after 3)).
I need to come up with the hierarchical order of DealID to process my deals. Order does not matter at same level. Eg: 3, 6, 7, 10 can come in any order but should be before next immediate deals that depend only on this list.
So output DealID order should be like this: (6,10,7,3), (5, 4), (2, 12), (1)
Order does not matter within the brackets.
Can someone help me to achieve this?
Thanks,
Naveen.
Every thought is a cause and every condition an effect
August 27, 2013 at 2:44 pm
Naveen PK (8/27/2013)
I have a table with two columns, DealID & NetOutDeals (consider this as foreign key to DealID). I am trying to find the Order in which I can execute my deals.
--Create Table
CREATE TABLE [DealExecutionOrder](
[DealID] [int] ,
[NetOutDeals] [varchar](30)
) ON [PRIMARY]
--Inserting Sample Data
INSERT INTO [DealExecutionOrder]
VALUES (1, '3,10,12'), (2, '3,5,7'), (3, '0'), (4, '6'), (5, '3'), (6, '0'), (7, '0'), (10,'0'), (12,'5')
Here are the business rules:
1. value 0 in NetOutDeals means no DealID dependencies. I can straight away execute DealID with 0 value in NetOutDeals first.
2. From here, I can execute a deal only if all deals in the corresponding NetOutDeals are executed. For eg: DealID 1 can be executed only after 3, 10, 12 (again 12 can be executed only after 5 (again 5 can be executed only after 3)).
I need to come up with the hierarchical order of DealID to process my deals. Order does not matter at same level. Eg: 3, 6, 7, 10 can come in any order but should be before next immediate deals that depend only on this list.
So output DealID order should be like this: (6,10,7,3), (5, 4), (2, 12), (1)
Order does not matter within the brackets.
Can someone help me to achieve this?
HUH?
Your description is very unclear. You also should look into normalization. You are storing multiple values in a single column. This violates 1NF and makes your work here a lot more difficult than it needs to be.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 27, 2013 at 2:54 pm
For simplicity, I have taken only needed columns. In reality, DealID itself is a FK. NetOutDeals is coma separated list that is also a FK similar to DealID. I request to ignore normalization here.
Btw, I tried using recursive cte after getting data into a temp table using a string splitter. I am not able to figure out a way to find new subset of DealID data based ONLY on previously obtained DealID set.
Thanks,
Naveen.
Every thought is a cause and every condition an effect
August 27, 2013 at 3:09 pm
This is what I tried but did not work. DealID=1 is coming before 12 in the output.
Please let me know if I am not clear.
--String Splitter keeping data into temp table
SELECT DEALID, SUBSTRING(NETOUTDEALS, n, CHARINDEX(',', NETOUTDEALS+',', n)-n) as netout
INTO #NETORDER FROM [dbo].DealExecutionOrder
CROSS JOIN (select number from master..spt_values where type='p') as numbers(n)
WHERE n<=len(NETOUTDEALS) + 1 and substring (',' + NETOUTDEALS, n,1)=','
-- cte to find order
;
WITH CTE AS
(
SELECT DEALID, NETOUT, 0 AS EXECORDER FROM #NETORDER WHERE NETOUT = '0'
UNION ALL
--recursive part
SELECT NN.DEALID, NN.NETOUT, EXECORDER+1 FROM #NETOrder nn
JOIN CTE ON NN.NETOUT=CTE.dealid
)
SELECT * FROM CTE
In the recursive part of above query, if I take DealIDs where All of its NetOutDeals are in CTE list, then I am good. What condition/filter to be used here?
Thanks,
Naveen.
Every thought is a cause and every condition an effect
August 29, 2013 at 11:50 am
Let me put the problem in different words so that it is easy to understand the context.
Assume the case to be an organization assigning employees to do annual reviews to others employees (from above: DealPartID as EmployeeID and NetOutDeals as ReviewID). ReviewID can get reviewed by multiple employeeIDs. An employee can start/do the review only if the particular reviewID completed all his reviewIDs or has no pending reviewIDs.
EmployeeIDs that have nothing to review (ReviewID=0) should be my first set of list (3, 6, 7, 10). EmployeedIDs who can start their review now are 4,5 ( should be my second set) as they need to review 6, 3 who dont have pending ReviewIDs. Here not employeeIDs 1 or 2 because 1 has reviewID 12 who did not complete all his reviews. so on...
I hope I can expect responses now 🙁
Please let me know if I am still not clear.
Sample Data in table is like this. Use the splitter logic above if you need this data in table.
EmployeeID- ReviewID
1 - 3
1 - 10
1 - 12
2 - 3
2 - 5
2 - 7
3 - 0
4 - 6
5 - 3
6 - 0
7 - 0
10 - 0
12 - 5
Thanks,
Naveen.
Every thought is a cause and every condition an effect
August 29, 2013 at 4:30 pm
Just to update, I got the solution.
--String Splitter keeping data into temp table
SELECT DEALID, SUBSTRING(NETOUTDEALS, n, CHARINDEX(',', NETOUTDEALS+',', n)-n) as netout
INTO #NETORDER FROM [dbo].DealExecutionOrder
CROSS JOIN (select number from master..spt_values where type='p') as numbers(n)
WHERE n<=len(NETOUTDEALS) + 1 and substring (',' + NETOUTDEALS, n,1)=','
-- cte to find order
;
WITH CTE AS
(
SELECT DEALID, NETOUT, 0 AS EXECORDER FROM #NETORDER WHERE NETOUT = '0'
UNION ALL
--recursive part
SELECT NN.DEALID, NN.NETOUT, EXECORDER+1 FROM #NETOrder nn
JOIN CTE ON NN.NETOUT=CTE.dealid
)
SELECT DealID, Max(execorder) as execorder FROM CTE
Group by DealID
order by Max(execorder)
Thanks,
Naveen.
Every thought is a cause and every condition an effect
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply