MIN Date function in Having clause returns error

[Chuck712]

I am tried several approaches to this and searched for an answer but haven't found anything so here goes.
I have been tasked with getting the result from an activity tracking table for the first 30 days of activity. Problem is each user
has a different activity start date. The results are calculated daily so there are many rows per user. What I have done is to try to use a min function on the date and a dateadd function to get 1 month from the min and only take those rows and sum them. I get the dreaded
"An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."

Below is my current query:
SELECT distinct tbl1_cst_key as CSTKEY,
tbl1_activity_date,
sum(tbl1_summary_steps) AS TotalSteps,
sum(tbl1_summary_floors)AS TotalFloors,
sum(tbl1_summary_distance_total)AS TotalDistance,
sum(tbl1_summary_activeScore)AS TotalActiveScore,
sum(tbl1_summary_sedentaryMinutes)AS TotalSedentaryMinutes,
sum(tbl1_summary_fairlyActiveMinutes)As TotalFairlyActiveMinutes,
sum(tbl1_summary_veryActiveMinutes)AS TotalVeryActiveMinutes,
sum(tbl1_summary_distance_lightlyActive)AS TotalLighlyActiveDistance,
sum(tbl1_summary_distance_moderatelyActive)AS TotalModeratelyActiveDistance,
sum(tbl1_summary_distance_sedentaryActive)AS TotalSedentaryActiveDistance,
sum(tbl1_summary_distance_veryActive) AS TotalVeryActiveDistance

from
tbl1_activity_log
GROUP BY tbl1_ind_cst_key,tbl1_activity_date
HAVING tbl1_activity_date between convert(varchar(10),MIN(tbl1_activity_date),101)
and convert(varchar(10),DATEADD(month,1,MIN(tbl1_activity_date)),101)

 

[gmmastros]

I'm not 100% sure why you would be getting that error, but I'm reasonably certain that you're headed down the wrong path, too.

Try this query. If it works for you, and you want me to explain it, just ask.

; With EndDates As
(
  Select    tbl1_cst_key,
            DateAdd(Month, 1, DateAdd(Day, DateDiff(Day, 0, MIN(tbl1_activity_date)), 0)) As EndDate
  From      tbl1_activity_log
  Group By  tbl1_cst_key
)
SELECT	tbl1_cst_key as CSTKEY,
		tbl1_activity_date,
		sum(tbl1_summary_steps) AS TotalSteps,
		sum(tbl1_summary_floors)AS TotalFloors,
		sum(tbl1_summary_distance_total)AS TotalDistance,
		sum(tbl1_summary_activeScore)AS TotalActiveScore,
		sum(tbl1_summary_sedentaryMinutes)AS TotalSedentaryMinutes,
		sum(tbl1_summary_fairlyActiveMinutes)As TotalFairlyActiveMinutes,
		sum(tbl1_summary_veryActiveMinutes)AS TotalVeryActiveMinutes,
		sum(tbl1_summary_distance_lightlyActive)AS TotalLighlyActiveDistance,
		sum(tbl1_summary_distance_moderatelyActive)AS TotalModeratelyActiveDistance,
		sum(tbl1_summary_distance_sedentaryActive)AS TotalSedentaryActiveDistance,
		sum(tbl1_summary_distance_veryActive) AS TotalVeryActiveDistance
from	tbl1_activity_log
        Inner Join StartDates
		  On tbl1_activity_log.tbl1_cst_key = StartDates.tbl1_cst_key
		  And tbl1_activity_log.tbl1_activity_date < EndDates.EndDate
GROUP BY tbl1_ind_cst_key

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chuck712]

Not only headed down the wrong path, but going to a different zip code. Where you have "; With EndDates As" in the first line in the above code, I assume that there was more there. Like a with StartDates logic? Yes, I would appreciate an explanation, as I struggled with this for a couple of days. I did get deeper into nested queries, but figured I would just start over.
Thanks George.

 

[gmmastros]

Nope. Nothing more there.

I was hoping you would copy/paste that code directly in to a query window and run it. Then let me know if it returns the correct results. I absolutely don't mind explaining things in detail, but I want to make sure it's right before I properly explain something that's wrong.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chuck712]

Thanks. My database did not recognize StartDates or EndDates. I did copy and paste the query directly into the query window, however I changed the field names and table names. In the query I posted they were changed to protect the innocent. I am going to make sure that I haven't messed something up.

 

[Chuck712]

Here is the query that cut and pasted with the correct prefix and table name.
;With EndDates As
(
Select h48_ind_cst_key,
DateAdd(Month, 1, DateAdd(Day, DateDiff(Day,0, MIN(h48_activity_date)), 0)) As EndDate
From client_activity_log
Group By h48_ind_cst_key
)
SELECT h48_ind_cst_key as CSTKEY,
h48_activity_date,
sum(h48_summary_steps) AS TotalSteps,
sum(h48_summary_floors)AS TotalFloors,
sum(h48_summary_distance_total)AS TotalDistance,
sum(h48_summary_activeScore)AS TotalActiveScore,
sum(h48_summary_sedentaryMinutes)AS TotalSedentaryMinutes,
sum(h48_summary_fairlyActiveMinutes)As TotalFairlyActiveMinutes,
sum(h48_summary_veryActiveMinutes)AS TotalVeryActiveMinutes,
sum(h48_summary_distance_lightlyActive)AS TotalLighlyActiveDistance,
sum(h48_summary_distance_moderatelyActive)AS TotalModeratelyActiveDistance,
sum(h48_summary_distance_sedentaryActive)AS TotalSedentaryActiveDistance,
sum(h48_summary_distance_veryActive) AS TotalVeryActiveDistance
from client_activity_log
Inner Join StartDates
On client_activity_log.h48_ind_cst_key = StartDates.h48_ind_cst_key
And client_activity_log.h48_activity_date < EndDates.EndDate
GROUP BY h48_ind_cst_key


The error is that StartDates is not recognized. (I think that EndDates would also not be recognized)

 

[gmmastros]

Try this:

;With EndDates As
(
  Select   h48_ind_cst_key,
           DateAdd(Month, 1, DateAdd(Day, DateDiff(Day,0, MIN(h48_activity_date)), 0)) As EndDate
  From     client_activity_log
  Group By h48_ind_cst_key
)
SELECT	h48_ind_cst_key as CSTKEY,
        sum(h48_summary_steps) AS TotalSteps,
        sum(h48_summary_floors)AS TotalFloors,
        sum(h48_summary_distance_total)AS TotalDistance,
        sum(h48_summary_activeScore)AS TotalActiveScore,
        sum(h48_summary_sedentaryMinutes)AS TotalSedentaryMinutes,
        sum(h48_summary_fairlyActiveMinutes)As TotalFairlyActiveMinutes,
        sum(h48_summary_veryActiveMinutes)AS TotalVeryActiveMinutes,
        sum(h48_summary_distance_lightlyActive)AS TotalLighlyActiveDistance,
        sum(h48_summary_distance_moderatelyActive)AS TotalModeratelyActiveDistance,
        sum(h48_summary_distance_sedentaryActive)AS TotalSedentaryActiveDistance,
        sum(h48_summary_distance_veryActive) AS TotalVeryActiveDistance
from	client_activity_log 
        Inner Join EndDates
          On client_activity_log.h48_ind_cst_key = EndDates.h48_ind_cst_key
          And client_activity_log.h48_activity_date < EndDates.EndDate
GROUP BY client_activity_log.h48_ind_cst_key

By the way, when posting code, it will format better if you put it in code tags, like this:

[ignore]

-- paste your code here

[/ignore]

I think this query should work. If not, please post the error message. I promise I will explain it once we get things working.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chuck712]

Thanks. I have to remote into another network to get to the machine, so it takes some time. Standby

 

[Chuck712]

It ran and without taking the rest of the night to verify, it looks correct. I had to change SELECT h48_ind_cst_key since it gave an ambigous error, but this appears to be what I was looking for. I was hoping to reuse this when I have to pull the same results for the "third" month of ownership.

 

[gmmastros]

ok. cool. I'm glad it works.

Now it's time for the explanation...

;With EndDates As
(
  Select   h48_ind_cst_key,
           DateAdd(Month, 1, DateAdd(Day, DateDiff(Day,0, MIN(h48_activity_date)), 0)) As EndDate
  From     client_activity_log
  Group By h48_ind_cst_key
)

This part of the code is called a Common Table Expression (or CTE for short). If you run the code within it, you will see that it returns exactly one row for each customer key, and also returns the min date + 1 month. This "min date + 1 month" was my doing because that is what you said you wanted in your original post. It doesn't really matter though because I will show you how you can easily modify the query to show any month.

Anyway... back to the CTE. There are some rules regarding CTE's. One rule is... the previous line of SQL code must end with a semi colon. As a safety precaution, I always put a semi-colon at the beginning because I don't end every line with a semi-colon like I should.

The next "rule" is that every CTE must have a name. In this case, it's called EndDates. You can name it anything you want. Whatever name you choose should make sense with regards to the data that it returns.

The next rule is that you must use parenthesis around the query. This one is easy enough.

Now... the reason CTE are helpful is because it makes coding a lot easier. Outside of those parenthesis, you can refer to the query by it's name. From outside the CTE, the query acts very much like a regular table. This allows you to write a big query in smaller pieces, making it easier to understand and use.

Now... to make the query accommodate any month of ownership, we will need to change the code within the CTE, like this:

Declare @MonthNumber Int

Set @MonthNumber = 3

;With DateRange As
(
  Select   h48_ind_cst_key,
           DateAdd(Month, @MonthNumber, DateAdd(Day, DateDiff(Day, 0, Min(h48_activity_date)), 0)) As StartDate,
           DateAdd(Month, @MonthNumber + 1, DateAdd(Day, DateDiff(Day,0, MIN(h48_activity_date)), 0)) As EndDate
  From     client_activity_log
  Group By h48_ind_cst_key
)
SELECT	client_activity_log.h48_ind_cst_key as CSTKEY,
        sum(h48_summary_steps) AS TotalSteps,
        sum(h48_summary_floors)AS TotalFloors,
        sum(h48_summary_distance_total)AS TotalDistance,
        sum(h48_summary_activeScore)AS TotalActiveScore,
        sum(h48_summary_sedentaryMinutes)AS TotalSedentaryMinutes,
        sum(h48_summary_fairlyActiveMinutes)As TotalFairlyActiveMinutes,
        sum(h48_summary_veryActiveMinutes)AS TotalVeryActiveMinutes,
        sum(h48_summary_distance_lightlyActive)AS TotalLighlyActiveDistance,
        sum(h48_summary_distance_moderatelyActive)AS TotalModeratelyActiveDistance,
        sum(h48_summary_distance_sedentaryActive)AS TotalSedentaryActiveDistance,
        sum(h48_summary_distance_veryActive) AS TotalVeryActiveDistance
from	client_activity_log 
        Inner Join DateRange
          On client_activity_log.h48_ind_cst_key = DateRange.h48_ind_cst_key
          And client_activity_log.h48_activity_date >= DateRange.StartDate
          And client_activity_log.h48_activity_date < DateRange.EndDate
GROUP BY client_activity_log.h48_ind_cst_key

Notice that I changed the name of the CTE to be DateRange because we are now dealing with a range. I also changed the join clause (near the end of the query) to use both values (start date and end date). You can simply change the month number at the top to retrieve any month.

There is a slight gotcha that you should be aware of. Suppose you have a new customer that's been with you for 3 months. If you run this query for month number 10, that new customer will not show in the list. This is not a problem with the query, it's simply that there's no data for that particular customer.

Does this make sense? IF it doesn't, please let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chuck712]

Thanks George, This makes perfect sense now that I see it. And it was exactly what I was looking for. I searched BOL and many SQL web sites, but nothing like this came to light.