Pivot Data Question

• rdsb_2170

  SSC Eights!

  Points: 817

  More actions

  February 23, 2018 at 11:18 am

  #336334

  Hi All,

  I have a requirement to show my pivot results for each day of the month, but use a [LastCallDate] to determine which row the data appears on for [NumberOfAttemtps].
  I'm using a DimDate table to return a list of the Calendar Month/Day(s). I wanted to use the Calendar Month/day as my Y (vertical axis) and use number of [NumberOfAttemtps] as my
  X (horizontal axis). The Y axis would need to look up the value for the X by using the [LastCalldate] = [CalendarDates] showing on the Y axis. If there is no data for a specific date it would show zero(s) on that row/cell.

  I'm able to PIVOT using [LastCallDate] for Y axis, but I'm stumped on how to do what I described above.
  Here is a sample of my code. Any suggestions would be greatly appreciated!!

  DECLARE @StartDateTime DateTime;
  DECLARE @EndDateTime DateTime;

  SET @StartDateTime = '2018-01-01 00:00:00'
  SET @EndDateTime = '2018-01-31 23:59:59'

  Select DISTINCT(Concat([Month], '-', DayOfMonth)) as CalMonthDay, Date
  INTO #CalMonthDay
  From [dbo].[DimDate]
  Where Date Between @StartDateTime and DateAdd(month, 1 ,@EndDateTime)
  Order By Date DESC

  SELECT OPCalls.[I3_Identity]
    ,[CampaignName]
    ,OpCalls.[VoiceLeadID] as VoiceLeadID
    ,[previewpoptimeUTC]
    ,[callplacedtimeUTC]
    ,[ATTEMPTS]
    ,[I3_LASTCALLED_UTC] as LastCallDate
       ,Concat(Month([I3_LASTCALLED_UTC]), '-' ,(Day([I3_LASTCALLED_UTC]))) as LastCallMonthDay
       ,Day([I3_LASTCALLED_UTC]) as LastCallDay
       ,Month([I3_LASTCALLED_UTC])as LastCallMonth
    ,Contact.[VoiceLeadID] as LeadID
    ,[LoadedDateTime] as LoadDate
  INTO #Temp
  FROM [I3_CMS].[CMS].[vwCMSOwnerProgramsCalls] OPCalls
  LEFT JOIN [I3_CMS].[dbo].[OwnerContactList] Contact on OPCalls.VoiceLeadID = Contact.VoiceLeadID
  WHERE Contact.[LoadedDateTime] between @StartDateTime and @EndDateTime
  and [callplacedtimeUTC] >= Contact.[LoadedDateTime]

  Select CalMonthDay,
  CAST(LoadDate AS varchar(max)) LoadDateMonthDay,
  cast(LastCallMonthDay as nvarchar(max)) LastCallMonthDay,
  LastCallDay,
  LastCallMonth,
  VoiceLeadID,
  CAST(Count(VoiceLeadID) as Int) as NumberofAttempts
  INTO #TEMP1
  From #Temp
  LEFT JOIN #CalMonthDay on CalMonthDay = LastCallMonthDay
  Group By
  CalMonthDay,
  CAST(LoadDate AS varchar(max)),
  LastCallMonthDay,
  LastCallDay,
  LastCallMonth,
  VoiceLeadID
  Order By LastCallMonth,LastCallDay, NumberofAttempts

  --- PIVOT Results

  DECLARE @SColumns as NVARCHAR(MAX)
  DECLARE @Columns as NVARCHAR(MAX)

  SELECT @SColumns = ISNULL(@SColumns + ',','')+ 'ISNULL(' + QUOTENAME(NumberOfAttempts) + ', 0) AS '+ QUOTENAME(NumberOfAttempts)
  FROM (
      SELECT DISTINCT NumberOfAttempts FROM #TEMP1
  ) AS x
  ORDER BY NumberOfAttempts;

  SELECT @Columns =
  COALESCE(@Columns + ', ','') + QUOTENAME(NumberOfAttempts)
  FROM (SELECT DISTINCT NumberOfAttempts FROM #Temp1) AS B
  ORDER BY NumberOfAttempts

  DECLARE @SQL as NVARCHAR(MAX)
  SET @SQL = 'SELECT LastCallMonthDay, LastCallMonth, LastCallDay, ' + @SColumns + '

  FROM
  (
  Select LastCallMonthDay, LastCallMonth, LastCallDay, NumberOfAttempts, Cast(Count(DISTINCT(VoiceLeadID))as numeric(10,0)) as Count1
  From #Temp1
  Where LastcallDay IS NOT NULL and LastCallMonth IS NOT NULL
  Group By LastCallMonthDay, LastCallMonth, LastCallDay, NumberOfAttempts) AS t1
  PIVOT
  (MAX(Count1) FOR NumberOfAttempts IN (' + @Columns + ')) AS PivotResult
  ORDER BY LastCallMonth, LastCallDay'

  EXEC(@SQL)

  Regards,
  Cervello

• rdsb_2170

  SSC Eights!

  Points: 817

  More actions

  February 23, 2018 at 12:27 pm

  #1980992

  I was able to figure this out. Way to end a Friday!!