Pivot

Mark,

My requirement were a bit off

I need Cummulative totals..

In other words I need a month total for everything till the end of july and a month total for evertthing till the end of August and evertthing till the end of September

July            August       September
501,853     586,535      595,564
 
Assuming that there is an increase every month

In reference to this question
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_27386198.html#a36936893




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

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.

Kevin CrossChief Technology OfficerCommented:
Leo, you can just add them together:

SELECT BusinessUnitName,
       isnull([3],0) as [ClickCount July],
       isnull([3],0) + isnull([2],0) as [ClickCount August],
        isnull([3],0) + isnull([2],0) + 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

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
Mark WillsTopic AdvisorCommented:
Wish I had said that :)

The only difference is that first month needs to be a year-to-date type figure  (or does it ?)

The question (apart from above) becomes is it really year to date (and therefore we need to collect more information rather than limit within the WHERE clause) ?
0
Kevin CrossChief Technology OfficerCommented:
Doesn't seem like this is a good use of PIVOT in that case. For scenarios of YTD if Mark is correct, I would use conditional aggregates instead. Therefore, as Mark said, you would bind to the full year in the WHERE then control the data in the three columns via SUM(CASE ... END) syntax.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Leo TorresSQL DeveloperAuthor Commented:

The numbers for end of july is al time before July then August is all time till ens of August same for september all time till end of september ..

This was requested by the user they want to see all time number and how they increase monthly during the quarter
0
Leo TorresSQL DeveloperAuthor Commented:
Sorry I have not had a chance to continue this but I probably can finish with what you have given guys thank you both
0
Kevin CrossChief Technology OfficerCommented:
You are welcome and good luck!

Here is an example of what I meant by conditional aggregates just in case:

SELECT B.BusinessUnitName
     , SUM(CASE WHEN datediff(MONTH, sm.EntryTime, getdate()) >= 3 THEN sm.Visits ELSE 0 END) AS [ClickCount Three Months Ago]
/* ... */
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(y,datediff(y,0,getdate()),0)
AND EntryTime <  dateadd(m,datediff(m,0,getdate()),0)
GROUP BY B.BusinessUnitName
;

Best regards and happy coding,
Kevin
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.