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
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
Last Comment
Can you show the result of Select union query segment
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 :
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:
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)
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
No problems, and thanks for the kind words... I am pretty sure we will be able to help you :)
ASKER
This was a SLAM DUNK.. Thank you it ran perfect..
ASKER
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!
Thanks again!
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 :)
And very happy to have been able to help :)
SQL
SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
I am getting Null values
I know there are values