Author 
Topic 

caoneill
Starting Member
10 Posts 
Posted  08/20/2014 : 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 subaggregate. What is the best way to approach? Thanks so much! 

caoneill
Starting Member
10 Posts 
Posted  08/20/2014 : 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)
Singapore
17689 Posts 
Posted  08/20/2014 : 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 Time is always against us




Topic 


