[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Darn Pivot can never figure them out

Posted on 2011-10-07
9
Medium Priority
?
183 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Leo Torres
  • 4
  • 4
9 Comments
 
LVL 8

Author Comment

by:Leo Torres
ID: 36933791

I am getting Null values

I know there are values
0
 
LVL 5

Expert Comment

by:AlokJain0412
ID: 36935707
Can you show the result of  Select union query segment
 
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 36936789
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 36936893
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
 
LVL 8

Author Comment

by:Leo Torres
ID: 36936935

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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 36936980
No problems, and thanks for the kind words... I am pretty sure we will be able to help you :)
0
 
LVL 8

Author Closing Comment

by:Leo Torres
ID: 36942652
This was a SLAM DUNK.. Thank you it ran perfect..
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 36942691
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 36944814
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question