Results 1 to 5 of 5

Thread: Help with Query

  1. #1
    Join Date
    Aug 2013
    Posts
    2

    Unanswered: Help with Query

    Hello,

    I build a query back in the day, but I am trying to remember how I did it.

    I have three tables. #TESTS contains all the tests required. #EXEC_ORDER order contains the prerequisites definition. #SUBGROUP is the transaction table.

    Code:
    CREATE TABLE #SUBGROUP (
    	SYSID INT IDENTITY(1,1) NOT NULL,
    	TESTID INT NOT NULL,
    	COMPLETED BIT NOT NULL DEFAULT 'FALSE'
    )
    
    CREATE TABLE #TESTS (
    	SYSID INT IDENTITY(1,1) NOT NULL,
    	TESTID INT NOT NULL
    )
    
    CREATE TABLE #EXEC_ORDER (
    	SYSID INT IDENTITY(1,1) NOT NULL,
    	TESTID1 INT NOT NULL,
    	TESTID2 INT NOT NULL
    )
    
    
    INSERT INTO #TESTS (TESTID) VALUES (1),(2),(3),(4),(5)
    INSERT INTO #EXEC_ORDER (TESTID1,TESTID2) VALUES (2,3),(2,4),(5,4)
    I am looking for Tests #1 and #2 would be returned. When Test #2 is completed, then Tests #1, #3 and #4 would be returned. When Test #4 is completed, then Tests #1, #3, and #5 would be returned.

    Code:
    SELECT t.TESTID,s.COMPLETED
    FROM #TESTS t
    LEFT JOIN #SUBGROUP s
      ON t.TESTID = s.TESTID
     AND s.COMPLETED = 'FALSE'
    LEFT JOIN #EXEC_ORDER eo
      ON t.TESTID = eo.TESTID1
    Thanks
    jlimited

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    jlimited,

    The example given and the explanation of what you wanted were not very clear to me. I also think the expected results were not correct.

    Because you were so nice to provide us with the CREATE scripts of the tables and INSERT scripts to populate them, I did try to come up a with a solution for the problem that I think you are faced with.

    I have changed the names of tables and columns to make it more clear what I think they stand for.
    Code:
    CREATE TABLE #Tests_started (
    	test_id INT NOT NULL,
    	is_completed BIT NOT NULL DEFAULT 'FALSE'
    );
    
    CREATE TABLE #Tests (
    	test_id INT NOT NULL
    );
    
    CREATE TABLE #Exec_order (
    	preceding_test_id INT NOT NULL,
    	next_test_id INT NOT NULL
    );
    
    INSERT INTO #Tests (test_id) VALUES (1), (2), (3), (4), (5), (6), (7);
    INSERT INTO #Exec_order (preceding_test_id, next_test_id) VALUES 
    (2, 3), 
    (2, 4),
    (5, 4),
    (3, 6),
    (4, 6),
    (6, 7);
    
    -- Tests #1 and #2 would be returned. 
    -- When Test #2 is is_completed, then Tests #1, #3 and #4 would be returned. 
    -- When Test #4 is is_completed, then Tests #1, #3, and #5 would be returned.
    This is what I think is the problem:
    • There are a number of tests: in the #Tests table. In the example 1 to 7 (I have added 2 more).
    • Certain tests may only be started after the completion of a number of other tests. In table #Exec_order: a given test next_test_id may only start after the completion of all the related tests preceding_test_id
    • Some tests may have been started but are not yet completed: table #Tests_started hold those. is_completed is set to TRUE when the test is completed. For the given problem, a test in #Tests_started with is_completed = 'FALSE' behaves exactly the same as a test that does not occur in #Tests_started at all.

    In the given example:
    • Test 1 can start at any time.
    • Test 2 can start at any time.
    • Test 3 can only start after test 2 is completed
    • Test 4 can only start after both test 2 and test 5 are completed
    • Test 5 can start any time.
    • Test 6 can only start after both test 3 and test 4 are completed
    • Test 7 can only start after test 6 is completed


    Expected result: give me all tests that can be started.
    What tests can be started?
    - tests that do not occur in the column #Exec_order.next_test_id; at the beginning, those are 1, 2 and 5.
    - tests that are not "locked" by the presence of at least one preceding_test_id that is not completed yet (either by having it's #Tests_started.is_completed = 'FALSE' or by not occurring in #Tests_started at all)

    By removing the comment marks "--" in the query, it also removes the tests that are completed, so you won't do a completed test again.
    Code:
    SELECT T.test_id
    FROM #Tests as T
    WHERE 
    --	T.test_id NOT IN (-- tests that were started and completed
    --		SELECT TS.test_id
    --		FROM #Tests_started as TS
    --		WHERE T.test_id = TS.test_id AND
    --				TS.is_completed = 'TRUE'
    --		) AND
    	(T.test_id IN (--tests with no requirements: 1, 2 and 5
    		SELECT No_Order.test_id
    		FROM #Tests as No_Order
    		WHERE NOT EXISTS (SELECT 1 
    			FROM #Exec_order 
    			WHERE No_Order.test_id = #Exec_order.next_test_id
    			)
    		) OR
    	T.test_id NOT IN (			
    		-- Tests still locked because not all preceding tests 
    		--  were completed
    		-- E.g. test 4 is only unlocked after completing BOTH 
    		--  tests 2 and 5
    		SELECT next_test_id 
    		FROM #Exec_order as EO
    			LEFT OUTER JOIN #Tests_started as TS ON
    				EO.preceding_test_id = TS.test_id
    		WHERE COALESCE(TS.is_completed, 'FALSE') = 'FALSE'
    		)
    	)
    ;
    Remove the comment marks "--" in the INSERT statement one by one and see how the result of the query changes.
    Code:
    INSERT INTO #Tests_started(test_id, is_completed) VALUES
    (1, 'TRUE')
    --,(2, 'TRUE')
    --,(5, 'TRUE')
    --,(3, 'TRUE')
    --,(4, 'TRUE')
    --,(6, 'TRUE')
    --,(7, 'TRUE')
    ;
    Have a nice weekend.
    Last edited by Wim; 09-03-13 at 06:11. Reason: corrected about Test 4, thank you tonkuma
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    My guess is that this will help you:

    https://www.simple-talk.com/sql/t-sq...n-constraints/

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Wim View Post
    ...
    ...

    In the given example:
    • Test 1 can start at any time.
    • Test 2 can start at any time.
    • Test 3 can only start after test 2 is completed
    • Test 4 can only start after test 2 is completed
    • Test 5 can start any time.
    • Test 6 can only start after both test 3 and test 4 are completed
    • Test 7 can only start after test 6 is completed


    Expected result: give me all tests that can be started.
    What tests can be started?
    - tests that do not occur in the column #Exec_order.next_test_id; at the beginning, those are 1, 2 and 5.
    - tests that are not "locked" by the presence of at least one preceding_test_id that is not completed yet (either by having it's #Tests_started.is_completed = 'FALSE' or by not occurring in #Tests_started at all)

    By removing the comment marks "--" in the query, it also removes the tests that are completed, so you won't do a completed test again.
    Code:
    SELECT T.test_id
    FROM #Tests as T
    WHERE 
    --	T.test_id NOT IN (-- tests that were started and completed
    --		SELECT TS.test_id
    --		FROM #Tests_started as TS
    --		WHERE T.test_id = TS.test_id AND
    --				TS.is_completed = 'TRUE'
    --		) AND
    	(T.test_id IN (--tests with no requirements: 1, 2 and 5
    		SELECT No_Order.test_id
    		FROM #Tests as No_Order
    		WHERE NOT EXISTS (SELECT 1 
    			FROM #Exec_order 
    			WHERE No_Order.test_id = #Exec_order.next_test_id
    			)
    		) OR
    	T.test_id NOT IN (			
    		-- Tests still locked because not all preceding tests 
    		--  were completed
    		-- E.g. test 4 is only unlocked after completing BOTH 
    		--  tests 2 and 5
    		SELECT next_test_id 
    		FROM #Exec_order as EO
    			LEFT OUTER JOIN #Tests_started as TS ON
    				EO.preceding_test_id = TS.test_id
    		WHERE COALESCE(TS.is_completed, 'FALSE') = 'FALSE'
    		)
    	)
    ;
    Remove the comment marks "--" in the INSERT statement one by one and see how the result of the query changes.
    Code:
    INSERT INTO #Tests_started(test_id, is_completed) VALUES
    (1, 'TRUE')
    --,(2, 'TRUE')
    --,(5, 'TRUE')
    --,(3, 'TRUE')
    --,(4, 'TRUE')
    --,(6, 'TRUE')
    --,(7, 'TRUE')
    ;
    Have a nice weekend.
    I thought the condition for Test 4 should be changed, like this...
    • Test 4 can only start after both test 2 and test 5 are completed

    This might be your careless mistake.


    I tried to shorten the query.
    The follwoing examples worked same as
    ..., it also removes the tests that are completed, so you won't do a completed test again.
    The examples were not tested on Microsoft SQL Server.
    (Tested briefly on DB2 9.7 on Windows.)

    Example 1:
    Code:
    SELECT t.test_id
     FROM  #Tests AS t
     WHERE t.test_id
           NOT IN
           (SELECT CASE ts.is_completed
                   WHEN 'TRUE' THEN ts.test_id
                   ELSE             COALESCE(eo.next_test_id , 0)
                   END
             FROM  #Tests_started AS ts
             FULL  OUTER JOIN
                   #Exec_order    AS eo
              ON   eo.preceding_test_id = ts.test_id
           )
    ;
    Note: COALESCE was used to avoid null values in the result of subquery.
    If a null value was in the result of subquery, no t.test_id would satisfy the NOT IN predicate.


    Example 2: NOT EXISTS need not consider null values in the result of the CASE expression.
    Code:
    SELECT t.test_id
     FROM  #Tests AS t
     WHERE NOT EXISTS
           (SELECT *
             FROM  #Tests_started AS ts
             FULL  OUTER JOIN
                   #Exec_order    AS eo
              ON   eo.preceding_test_id = ts.test_id
             WHERE CASE ts.is_completed
                   WHEN 'TRUE' THEN ts.test_id
                   ELSE             eo.next_test_id
                   END  =           t .test_id
           )
    ;

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by tonkuma View Post
    I thought the condition for Test 4 should be changed, like this...
    • Test 4 can only start after both test 2 and test 5 are completed

    This might be your careless mistake.
    Thank you for pointing that out. I have corrected my post.

    I knew my query could be optimized. Both your solutions (Example 1 & 2) work flawless on SQL Server 2008R2. Nice job.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •