MS SQL 2008, join 3 tables, filter by columns in T1 and T2, find the average value of the smallest values ​​only from T3

Question

I found similar, but not entirely suitable scenarios here, and I could not get this to work. Forgive me if this is a duplicate question, I was definitely looking for the first one! I have three tables. The first, A, is a list of help desk tickets. Second, B, a list of time entries. Third, C, more about time records in the second. For each element in B there can be several elements, but for each element in B there is only and only one entry for C.

wh_task as A

task_id create_time source_id ======== ============ ========== 1351000 2013-01-23 12:03:23.590 8 1351001 2013-01-23 13:03:23.590 5 1351002 2013-01-23 15:03:23.590 8 

wh_time_item like B

 task_id time_item_id created_by_user_id ======== ============ ================= 1351000 2456 1234567 1351000 2457 2345786 1351000 2458 1234567 

wh_time_subitem as C

 time_item_id create_time ======== ============ 2456 2013-01-23 12:43:23.590 2457 2013-01-25 13:13:23.590 2458 2013-02-12 16:03:23.590 

High Level Objective - Define the average FIRST response time for each ticket as an engineer within a given date range.

In particular - First, find all the elements in A that were created between @StartDate and @EndDate. Then find all the elements in A, where source_id = 8 (these are the only tickets I care about). Then I need to find which element in B is the "first" entry, i.e. closest to the creation of the item in A. Table B does not have a creation date, although it is in C. Once I determined the item from B that was “first”, I need to see if the file created_by_user_id @Engineer matches. Finally, I want the average date between a.create_time and c.create_time, in minutes for all matches. Something like AVERAGE (DATEDIFF (MI, a.create_time, c.createtime) as a ResponseTime.

In the past two days, I went through a dozen iterations, here is my broken request, as it stands now. I know that this query, even if it could work, would not give me what I want, - fiddled with it to pull out additional columns for troubleshooting:

 DECLARE @StartDate datetime DECLARE @EndDate datetime DECLARE @Engineer integer SET @StartDate = '04/01/13' SET @EndDate = '04/30/13 23:59:59' SET @StartDate = DATEADD(S, (DateDiff(s, getdate(), getutcdate())), @StartDate) SET @EndDate = DATEADD(S, (DateDiff(s, getdate(), getutcdate())), @EndDate) SET @Engineer = 1234567 SELECT [task_number] ,a.[create_time] ,DateDiff(MI, a.create_time, c.create_time) as a_responsetime ,b.user_id ,c.[time_subitem_id] ,a.ticket_source_id FROM [databasename].[dbo].[wh_task] as a LEFT JOIN [databasename].[dbo].[wh_time_item] AS b ON a.task_id = b.task_id LEFT JOIN [[databasename].[dbo].[wh_time_subitem] AS c ON b.time_item_id = (SELECT c.time_item_id from [databasename].[dbo].[wh_time_subitem] WHERE c.create_time = (SELECT MIN(c.create_time) from [databasename].[dbo].[wh_time_subitem])) WHERE b.user_id = @Engineer AND a.ticket_source_id = 8 AND c.create_time between @StartDate and @EndDate ORDER BY a.ticket_source_id 

Thanks in advance for any help you can provide. I'm a SQL lover at best, so don't worry about hurting me. :-)

Answer 1

Jazz, by following the email I sent you using the following data provided to you, I get what I think is the right data point you are looking for:

 declare @StartDate datetime declare @EndDate datetime declare @Engineer int SET @StartDate = '01/01/13' SET @EndDate = '01/30/13 23:59:59' SET @StartDate = DATEADD(S, (DateDiff(s, getdate(), getutcdate())), @StartDate) SET @EndDate = DATEADD(S, (DateDiff(s, getdate(), getutcdate())), @EndDate) SET @Engineer = 1234567 select AVG(first_response_time) from ( select A.*, DATEDIFF(minute, A.create_time, ( select top 1 C.create_time from wh_time_item as B inner join wh_time_subitem as C on C.time_item_id = B.time_item_id order by C.create_time asc ) ) as first_response_time from wh_task as A inner join wh_time_item as B on B.task_id = A.task_id where A.source_id = 8 and A.create_time between @StartDate and @EndDate and B.created_by_user_id = @Engineer ) as first_response_table 

Note that the subquery against wh_time_item and wh_time_subitem assumes that create_time will always be included, or after wh_task create_time ... which seems like a hard guess.

Answer 2

Are you sure you are using this question above?

U is a familiar friend ... This error occurs when you use the aggregate function as AVG() directly in your ON clause statement. For instance:

 TableA join TableB on TableA.Code >= AVG(TableB.Code) 

This request above has the same error as yours

But now you are using a subquery, having an aggregate function in, It seems to be true. I meant this part of your request:

 LEFT JOIN [[databasename].[dbo].[wh_time_subitem] AS c ON b.time_item_id = (SELECT c.time_item_id from [databasename].[dbo].[wh_time_subitem] WHERE c.create_time = (SELECT MIN(c.create_time) from [databasename].[dbo].[wh_time_subitem])) 

Check out this link: Link may be useful