0

I have this code:

insert into [dbo].[NGC_Agent_Intervals]
SELECT  
    AgentID, 
    date, 
    [Hour] = DATEPART(HOUR, RSRange),
    hour_quarter = CONCAT(DATEPART(MINUTE, RSRange) ,'-', DATEPART(MINUTE, RERange)),
    DepartmentID,
    [DepartmentName], 
    [AgentState], 
    [AgentStateReason], 
    ( select AgentStateReasonDescription from RealRanges) as AgentStateReasonDescription,
    [ExtensionID],
    [WorkstationID],

    Duration = sum(CASE 
      WHEN RSRange <= aStart and aend >= RERange THEN DATEDIFF(SECOND, aStart, DATEADD(MINUTE, 15, RSRange)) 
      when  RSRange <= aStart and aend < RERange THEN DATEDIFF(SECOND, aStart, aEnd) 
      WHEN RERange >= aEnd THEN DATEDIFF(SECOND, RSRange, aEnd) 
      ELSE DATEDIFF(SECOND, RSRange, RERange)
    END)
 FROM RealRanges

 WHERE DATEDIFF(SECOND, RSRange, aEnd) > 0
   AND AgentState NOT IN ('logout', 'LOGIN')


 GROUP BY duration,RealRanges.AgentID, RealRanges.date, RealRanges.hour,
  DepartmentID,[DepartmentName], [AgentState], [AgentStateReason],
  [ExtensionID],[WorkstationID], RSRange, RealRanges.RERange

I run this code after using a cte to split the original rows from a table to a few rows by hour quarters. What I want to do is to sum the duration value, if a few columns are matching in a few rows.

That's the original table values and I want to sum the duration: original table if i dont sum the duration it looks like this: before sum It should look like this after the sum:

new table

The only problem is that I want to group by only for the following columns:

AgentID Date hour Hour_Quarter AgentState AgentStatereason

and exclude the other columns so even if the extensionid is different (for example) I still get the sum from 2 columns. I tried doing that with a sub-query (in the code above) but I get an error: error

So what am I missing?

  • What code are you actually running to get that error? The code above? How many rows does this return? select AgentStateReasonDescription from RealRanges? – Mark Sinkinson Mar 20 '18 at 12:59
  • yes the one above and 2 – sql_girl Mar 20 '18 at 13:11
  • So which of the two values is the correct one for the field AgentStateReasonDescription in your resultset? Also, is RealRanges a table or a CTE? – Mark Sinkinson Mar 20 '18 at 13:14
  • If you don't care about ExtensionID then why is it in the query at all? If you do care about it and different values are returned then you need to tell SQL Server which one you want, using MIN, MAX, etc. – Aaron Bertrand Mar 20 '18 at 13:15
  • @AaronBertrand at first he said he needs it there but i convinced him to take it off. i didnt know about the max min option. thanks – sql_girl Mar 20 '18 at 13:26
4

To avoid grouping by a specific column that returns multiple values, you can either remove it from the query, or you can explicitly tell it which value you want. You can do this using aggregate or analytic functions, like:

For numeric values you can also present new values, such as:

  • SUM(Duration)
  • AVG(Duration)

But basically, if you want to show a single row collapsed from two different ExtensionID values, you need to define how to do that. In this case, is ExtensionID important? If it is, do you want to show the most recent one? The first one? The one that occurs most frequently? What about ties? There are solutions to all of these, but you have to know what you want your query to return.

You can't just leave things out of the GROUP BY like you can with MySQL, where you get - let's say - weird results.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.