HAVING Clause has no effect

I have this stored procedure. I want to run a few simple SQL functions against my tables. In particular I want to take a subset of records (One or Two years worth) and calculate AVG, VAR and STDEV.

It does not work the way I thought it would. I end up with the whole input table in #tempor1 which is about 6 years worth of records.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF

GO
ALTER PROCEDURE [dbo].[findAve1YearDailyClose_MSFT]
AS
BEGIN
SET NOCOUNT ON;
SELECT adjClosed, volume INTO #tempor1 FROM dbo.dailyCl_MSFT
GROUP BY dateTimed, adjClosed, volume
HAVING (dateTimed > DATEADD (year, -1, MAX (dateTimed)))

SELECT AVG (adjClosed) AS "AVGAdjClose1Year",
VAR (adjClosed) AS "VARAdjClose1Year", AVG (volume) AS "AVGVolume1Year",
STDEV (volume) AS "STDEVVolume1Year", COUNT (*) AS "total"
FROM #tempor1
END

Thus if I change the number of years I subtract from the latest date from 1 to 2 I end up with the same result. What is the problem?

Thanks.

What about using:

SELECT adjClosed, volume INTO #tempor1
FROM dbo.dailyCl_MSFT
WHERE dateTimed > (SELECT DATEADD(year, -1, MAX (dateTimed)) FROM dbo.dailyCl)
GROUP BY dateTimed, adjClosed, volume

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

What about using:

SELECT adjClosed, volume INTO #tempor1
FROM dbo.dailyCl_MSFT
WHERE dateTimed > (SELECT DATEADD(year, -1, MAX (dateTimed)) FROM dbo.dailyCl)
GROUP BY dateTimed, adjClosed, volume

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

It sure worked! Many thanks for a lesson. Marked as answered!

Thanks.

sql

Hash join

Can any one explain me this "Option [hash join]"...
what that Option [hash join] will effect in this query.
select * from [group] inner join patientgroup on PA_PatientID = PG_PatientID
Option [hash join]
Thanks
Noor
Noor wrote:
> Can any one explain me this "Option [hash join]"...
> what that Option [hash join] will effect in this query.
> select * from [group] inner join patientgroup on PA_PatientID =
> PG_PatientID Option [hash join]
>
> Thanks
> Noor
It will force SQL Server to use a hash join on the tables instead of a
nested loop join or a merge join. Generally, you want to let SQL Server
make up its own mind about what the best join operation is. Essentially,
a hash join forces SQL Server to create hash values on both tables in
order to determine the matching values.
To see how it affects your query test it in Query Analyzer with and
without the hint.
See Understanding Hash Joins in the BOL.
David G.