locked
Combining two queries into one? RRS feed

  • Question

  • I currently have 2 queries like so:

    SELECT time, [Index Servers Queried] as indexServerA from myTable where date = @date and [Index Servers Queried] < [Total Index Servers]

    AND

    SELECT time, [Index Servers Queried] as indexServerB from myTable where date = @date and [Index Servers Queried] = [Total Index Servers]

    This seems super inefficient. for simplicity 'time' is just an nvarchar and 'index servers queried' and 'total index servers' are just ints. the reside in the same table.

    I would really like one query to do this. The problems I'm facing is that 'time' can and most likely will be unique to each query.

    so really I need to have the time for the first query and the time for the second query.

    So the output would look something like:

    timeA  | IndexServerA | timeB | IndexServerB |

    ideas?

    Wednesday, February 20, 2013 10:27 PM

Answers

  • Not sure whether you are looking for something like this or not -

    Example -

    ;WITH cte AS 
    (
    SELECT 
    	 CASE WHEN ReorderPoint<500  THEN ProductID END AS PA
    	,CASE WHEN ReorderPoint<500  THEN ReorderPoint END AS A
    	,CASE WHEN ReorderPoint>500  THEN ProductID END AS PB 
    	,CASE WHEN ReorderPoint>500  THEN ReorderPoint END AS B  
    	,ROW_NUMBER() OVER
    		(PARTITION BY CASE WHEN ReorderPoint<500  THEN ProductID ELSE 0 END
    			ORDER BY CASE WHEN ReorderPoint<500  THEN ReorderPoint END) AS RN1
    	,ROW_NUMBER() OVER
    		(PARTITION BY CASE WHEN ReorderPoint>500  THEN ProductID ELSE 0 END
    			ORDER BY CASE WHEN ReorderPoint>500  THEN ReorderPoint END) AS RN2
     FROM [AdventureWorks].[Production].[Product]
     WHERE SellStartDate ='2002-06-01'  
    ),cte2 AS 
    (
    SELECT *
    FROM cte AS a
    WHERE a.RN1=1
    	AND a.A IS NOT NULL 
    ),cte3 AS 
    (
    SELECT *
    FROM cte AS a
    WHERE a.RN2=1
    	AND a.B IS NOT NULL 
    )
    SELECT cte2.PA,cte2.A,cte3.PB,cte3.B
    FROM cte3
    	LEFT JOIN cte2
    		ON cte3.RN2=cte3.RN1


    Narsimha

    • Marked as answer by Iric Wen Thursday, February 28, 2013 8:51 AM
    Wednesday, February 20, 2013 11:42 PM

All replies

  • Just wondering ..could you try this

    SELECT time, [Index Servers Queried] as indexServer from myTable where date = @date and [Index Servers Queried] <= [Total Index Servers]


    Hope it Helps!!


    • Edited by Stan210 Wednesday, February 20, 2013 11:03 PM
    Wednesday, February 20, 2013 11:03 PM
  • Not sure whether you are looking for something like this or not -

    Example -

    ;WITH cte AS 
    (
    SELECT 
    	 CASE WHEN ReorderPoint<500  THEN ProductID END AS PA
    	,CASE WHEN ReorderPoint<500  THEN ReorderPoint END AS A
    	,CASE WHEN ReorderPoint>500  THEN ProductID END AS PB 
    	,CASE WHEN ReorderPoint>500  THEN ReorderPoint END AS B  
    	,ROW_NUMBER() OVER
    		(PARTITION BY CASE WHEN ReorderPoint<500  THEN ProductID ELSE 0 END
    			ORDER BY CASE WHEN ReorderPoint<500  THEN ReorderPoint END) AS RN1
    	,ROW_NUMBER() OVER
    		(PARTITION BY CASE WHEN ReorderPoint>500  THEN ProductID ELSE 0 END
    			ORDER BY CASE WHEN ReorderPoint>500  THEN ReorderPoint END) AS RN2
     FROM [AdventureWorks].[Production].[Product]
     WHERE SellStartDate ='2002-06-01'  
    ),cte2 AS 
    (
    SELECT *
    FROM cte AS a
    WHERE a.RN1=1
    	AND a.A IS NOT NULL 
    ),cte3 AS 
    (
    SELECT *
    FROM cte AS a
    WHERE a.RN2=1
    	AND a.B IS NOT NULL 
    )
    SELECT cte2.PA,cte2.A,cte3.PB,cte3.B
    FROM cte3
    	LEFT JOIN cte2
    		ON cte3.RN2=cte3.RN1


    Narsimha

    • Marked as answer by Iric Wen Thursday, February 28, 2013 8:51 AM
    Wednesday, February 20, 2013 11:42 PM
  • I tried answering and got an error couple of times. Trying now (have to repeat the answer as I didn't save it):

    Try

    SELECT 
    case when [Index Servers Queried] < [Total Index Servers]
    then time end as TimeA, 
    
    case when [Index Servers Queried] < [Total Index Servers] then [Index Servers Queried] end 
     as indexServerA, 
    case when [Index Servers Queried] = [Total Index Servers]
    then time end as TimeB, 
    
    case when [Index Servers Queried] = [Total Index Servers] then [Index Servers Queried] end 
     as indexServerB 
    
    
    from myTable where date = @date and [Index Servers Queried] <= [Total Index Servers]
    
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, February 21, 2013 3:53 AM