SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Pivot Data Question


Pivot Data Question

Author
Message
rdsb_2170
rdsb_2170
Old Hand
Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)

Group: General Forum Members
Points: 350 Visits: 101
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
rdsb_2170
Old Hand
Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)

Group: General Forum Members
Points: 350 Visits: 101
I was able to figure this out. Way to end a Friday!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search