Solved

# Pivot

Posted on 2011-10-10
176 Views
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)
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
) src
PIVOT
(
sum(Visits) for [AgedMonth] in ([3],[2],[1])
) Pvt
``````
0
Question by:Leo Torres

LVL 59

Accepted Solution

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)
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
) src
PIVOT
(
sum(Visits) for [AgedMonth] in ([3],[2],[1])
) Pvt
``````
0

LVL 51

Assisted Solution

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

LVL 59

Expert Comment

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

LVL 8

Author Comment

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

LVL 8

Author Closing Comment

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

LVL 59

Expert Comment

You are welcome and good luck!

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

, 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)
WHERE TARGETID IN (SELECT TargetID FROM #temp_targets T )
;

Best regards and happy coding,
Kevin
0

## Featured Post

### Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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…
how to add IIS SMTP to handle application/Scanner relays into office 365.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.