February 23, 2018 at 11:18 am
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
February 23, 2018 at 12:27 pm
I was able to figure this out. Way to end a Friday!!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply