Aggregate 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)

Aggregate Count

Author

Topic

sql_chaser
Starting Member

33 Posts

Posted - 2015-01-05 : 11:38:19

Finding hard to on adding the Month To Date Count along with the daily count in the below mentioned query. Please help !!!

SELECT

ProcessDtInt
,ProcessDt
,'New Employees' AS Report
,Product
,ProductType
,ProductCtgry
,SUM(Cost) AS Cost
,SUM(Revenue) AS Revenue
,COUNT(1) AS DailyCount
FROM
EmployeeProduct
WHERE
StartDt > ProcessDt
GROUP
BY
ProcessDtInt
,ProcessDt
,Product
,ProductType
,ProductCtgry

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-05 : 12:11:35

Do you mean count by each month? or one month at a time?
if you want to calc on every month you want a case select.

SELECT

ProcessDtInt
,ProcessDt
,'New Employees' AS Report
,Product
,ProductType
,ProductCtgry
,SUM(Cost) AS Cost
,SUM(Revenue) AS Revenue
,COUNT(1) AS DailyCount
FROM
EmployeeProduct
WHERE
StartDt > ProcessDt
And Month(StartDt) = 1 -- January
GROUP
BY
ProcessDtInt
,ProcessDt
,Product
,ProductType
,ProductCtgry

We are the creators of our own reality!

sql_chaser
Starting Member

33 Posts

Posted - 2015-01-05 : 12:30:28

It will be for each month !!!

sql_chaser
Starting Member

33 Posts

Posted - 2015-01-05 : 13:01:16

One more problem is to identify the % of Cancelled Employees where the daily Count of NewEmployees/Count of Cancelled Employees...Please help to use it in the same query with Month-To-Date (which is each month based on the StartDt)

sql_chaser
Starting Member

33 Posts

Posted - 2015-01-05 : 19:40:57

I tried the case statement but not able to include the % of Cancelled is tracking the cancellation for the New Employees day over day...

SELECT
ProcessDtInt
,ProcessDt
,'New Employees' AS Report
,Product
,ProductType
,ProductCtgry
,SUM(Cost) AS Cost
,SUM(Revenue) AS Revenue
,COUNT(1) AS DailyCount
,SUM(CASE WHEN MONTH(StartDt)=MONTH(AsOfDt) THEN 1 ELSE 0 END) AS MTD
FROM
EmployeeProduct
WHERE
StartDt > ProcessDt
GROUP
BY
ProcessDtInt
,ProcessDt
,Product
,ProductType

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-06 : 05:53:06

We really need some sample data to play with but off top of my head something like this, you will have a new column with the month name in it.

With bymonth
As
(
SELECT
[ProcessDtInt]
,[ProcessDt]
,'New Employees' AS Report
,[Product]
,[ProductType]
,[ProductCtgry]
,SUM([Cost]) AS Cost
,SUM([Revenue]) AS Revenue
,COUNT(1) AS DailyCount
FROM [EmployeeProduct]
WHERE [StartDt] > [ProcessDt]

GROUP BY
[ProcessDtInt]
,[ProcessDt]
,[Product]
,[ProductType]
,[ProductCtgry]
)
Select [ProcessDtInt]
,[ProcessDt]
,[Report]
,[Product]
,[ProductType]
,[ProductCtgry]
,[Cost]
,[Revenue]
,[MonthName] = Case
When Month([StartDt]) = 1
Then 'January'
When Month([StartDt]) = 2
Then 'February'
When Month([StartDt]) = 3
Then 'March'
When Month([StartDt]) = 4
Then 'April'
When Month([StartDt]) = 5
Then 'May'
When Month([StartDt]) = 6
Then 'June'
When Month([StartDt]) = 7
Then 'July'
When Month([StartDt]) = 8
Then 'August'
When Month([StartDt]) = 9
Then 'September'
When Month([StartDt]) = 10
Then 'October'
When Month([StartDt]) = 11
Then 'November'
When Month([StartDt]) = 12
Then 'December'
End
From bymonth

We are the creators of our own reality!

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-06 : 12:44:40

I'd put the initial results into a (keyed) temp table, then compute the month-to-date totals using that table, since that's the easiest way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources