Query help maybe pivot table not sure

• kss113

  SSChasing Mays

  Points: 654

  More actions

  April 21, 2017 at 11:53 am

  #322981

  Hello all,
  I am not sure how to get the info I need.  Currently the info get imported to an excel spreadsheet, from there people create a pivot table to show the info needed (Extension, name, day of week, and a count of calls) where calls have a result of "connected"

  Excel Screen shot example: here we have the name of the person, and how many calls they made per day and the grand total

  

  Now the call can get transferred, missed etc.. so a call belongs to a GroupingID to show that the call was transferred etc.., but I am only worried about the call that was answered, and this will have a Result Status of "Connected".
  Here is a screen shot of my table:
  
  I know in my sample above non of the "Result" have a "Result" status of "Connected".
  But say GroupingID of 12 which bounced 10 times had a Result Value of "Connected" then I would want to count this call to this particular "Name" for that day of the week.

  So in a pivot table I would want the "Name" on the left as rows, and the "DateTimeStamp" as the top Columns, but by day of the week. (Monday, Tuesday, Wednesday etc..)
  So my final result would be like the excel spreadsheet, showing how many calls a person answered for each day of the week, plus a grand total.

  I hope this makes sense and is detailed enough.
  Thanks for any help or assistance.
  Keith.

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  April 21, 2017 at 12:43 pm

  #1939371

  This is best done using a reporting tool like SSRS (which can even export directly to Excel).

  If that doesn't work, then you will need to use a CROSSTAB or PIVOT.  If you want more help than that, you'll need to provide CONSUMMABLE sample data as outlined in the first link in my signature.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• R. Brush

  SSCommitted

  Points: 1669

  More actions

  April 23, 2017 at 11:02 pm

  #1939482

  Just to demonstrate a couple of possibilities.  The first example doesn't show the actual dates in the column headers, only the day of week, but is very simple.  The second has the actual dates in the column headings, but is more complex. To test the examples, a table was created named [calls], with columns: Extension, Name, DateTimeStamp, and Results.

  DECLARE @WeekStartDate as smalldatetime
  , @WeekEndDate as smalldatetime

  SET @WeekStartDate = '4/16/2017'
SET @WeekEndDate = '4/22/2017'

  SELECT Extension + ' - ' + Name as ExtName
    , SUM(case when dWeekday = 2 then 1 else 0 end) as 'Mon '
    , SUM(case when dWeekday = 3 then 1 else 0 end) as 'Tue '
    , SUM(case when dWeekday = 4 then 1 else 0 end) as 'Wed '
    , SUM(case when dWeekday = 5 then 1 else 0 end) as 'Thr '
    , SUM(case when dWeekday = 6 then 1 else 0 end) as 'Fri '
    , SUM(case when dWeekday = 7 then 1 else 0 end) as 'Sat '
    , SUM(case when dWeekday = 1 then 1 else 0 end) as 'Sun '

  FROM calls
CROSS APPLY(SELECT datepart(weekday, DateTimeStamp) AS dWeekday) _
GROUP BY Extension + ' - ' + Name
HAVING COUNT(*) > 0
ORDER BY Extension + ' - ' + Name

  Returns:
  ExtName    Mon     Tue     Wed     Thr     Fri     Sat     Sun
  101 - John Doe    2    0    0    1    1    0    0
  102 - Sam Smith    1    0    0    1    0    0    0

  DECLARE @WeekStartDate as smalldatetime
  , @WeekEndDate as smalldatetime
  , @SQL as nvarchar(4000)

  SET @WeekStartDate = '4/16/2017'
SET @WeekEndDate = '4/22/2017'

  SET @SQL = '
SELECT Extension + '' - '' + Name as ExtName
  , SUM(case when dWeekday = 2 then 1 else 0 end) as ''Mon dateadd(d,0, @WeekStartDate)''
    , SUM(case when dWeekday = 3 then 1 else 0 end) as ''Tue dateadd(d,1, @WeekStartDate)''
    , SUM(case when dWeekday = 4 then 1 else 0 end) as ''Wed dateadd(d,2, @WeekStartDate)''
    , SUM(case when dWeekday = 5 then 1 else 0 end) as ''Thr dateadd(d,3, @WeekStartDate)''
    , SUM(case when dWeekday = 6 then 1 else 0 end) as ''Fri dateadd(d,4, @WeekStartDate)''
    , SUM(case when dWeekday = 7 then 1 else 0 end) as ''Sat dateadd(d,5, @WeekStartDate)''
    , SUM(case when dWeekday = 1 then 1 else 0 end) as ''Sun dateadd(d,6, @WeekStartDate)''
FROM calls
CROSS APPLY(SELECT datepart(weekday, DateTimeStamp) AS dWeekday) _
GROUP BY Extension + '' - '' + Name
HAVING COUNT(*) > 0
ORDER BY Extension + '' - '' + Name
'

  SET @SQL = replace(@SQL,'dateadd(d,0, @WeekStartDate)', Convert(varchar(20),Convert(date, dateadd(d,0, @WeekStartDate))) )
SET @SQL = replace(@SQL,'dateadd(d,1, @WeekStartDate)', Convert(varchar(20),Convert(date, dateadd(d,1, @WeekStartDate))) )
SET @SQL = replace(@SQL,'dateadd(d,2, @WeekStartDate)', Convert(varchar(20),Convert(date, dateadd(d,2, @WeekStartDate))) )
SET @SQL = replace(@SQL,'dateadd(d,3, @WeekStartDate)', Convert(varchar(20),Convert(date, dateadd(d,3, @WeekStartDate))) )
SET @SQL = replace(@SQL,'dateadd(d,4, @WeekStartDate)', Convert(varchar(20),Convert(date, dateadd(d,4, @WeekStartDate))) )
SET @SQL = replace(@SQL,'dateadd(d,5, @WeekStartDate)', Convert(varchar(20),Convert(date, dateadd(d,5, @WeekStartDate))) )
SET @SQL = replace(@SQL,'dateadd(d,6, @WeekStartDate)', Convert(varchar(20),Convert(date, dateadd(d,6, @WeekStartDate))) )
SET @SQL = replace(@SQL,'@WeekStartDate', Convert(varchar(20),Convert(date, @WeekStartDate)) )
SET @SQL = replace(@SQL,'@WeekEndDate', Convert(varchar(20),Convert(date, @WeekEndDate)) )

  exec(@SQL)

  Returns:
  ExtName    Mon 2017-04-16    Tue 2017-04-17    Wed 2017-04-18    Thr 2017-04-19    Fri 2017-04-20    Sat 2017-04-21    Sun 2017-04-22
  101 - John Doe    2    0    0    1    1    0    0
  102 - Sam Smith    1    0    0    1    0    0    0