Darn Pivot can never figure them out

I would like to create this pivot

below is the code I have and the excel sheet has what I want the file result to be
SELECT BusinessUnitName,
[ClickCount July],
[ClickCount August],
[ClickCount September]
FROM(
SELECT B.BusinessUnitName , SUM(Visits) AS Visits, 'ClicksCount '+DATENAME(MONTH, DATEADD(month, -1, GETDATE())) AS [Month]
FROM bridgetrack.dbo.VIEW_Summary_Month SM WITH (NOLOCK)
JOIN bridgetrack.dbo.BUSINESSUNITS B WITH (NOLOCK) ON SM.BusinessUnitID = B.BusinessUnitID
WHERE TARGETID IN (SELECT TargetID FROM #temp_targets T ) 
AND YEAR(EntryTime) = YEAR(GETDATE())
AND MONTH(EntryTime) = MONTH(DATEADD(month, -1, GETDATE()))
GROUP BY B.BusinessUnitName 
UNION
SELECT B.BusinessUnitName , SUM(Visits) AS Visits,'ClicksCount '+ DATENAME(MONTH, DATEADD(month, -2, GETDATE())) AS [Month]
FROM bridgetrack.dbo.VIEW_Summary_Month SM WITH (NOLOCK)
JOIN bridgetrack.dbo.BUSINESSUNITS B WITH (NOLOCK) ON SM.BusinessUnitID = B.BusinessUnitID
WHERE TARGETID IN (SELECT TargetID FROM #temp_targets T ) 
AND YEAR(EntryTime) = YEAR(GETDATE())
AND MONTH(EntryTime) = MONTH(DATEADD(month, -2, GETDATE()))
GROUP BY B.BusinessUnitName 
UNION
SELECT B.BusinessUnitName , SUM(Visits) AS Visits,'ClicksCount '+ DATENAME(MONTH, DATEADD(month, -3, GETDATE())) AS [Month]
FROM bridgetrack.dbo.VIEW_Summary_Month SM WITH (NOLOCK)
JOIN bridgetrack.dbo.BUSINESSUNITS B WITH (NOLOCK) ON SM.BusinessUnitID = B.BusinessUnitID
WHERE TARGETID IN (SELECT TargetID FROM #temp_targets T ) 
AND YEAR(EntryTime) = YEAR(GETDATE())
AND MONTH(EntryTime) = MONTH(DATEADD(month, -3, GETDATE())) 
GROUP BY B.BusinessUnitName 
) p
pivot (MAX(p.Visits) for p.[Month] in ([ClickCount July],[ClickCount August],
[ClickCount September])) uPvt
ORDER BY BusinessUnitName

Open in new window

sample-Pivot.xlsx
LVL 8
Leo TorresSQL DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leo TorresSQL DeveloperAuthor Commented:

I am getting Null values

I know there are values
0
AlokJain0412Commented:
Can you show the result of  Select union query segment
 
0
Mark WillsTopic AdvisorCommented:
So, for every business unit there should be values in each of the three months - right ?

In which case we need to check out just the select part of the problem...

Let's try with a more simplified approach first :

SELECT B.BusinessUnitName , SUM(Visits) AS Visits, DATENAME(MONTH, EntryTime) AS [Month]
FROM bridgetrack.dbo.VIEW_Summary_Month SM WITH (NOLOCK)
JOIN bridgetrack.dbo.BUSINESSUNITS B WITH (NOLOCK) ON SM.BusinessUnitID = B.BusinessUnitID
WHERE TARGETID IN (SELECT TargetID FROM #temp_targets T ) 
AND YEAR(EntryTime) = YEAR(GETDATE())
AND MONTH(EntryTime) between MONTH(DATEADD(month, -3, GETDATE())) and MONTH(DATEADD(month, -1, GETDATE()))
GROUP BY B.BusinessUnitName, DATENAME(MONTH, EntryTime) 
ORDER BY B.BusinessUnitName, DATENAME(MONTH, EntryTime) 

Open in new window


So, using the above, lets first check to see that the "raw" data is coming out correctly.

Once we are happy with the raw data then we can progress to the pivot - which looks OK on first glance, so guessing there is something a bit more fundamental in how we are getting the data.

and of course that query will break in January, February, March because of the change in year, but we will get to that next. Really, we want the trailing months, so we can code for that using a more generic formula in the WHERE clause:

select dateadd(m,datediff(m,0,getdate())-3,0) as start_period,
        dateadd(ms,-3,dateadd(m,datediff(m,0,getdate()),0)) as end_period

Open in new window


0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Mark WillsTopic AdvisorCommented:
Now, the nice thing about pivot is you really shouldnt have to aggregate first. Thats what the pivot will want to do.

So, lets look at simplifying our pivot even further :

SELECT BusinessUnitName,
       isnull([3],0) as [ClickCount July],
       isnull([2],0) as [ClickCount August],
       isnull([1],0) as [ClickCount September]
FROM (
       SELECT B.BusinessUnitName , sm.Visits, datediff(MONTH, sm.EntryTime, getdate()) AS [AgedMonth]
       FROM bridgetrack.dbo.VIEW_Summary_Month SM WITH (NOLOCK)                                         
       JOIN bridgetrack.dbo.BUSINESSUNITS B WITH (NOLOCK) ON SM.BusinessUnitID = B.BusinessUnitID
       WHERE TARGETID IN (SELECT TargetID FROM #temp_targets T ) 
       AND EntryTime >= dateadd(m,datediff(m,0,getdate())-3,0)            -- could use between, but sometimes easier to manage this way
       AND EntryTime <  dateadd(m,datediff(m,0,getdate()),0)
     ) src
PIVOT 
     (
       sum(Visits) for [AgedMonth] in ([3],[2],[1])
     ) Pvt
ORDER BY BusinessUnitName 

Open in new window


If we wanted to make it even more generic, then we need to go to a dynamic SQL procedure, but in the above, we only have to change the initial select and the rest should very simply flow...

You can gain more insights into dynamic pivot reading my article : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leo TorresSQL DeveloperAuthor Commented:

Mark, you always provide excellant solutions.. Thank for your expert advise on these pivots..

I am away from my work computer but I will try this as soon as get an opportunity so probably later tonight or something.. Thank you
0
Mark WillsTopic AdvisorCommented:
No problems, and thanks for the kind words... I am pretty sure we will be able to help you :)
0
Leo TorresSQL DeveloperAuthor Commented:
This was a SLAM DUNK.. Thank you it ran perfect..
0
Leo TorresSQL DeveloperAuthor Commented:
FYI yes I have that article bookmarked before I have seen it and read it already great Article.. Its just a hard follow for me.. Dont know why been doing sql for about 3 years now I feel by now it should sink in.. No worries I will keep it book marked and keeping reading it till i do....:)

Thanks again!
0
Mark WillsTopic AdvisorCommented:
Well, let me know what areas are confusing and I can see what I can do to either explain or simplify...

And very happy to have been able to help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.