cumulative count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

cumulative count

Author

Topic

2revup
Posting Yak Master

112 Posts

Posted - 2013-11-20 : 18:52:32

So I have the following query:

Select Year(DATEADD(MONTH,3,date)) as Year, Month(DATEADD(MONTH,3,date)) as Month, location, COUNT (agent_login_id)
from Agents
where Location = 'syd'
Group by Location, Year(DATEADD(MONTH,3,date)), Month(DATEADD(MONTH,3,date))

it outputs:

Year Month location Count
2013 1 SYD 1
2013 3 SYD 11
2013 4 SYD 2
2013 5 SYD 2
2013 8 SYD 3
2013 9 SYD 1
2013 10 SYD 4
2013 11 SYD 7
2013 12 SYD 7
2014 1 SYD 3
2014 2 SYD 1

But I need to do a cumulative count so for example this is what I need it to look like

Year Month location Count Cumulative count
2013 1 SYD 1 1
2013 3 SYD 11 12
2013 4 SYD 2 14

How can I do this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-20 : 21:59:53

[code]
;with cte as
(
select [date] = dateadd(month, datediff(month, 0, [date]) + 3, 0), location, cnt = count(*)
from Agents a
where location = 'SYD'
group by dateadd(month, datediff(month, 0, [date]) + 3, 0), location
)
select *
from cte a
cross apply
(
select Cumulative = count(*)
from Agents x
where x.location = a.location
and dateadd(month, datediff(month, 0, x.[date]) + 3, 0) <= a.[date]
) c
[/code]

KH
[spoiler]Time is always against us[/spoiler]

2revup
Posting Yak Master

112 Posts

Posted - 2013-11-20 : 22:35:17

Genius. thank you!

2revup
Posting Yak Master

112 Posts

Posted - 2013-11-20 : 23:02:54

Now I have what is likely a harder barer to overcome, I need to include the above with another query. As I need the query to use the CTE output as a count.

I have 2 queries that I need to put together but need it to use the results of one of them to give me the intended output.

So in brief I am trying to find out the AVG of each month, to do this I need to divide by the users. The problem is the users varies from month to month, so I just cant take the number from today, as the result will have varied from month to month and never be constant.

The first query gives me a result of how many staff we have for that month.

;with Cumulative_cte as
(
select date = dateadd(month, datediff(month, 0, date) + 3, 0), location, cnt = count(*)
from Agents a
where location = 'SYD'
group by dateadd(month, datediff(month, 0, date) + 3, 0), location
)
select *
from Cumulative_cte a
cross apply
(
select Cumulative = count(*)
from Agents x
where x.location = a.location
and dateadd(month, datediff(month, 0, x.date) + 3, 0) <= a.date
) c

Using that I need to incorporate that into this query, which is doing a division by the engineer, so it needs to do the division by the engineer of the matching month.

here is the query that I need to get to use that CTE result.
Select
Correspondances.Year,
Correspondances.Month,
a.location,
correspondances.Corr_Count,
Phone.CallNo,
Chat.ChatNo,
Forums.ForumPosts,
sum(correspondances.Corr_Count + Phone.CallNo + Chat.ChatNo + ISNULL(Forums.ForumPosts, 0)) / count(distinct a.agent_login_id),
(sum(correspondances.Corr_Count + Phone.CallNo + Chat.ChatNo + ISNULL(Forums.ForumPosts, 0)) / count(distinct a.agent_login_id)) / count(distinct a.agent_login_id) as AVGPerEngineer ,
count(distinct a.agent_login_id) as AgentsOnSite
from
(
Select Year(comm_date_utc) as Year, Month(comm_date_utc) as Month, location, Count(comm_id) as Corr_Count
from
Correspondences
left join Agents on owner_agent_login = agent_login_id
where location in ('SYD')
and comm_date_utc between '2013/01/01 00:00:00' and '2013/10/31 23:59:00'
and comm_date_utc between DATEADD(day,90,Date) and '2013/10/31 23:59:00'
group by Year(comm_date_utc) , Month(comm_date_utc), location
) Correspondances
Left outer join
(
select Year(STATISTIC_DATE) as Year, Month(STATISTIC_DATE) as Month, location, sum(tot_calls_handled) as CallNo
from
PhoneandChat
left join Agents on agent_login = agent_login_id
where location in ('SYD')
and STATISTIC_DATE between '2013/01/01 00:00:00' and '2013/10/31 23:59:00'
and STATISTIC_DATE between DATEADD(day,90,Date) and '2013/10/31 23:59:00'
and TOT_CALLS_HANDLED >= 0
and phone_skill not LIKE '%Chat%'
group by Year(STATISTIC_DATE),Month(STATISTIC_DATE), location
) Phone
on Correspondances.location = Phone.Location
and Correspondances.Year = Phone.Year
and Correspondances.Month = Phone.Month
Left outer join
(
select Year(STATISTIC_DATE) as Year, Month(STATISTIC_DATE) as Month, location, sum(tot_calls_handled) as chatNo
from
PhoneandChat
left join Agents on agent_login = agent_login_id
where location in ('SYD')
and STATISTIC_DATE between '2013/01/01 00:00:00' and '2013/10/31 23:59:00'
and STATISTIC_DATE between DATEADD(day,90,Date) and '2013/10/31 23:59:00'
and TOT_CALLS_HANDLED >= 0

group by Year(STATISTIC_DATE),Month(STATISTIC_DATE), location

) Chat
on Correspondances.Location = chat.Location
and Correspondances.Year = chat.Year
and Correspondances.Month = chat.Month
Left outer join
(
Select Year(Posted_Date_utc) as Year, Month(Posted_Date_utc) as Month, location, ISNULL(count(login), 0) as ForumPosts
from Forums
left join Agents on login = agent_login_id
where location in ('SYD')
and Posted_Date_utc between '2013/01/01 00:00:00' and '2013/10/31 23:59:00'
and Posted_Date_utc between DATEADD(day,90,Date) and '2013/10/31 23:59:00'

group by Year(Posted_Date_utc),Month(Posted_Date_utc), location
) Forums
on Correspondances.Location = Forums.Location
and Correspondances.Year = forums.Year
and Correspondances.Month = Forums.Month
join (select distinct location, agent_login_id, title from Agents) a on Correspondances.Location = a.Location
group by Correspondances.Year, Correspondances.Month,a.Location, correspondances.Corr_Count, phone.callNo,Chat.chatNo, Forums.ForumPosts
order by YEAR, MONTH, location

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources