dcsimg
Results 1 to 3 of 3

Thread: Sql query question 4 experts

  1. #1
    Join Date
    Aug 2011
    Posts
    1

    Question Sql query question 4 experts

    I have a table that contains my Service Orders and for the service orders there could be many service troubles. I need to a query that will get the results as folows: Joe went to fix a problem and opened service order number 1. The issue was not resolved, customer calls back. Now Bob goes to fix the issue that Joe didn't resolve. Out of this Joe is docked a repeat service. Joe has 1 repeat. The next day customer calls back: issue is not resolved and Joe goes out to fix the problem. Now Bob is docked a repeat. Bob has 1 repeat. The issue was not resolved and Joe is sent to fix the issue again. Joe is docked another repeat. Now joe has 2 repeats. Issue still not resolves and Bob goes out to fix the problem. Joe is docked another repeat. Now Joe has 3 repeats. Issue still not resolved and Bob is sent out again to fix problem. Bob is docked a repeat. Now Bob has 2 repeats and Joe has 3 repeats for the same service order.

    The serv_trouble is numeric. The serv_order has a start date and a closed date in another table. Unfortunately the Serv_trouble does not have a date assigned.

    I need ideas for how to write this type of query. Below is a sample of the table:

    Serv_Trouble TechID Tech ServOrder
    1 1 joe 1
    2 2 bob 1
    3 1 joe 1
    4 1 joe 1
    5 2 bob 1
    6 2 bob 1
    7 1 joe 1
    1 1 joe 2
    2 1 joe 2
    3 2 bob 2
    4 2 bob 2
    5 3 larry 2
    7 2 bob 2

    Any help will be very, and I mean very appreciated. I have searched most SQL sites for ideas but non seem to solve something like this.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,912
    What do you expect in the result based on the table data?

  3. #3
    Join Date
    Sep 2005
    Posts
    168
    Code:
    declare @orders table	(
    			id smallint identity(1,1), 
    			ServOrder smallint, 
    			ServTrouble smallint, 
    			TechID smallint, 
    			TechName varchar(10) collate database_default,
    			primary key clustered(ServOrder, ServTrouble, Techid)
    			);
    						
    insert into @orders (ServOrder, ServTrouble, TechID, TechName)
    values (1, 1, 1, 'joe'), (1, 2, 2, 'bob'), (1, 3, 1, 'joe'), (1, 4, 1, 'joe'), (1, 5, 2, 'bob'), (1, 6, 2, 'bob'), (1, 7, 1, 'joe'),
    (2, 1, 1, 'joe'), (2, 2, 1, 'joe'), (2, 3, 2, 'bob'), (2, 4, 2, 'bob'), (2, 5, 3, 'larry'), (2, 6, 3, 'larry'), (2, 7, 2, 'bob');
    
    
    select * from @orders order by ServOrder, ServTrouble;
    
    select ServOrder, TechID, TechName, count(distinct uplimit) as repeats
    from
    (
    	select a.*, 
    		(	
    		select min(ServTrouble) 
    		from @orders as b 
    		WHERE b.ServOrder = a.servorder 
    		and (
    			( b.ServTrouble >= a.servtrouble 
    			  and  exists (select * from @orders as c where c.ServOrder = b.servorder and c.ServTrouble = b.ServTrouble + 1 and c.TechID <> b.TechID)
    			)
    			or
    			( b.ServTrouble = (select max(ServTrouble) from @orders as d where d.ServOrder = b.ServOrder)
    			  and exists(select * from @orders as e where e.ServOrder = b.ServOrder and e.ServTrouble < b.ServTrouble and e.TechID <> b.TechID)
    			)
    	 	   )
    		) AS uplimit
    	from @orders as a
    ) AS o
    group by ServOrder, TechID, TechName
    order by ServOrder, repeats, TechName;
    --HTH--

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
  •