Sum by Week, Month, Quarter, Year

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

General SQL Server Forums

New to SQL Server Programming

Sum by Week, Month, Quarter, Year

Author

Topic

caoneill
Starting Member

10 Posts

Posted - 2014-08-20 : 10:55:08

Hi all,

I am trying to group counts by week,month,quarter, year for a particular activity type and I'm having issues.

Here's my code so far:

SELECT
distinct
EmailAddressID,
emailaddress,
SUM(CASE WHEN [ActivityDate] >= DATEADD(WEEK, DATEDIFF(WEEK, 0, @DT), 0)
THEN
SUM(CASE WHEN EmailActivityType = 'OPEN' THEN 1 ELSE 0 END) END AS WeekTotalOpens
FROM EmailActivity
WHERE DATEPART(YEAR, [ActivityDate]) = DATEPART(YEAR, @DT)
GROUP BY EmailAddressID,emailaddress

Desired Output:

EmailAddressId EmailAddress WeekTotalOpens MonthTotalOpens etc. then WeekTotalClicks and so on....

SQL doesn't seem to like the sub-aggregate. What is the best way to approach?
Thanks so much!

caoneill
Starting Member

10 Posts

Posted - 2014-08-20 : 11:23:38

I figured it out..

SELECT
EmailAddressID,
emailaddress,
SUM(CASE WHEN [ActivityDate] >= DATEADD(WEEK, DATEDIFF(WEEK, 0, @DT), 0)
THEN CASE WHEN EmailActivityType = 'OPEN' THEN 1 ELSE 0 END END) AS WeekTotalOpens,
SUM(CASE WHEN [ActivityDate] >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @DT), 0)
THEN CASE WHEN EmailActivityType = 'OPEN' THEN 1 ELSE 0 END END) AS MonthTotalOpens,
SUM(CASE WHEN [ActivityDate] >= DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @DT), 0)
THEN CASE WHEN EmailActivityType = 'OPEN' THEN 1 ELSE 0 END END) AS QuarterTotalOpens,
SUM(CASE WHEN [ActivityDate] >= DATEADD(YEAR, DATEDIFF(YEAR, 0, @DT), 0)
THEN CASE WHEN EmailActivityType = 'OPEN' THEN 1 ELSE 0 END END) AS YearTotalOpens
FROM EmailActivity
WHERE DATEPART(YEAR, [ActivityDate]) = DATEPART(YEAR, @DT)
GROUP BY EmailAddressID,emailaddress

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-08-20 : 23:12:16

you can use AND condition instead of nested CASE WHEN . . it will give you less headache looking at the query


	SUM(CASE WHEN [ActivityDate] >= DATEADD(WEEK, DATEDIFF(WEEK, 0, @DT), 0)
		 AND  EmailActivityType = 'OPEN' THEN 1 ELSE 0 END) AS WeekTotalOpens,

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources