Having a HAVING issue - 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 2005 Forums

Transact-SQL (2005)

Having a HAVING issue

Author

Topic

mattlightbourn
Starting Member

37 Posts

Posted - 2009-12-01 : 07:30:38

Dear all,

I don't understand why I'm having a HAVING issue with this script. I need the where statements and I need to group the whole lot by the Company and FunctionalGroup.

The result I'm after is


Company   FunctionalGroup   Last3Months    Last6Months    Prev3Months
AVIVA     Designers         1350           3600           1250
AVIVA     Production         850           1995           1145

What have I done that's wrong? Thanks


SELECT	  FM_Timesheets.Studio_Company
		, FM_Timesheets.FunctionalGroup
		,Last6Months.TimeEntryValue
		,Last3Months.TimeEntryValue
		,Prev3Months.TimeEntryValue
FROM		FM_Timesheets
LEFT OUTER JOIN  (SELECT 
			SUM		(TimeEntry)  as TimeEntryValue, Studio_Company, FunctionalGroup 
			FROM	FM_Timesheets 
			WHERE	TimeDate > MAX(TimeDate)-180)
			GROUP BY Studio_Company, FunctionalGroup)
			Last6Months
ON FM_Timesheets.Studio_Company = Last6Months.Studio_Company
LEFT OUTER JOIN  (SELECT 
			SUM		(TimeEntry)  as TimeEntryValue, Studio_Company, FunctionalGroup 
			FROM	FM_Timesheets 
			WHERE	TimeDate > MAX(TimeDate)-90)
			GROUP BY Studio_Company, FunctionalGroup)
			Last3Months
ON FM_Timesheets.Studio_Company =Last3Months.Studio_Company
LEFT OUTER JOIN (SELECT 
			SUM (TimeEntry) as TimeEntryValue, Studio_Company, FunctionalGroup 
			FROM	FM_Timesheets 
			WHERE	TimeDate > MAX(TimeDate)-90)
			AND		TimeDate < MAX(TimeDate)-180
			GROUP BY Studio_Company, FunctionalGroup)
			 Prev3Months
ON FM_Timesheets.Studio_Company =  Prev3Months.Studio_Company
	GROUP BY	FM_Timesheets.Studio_Company
		, FM_Timesheets.FunctionalGroup
	ORDER BY	FM_Timesheets.Studio_Company
		, FM_Timesheets.FunctionalGroup

mattlightbourn
Starting Member

37 Posts

Posted - 2009-12-01 : 07:50:23

I've edited it, maybe this looks better but, it still doesn't work:


SELECT	  FM_Timesheets.Studio_Company
		, FM_Timesheets.FunctionalGroup
		,Last6Months.TimeEntryValue
		,Last3Months.TimeEntryValue
		,Prev3Months.TimeEntryValue
FROM		FM_Timesheets
LEFT OUTER JOIN  (SELECT 
			SUM		(TimeEntry)  as TimeEntryValue, Studio_Company, FunctionalGroup 
			FROM	FM_Timesheets 
			WHERE	TimeDate > MAX(TimeDate)-180)
--			GROUP BY Studio_Company, FunctionalGroup)
			Last6Months
ON	FM_Timesheets.Studio_Company = Last6Months.Studio_Company
AND	FM_Timesheets.FunctionalGroup = Last6Months.FunctionalGroup

LEFT OUTER JOIN  (SELECT 
			SUM		(TimeEntry)  as TimeEntryValue, Studio_Company, FunctionalGroup 
			FROM	FM_Timesheets 
			WHERE	TimeDate > MAX(TimeDate)-90)
--			GROUP BY Studio_Company, FunctionalGroup)
			Last3Months
ON	FM_Timesheets.Studio_Company =Last3Months.Studio_Company
AND	FM_Timesheets.FunctionalGroup = Last3Months.FunctionalGroup

LEFT OUTER JOIN (SELECT 
			SUM (TimeEntry) as TimeEntryValue, Studio_Company, FunctionalGroup 
			FROM	FM_Timesheets 
			WHERE	TimeDate > MAX(TimeDate)-90
			AND		TimeDate < MAX(TimeDate)-180)
--			GROUP BY Studio_Company, FunctionalGroup)
			 Prev3Months
ON	FM_Timesheets.Studio_Company =  Prev3Months.Studio_Company
AND	FM_Timesheets.FunctionalGroup = Prev3Months.FunctionalGroup

	GROUP BY	FM_Timesheets.Studio_Company
		, FM_Timesheets.FunctionalGroup

	ORDER BY	FM_Timesheets.Studio_Company
		, FM_Timesheets.FunctionalGroup

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-01 : 08:01:30

You say you're having a HAVING issue, but I don't see any HAVING clauses in your script. Also, you can't do aggregates in a where clause, maybe that's where the HAVING clause would work
For example, instead of


SELECT 
			SUM (TimeEntry) as TimeEntryValue, Studio_Company, FunctionalGroup 
			FROM	FM_Timesheets 
			WHERE	TimeDate > MAX(TimeDate)-90
			AND		TimeDate < MAX(TimeDate)-180)
--			GROUP BY Studio_Company, FunctionalGroup

use


SELECT 
SUM (TimeEntry) as TimeEntryValue, Studio_Company, FunctionalGroup 
FROM	FM_Timesheets 

GROUP BY Studio_Company, FunctionalGroup
HAVING DATEDIFF (day,TimeDate,MAX(TimeDate))>90

Jim

Everyday I learn something that somebody else already knew

mattlightbourn
Starting Member

37 Posts

Posted - 2009-12-01 : 08:05:21

I'll give it a go, that sounds bang on - thank you

mattlightbourn
Starting Member

37 Posts

Posted - 2009-12-01 : 08:16:13

I'm afriad that didn't work. I also forgot that I would like the report to be grouped my year and month as well so that the results do not come out as a record for every day that there was a timesheet.

Here is my revised code:


SELECT	  FM_Timesheets.Studio_Company
		, FM_Timesheets.FunctionalGroup
		, MONTH(FM_Timesheets.TimeDate) AS MonthNum
		, YEAR(FM_Timesheets.YearNum)	AS YearNum
		,Last6Months.TimeEntryValue
		,Last3Months.TimeEntryValue
		,Prev3Months.TimeEntryValue

FROM		FM_Timesheets

LEFT OUTER JOIN  (SELECT 
			SUM		(TimeEntry)  as TimeEntryValue, Studio_Company, FunctionalGroup, monthnum
			FROM	FM_Timesheets 
			GROUP BY YEAR(TimeDate), MONTH(TimeDate), Studio_Company, FunctionalGroup
			HAVING DATEDIFF (day,TimeDate,MAX(TimeDate))<180)
			Last6Months
ON	FM_Timesheets.Studio_Company = Last6Months.Studio_Company
AND	FM_Timesheets.FunctionalGroup = Last6Months.FunctionalGroup

LEFT OUTER JOIN  (SELECT 
			SUM		(TimeEntry)  as TimeEntryValue, Studio_Company, FunctionalGroup, monthnum
			FROM	FM_Timesheets 
			GROUP BY YEAR(TimeDate), MONTH(TimeDate), Studio_Company, FunctionalGroup
			HAVING DATEDIFF (day,TimeDate,MAX(TimeDate))<90)
			Last3Months
ON	FM_Timesheets.Studio_Company =Last3Months.Studio_Company
AND	FM_Timesheets.FunctionalGroup = Last3Months.FunctionalGroup

LEFT OUTER JOIN (SELECT 
			SUM (TimeEntry) as TimeEntryValue, Studio_Company, FunctionalGroup, monthnum 
			FROM	FM_Timesheets 
			GROUP BY YEAR(TimeDate), MONTH(TimeDate), Studio_Company, FunctionalGroup
			HAVING	DATEDIFF (day,TimeDate,MAX(TimeDate))<90
			AND		DATEDIFF (day,TimeDate,MAX(TimeDate))<180)
			 Prev3Months
ON	FM_Timesheets.Studio_Company =  Prev3Months.Studio_Company
AND	FM_Timesheets.FunctionalGroup = Prev3Months.FunctionalGroup

	GROUP BY	FM_Timesheets.Studio_Company
		, FM_Timesheets.FunctionalGroup
		, YEAR(TimeDate), MONTH(TimeDate)
	ORDER BY	FM_Timesheets.Studio_Company
		, FM_Timesheets.FunctionalGroup

The error I now get is:

Msg 8121, Level 16, State 1, Line 1
Column 'FM_Timesheets.TimeDate' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

It's infuriating - thank you again for your help

Kind Regards
Matt

mattlightbourn
Starting Member

37 Posts

Posted - 2009-12-01 : 08:30:54

I'm getting the error even when I have made it into one part of the issue - I've never had this problem before, grrrh

Msg 8121, Level 16, State 1, Line 1
Column 'FM_Timesheets.TimeDate' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.


SELECT 
		Year(TimeDate)
		, Month(TimeDate)
		, Studio_Company
		, FunctionalGroup
		, SUM(TimeEntry) AS SumHours
FROM FM_Timesheets
GROUP BY Studio_Company, FunctionalGroup, Year(TimeDate), Month(TimeDate)
HAVING	DATEDIFF(Day, TimeDate, MAX(TimeDate))>90

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-01 : 08:33:44

Hi.

First off, here's your query with some slightly better formatting.


SELECT
	FM_Timesheets.Studio_Company
	, FM_Timesheets.FunctionalGroup
	, MONTH(FM_Timesheets.TimeDate) AS MonthNum
	, YEAR(FM_Timesheets.YearNum)	AS YearNum
	,Last6Months.TimeEntryValue
	,Last3Months.TimeEntryValue
	,Prev3Months.TimeEntryValue
FROM
	FM_Timesheets

	LEFT OUTER JOIN (
		SELECT 
			SUM (TimeEntry)  as TimeEntryValue
			, Studio_Company
			, FunctionalGroup
			, monthnum
		FROM
			FM_Timesheets 
		GROUP BY
			YEAR(TimeDate)
			, MONTH(TimeDate)
			, Studio_Company
			, FunctionalGroup
		HAVING
			DATEDIFF (day, TimeDate , MAX(TimeDate) ) < 180
	)
	Last6Months ON
		FM_Timesheets.Studio_Company = Last6Months.Studio_Company
		AND	FM_Timesheets.FunctionalGroup = Last6Months.FunctionalGroup

	LEFT OUTER JOIN (
		SELECT 
			SUM (TimeEntry)  as TimeEntryValue
			, Studio_Company
			, FunctionalGroup
			, monthnum
		FROM
			FM_Timesheets 
		GROUP BY
			YEAR(TimeDate)
			, MONTH(TimeDate)
			, Studio_Company
			, FunctionalGroup
		HAVING
			DATEDIFF (day,TimeDate,MAX(TimeDate) ) < 90
		)
		Last3Months ON
			FM_Timesheets.Studio_Company =Last3Months.Studio_Company
			AND	FM_Timesheets.FunctionalGroup = Last3Months.FunctionalGroup

	LEFT OUTER JOIN (
		SELECT 
			SUM (TimeEntry) as TimeEntryValue
			, Studio_Company
			, FunctionalGroup
			, monthnum 
		FROM
			FM_Timesheets 
		GROUP BY
			YEAR(TimeDate)
			, MONTH(TimeDate)
			, Studio_Company
			, FunctionalGroup
		HAVING
			DATEDIFF ( day,TimeDate,MAX(TimeDate) ) < 90
			AND DATEDIFF ( day,TimeDate,MAX(TimeDate) ) < 180
		)
		Prev3Months ON
			FM_Timesheets.Studio_Company =  Prev3Months.Studio_Company
			AND	FM_Timesheets.FunctionalGroup = Prev3Months.FunctionalGroup
GROUP BY
	FM_Timesheets.Studio_Company
	, FM_Timesheets.FunctionalGroup
	, YEAR(TimeDate), MONTH(TimeDate)
ORDER BY
	FM_Timesheets.Studio_Company
	, FM_Timesheets.FunctionalGroup

The problem is in your left joins.

You can debug it by just trying to get this part working:


SELECT 
			SUM (TimeEntry)  as TimeEntryValue
			, Studio_Company
			, FunctionalGroup
			, monthnum
		FROM
			FM_Timesheets 
		GROUP BY
			YEAR(TimeDate)
			, MONTH(TimeDate)
			, Studio_Company
			, FunctionalGroup
		HAVING
			DATEDIFF (day, TimeDate , MAX(TimeDate) ) < 180

Now maybe you should try and explain what you want to accomplish. The technical reason the error is thrown is because timedate is not in the group by clause YEAR(timedate) and MONTH(timedate) are but that's no enough.

Maybe if you gave some sample data and expected output (just for this chunk of code) we can suggest something (probably a better way).

I think that this could be rewritten completely without resorting to YEAR and MONTH and without using 3 different left joins.

Fell free to just start at the start and post sample data for the relevant tables and desired results. I think you've got stuck in the trap of just writing MORE sql to try and get what you want.

Regards,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-01 : 08:46:27

Maybe ditch the having clauses altogether.

Does this work for you:


DECLARE @referenceDate DATETIME
DECLARE @sixMonthsAgo DATETIME

-- ReferenceDate is as of right now -- but this could be any time specified
SET @referenceDate = GETDATE()

-- set sixMonthsAgo = to the startof the day 6 months before the reference date
SET @sixMonthsAgo = DATEADD(DAY, 0, DATEDIFF(DAY, 0, DATEADD(MONTH, -6, @referenceDate)))

SELECT @referenceDate
SELECT @sixMonthsAgo

-- Now we can ditch the HAVING clause and still use any INDEX on the timeDate column
SELECT 
	SUM (TimeEntry)  as TimeEntryValue
	, Studio_Company
	, FunctionalGroup
	, monthnum
FROM
	FM_Timesheets
WHERE
	timeDate >= @sixMonthsAgo
	AND timeDate < @referenceDate
GROUP BY
	YEAR(TimeDate)
	, MONTH(TimeDate)
	, Studio_Company
	, FunctionalGroup

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mattlightbourn
Starting Member

37 Posts

Posted - 2009-12-01 : 08:52:35

Thank you for your reply.

I have a table called FM_Timesheets with individual timesheet entries on there for different dates.

Each timesheet has a user, who is a part of a FunctionalGroup and the users work at any one of the studios, so there is also Studio_Company.

What I want to do is create a data source for a 'bubble chart' for a dashboard.

It requires the following:

Studio_Company,
The average of TimeEntry from the last 3 months, The average of the TimeEntry from the PREVIOUS 3 months to that.

To get the last three months of data I can do this:


SELECT 
		Year(TimeDate) AS YearNum
		, Month(TimeDate) AS MonthNum
		, Studio_Company
		, FunctionalGroup
		, SUM(TimeEntry)/3 AS SumHours
FROM FM_Timesheets

WHERE	timedate > '2009-08-01'

GROUP BY Studio_Company, FunctionalGroup, Year(TimeDate), Month(TimeDate)

But I want the WHERE to look at what month we're in NOW - I guess done by GetDate() - minus 3 months, which would give me the manually entered above.

To get the previous 3 months average:


SELECT 
		Year(TimeDate) AS YearNum
		, Month(TimeDate) AS MonthNum
		, Studio_Company
		, FunctionalGroup
		, SUM(TimeEntry)/3 AS SumHours
FROM FM_Timesheets

WHERE	timedate > '2009-05-01' AND
WHERE	timedate < '2009-08-01'

GROUP BY Studio_Company, FunctionalGroup, Year(TimeDate), Month(TimeDate)

What I want as my end result is:


YearNum	 Studio_Company    FunctionalGroup       AVGLast     AVGPrev

2009	 Chevron	   Design Production   37.166666   41.554321
2009	 Chevron	   Job Management     287.500000  255.609455
2009	 Chevron	   Digital Media       23.083333   20.409550
2009	 Chevron           Creative	       26.666666   27.584875

This can be in its own table created once a month then referenced by the chart I need to get into SharePointless, I mean, sharepoint.

I hope this helps. Thank you for your support

Matt

mattlightbourn
Starting Member

37 Posts

Posted - 2009-12-01 : 08:54:17

Sorry Charlie, just noticed your other reply. I'm checking that now

thanks

mattlightbourn
Starting Member

37 Posts

Posted - 2009-12-01 : 08:58:37

Yeah, I think it does work! Mind you, how easy is it to get two ranges in there on the same rows? I take it I need a sub query within a query?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-01 : 09:03:24

No -- I don't think so just repeat the whole process with another variable. Something like:


DECLARE @referenceDate DATETIME
DEClARE @threeMonthsAgo DATETIME
DECLARE @sixMonthsAgo DATETIME

-- ReferenceDate is as of right now -- but this could be any time specified
SET @referenceDate = GETDATE()

-- Set threeMonthsAgo
SET @threeMonthsAgo = DATEADD(DAY, 0, DATEDIFF(DAY, 0, DATEADD(MONTH, -3, @referenceDate)))

-- set sixMonthsAgo = to the startof the day 6 months before the reference date
SET @sixMonthsAgo = DATEADD(DAY, 0, DATEDIFF(DAY, 0, DATEADD(MONTH, -6, @referenceDate)))

SELECT @referenceDate
SELECT @threeMonthsAgo
SELECT @sixMonthsAgo

-- Now we can ditch the HAVING clause and still use any INDEX on the timeDate column

-- Now to three months ago
SELECT 
	SUM (TimeEntry)  as TimeEntryValue
	, Studio_Company
	, FunctionalGroup
	, monthnum
FROM
	FM_Timesheets
WHERE
	timeDate >= @threeMonthsAgo
	AND timeDate < @referenceDate
GROUP BY
	YEAR(TimeDate)
	, MONTH(TimeDate)
	, Studio_Company
	, FunctionalGroup

-- The three months prior to that
SELECT 
	SUM (TimeEntry)  as TimeEntryValue
	, Studio_Company
	, FunctionalGroup
	, monthnum
FROM
	FM_Timesheets
WHERE
	timeDate >= @sixMonthsAgo
	AND timeDate < @threeMonthsAgo
GROUP BY
	YEAR(TimeDate)
	, MONTH(TimeDate)
	, Studio_Company
	, FunctionalGroup

And then do each of those selects as a LEFT JOIN on whatever is appropriate.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mattlightbourn
Starting Member

37 Posts

Posted - 2009-12-01 : 09:05:33

thank you very much, you're a star!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources