#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    4
    Rep Power
    0

    Trouble with Having clause


    I Need a query to get the value for the first day of each month at 7am for a fiscal year. Here is what I have.
    Code:
    SELECT Value as ELEVATION, 
    		DATEPART(Month, DateTime) AS EMONTH, 
    		DATEPART(Year, DateTime) AS EYEAR, 
    		DATEPART(Day, DateTime) as DAY, 
    		DATEPART(Hour, DateTime) as HOUR 
    FROM Runtime.dbo.AnalogHistory 
    WHERE TagName = 'ScituateGDGHReservoir.Level' AND 
    		DateTime > '06/30/2012 11:59:59PM' AND 
    		DateTime < '07/01/2013 12:00:00AM' 
    GROUP BY Value, DateTime 
    HAVING DatePart(Day, DateTime) = '1' AND DATEPART(Hour, DateTime) = '7' 
    ORDER BY EYEAR Asc, EMONTH Asc, DAY asc;
    When I run it I get nothing.
    But when I run this query
    Code:
    SELECT * FROM Runtime.dbo.AnalogHistory WHERE DateTime = '07/01/2012 7:00:00AM' AND TagName = 'ScituateGDGHReservoir.Level' ORDER BY DateTime ASC
    I get a result.

    Any Ideas??

    Thanks

    Ray
  2. #2
  3. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    The HAVING clause is to be used only when one of the things being compared is a rollup operation like SUM or COUNT. You can just put your DATEPART comparisons in the normal WHERE.

    Probably. I'm not a MSSQL guy.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    10
    Rep Power
    0

    Is this an aggregate function?


    The above poster is correct, Where is for passive functions Having is for aggregate functions that return less rows than came in.

    The way that you have your query written doesn't include any aggregate functions like SUM() or MAX()

    After moving the Having condition to a where clause you may want to consider removing your Group By and making this a SELECT DISTINCT
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    117
    Rep Power
    9
    Originally Posted by mkelley0309
    The above poster is correct, Where is for passive functions Having is for aggregate functions that return less rows than came in.

    The way that you have your query written doesn't include any aggregate functions like SUM() or MAX()

    After moving the Having condition to a where clause you may want to consider removing your Group By and making this a SELECT DISTINCT
    I.m not agree to with remove Group By it is better to have group by ther distinct ( for server performance) and it is not a problem there. The question is why query did not return any results...
    My question is how you define DateTime column in database is that a char or varchar? if so in condition
    DateTime > '06/30/2012 11:59:59PM' AND
    DateTime < '07/01/2013 12:00:00AM'
    you are not comparing real dates you comparring just strings and if will be a problem, try to change to

    cast([DateTime] as datetime) > '06/30/2012 11:59:59PM' AND
    cast([DateTime] as datetime) < '07/01/2013 12:00:00AM'

    And please for future never use reserved words for column name. DateTime is reserved for data type...

IMN logo majestic logo threadwatch logo seochat tools logo