• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

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

0
Leo Torres
Asked:
Leo Torres
  • 3
  • 2
2 Solutions
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now