Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Sep 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy SQL Data will not "select"

    Hopefully THIS is the correct place for this thread.

    I have been working on the query below for two days now. When I try to used datetime values from a reference table (view dbo.v_BOS_EOS which does return all data when opened individually) to indicate at what 'DateTime's I wish to read the 'Value', I get no results .

    However, when I use the (remarked) variables (aka @ values), the query produces one specific result exactly as requested. I need to return a result set for all entries in the reference table(view).

    Your help is greatly appreciated. If I need to clarify anything or give more information, please let me know.

    Thank you

    Code:
    /*
    Script to read total primary input volume (dbo.AnalogHistory.Value)
    at one minute before the shift start time.
    2010-09-30 chi2jjk@ups.com
    
    Model Query created in "Active Factory Query":
    	SET NOCOUNT ON
    	DECLARE @StartDate DateTime
    	DECLARE @EndDate DateTime
    	SET @StartDate = '20100930 10:04:00.000'
    	SET @EndDate = '20100930 10:04:00.000'
    	SET NOCOUNT OFF
    	SELECT TagName, DateTime, Value, vValue
    	 FROM History
    	 WHERE TagName IN ('PRXC7INPUT')
    	 AND wwRetrievalMode = 'Full'
    	 AND wwVersion = 'Latest'
    	 AND DateTime >= @StartDate
    	 AND DateTime <= @EndDate
    */
    
    /* 
    	Solution does not use static time values stored in local variables (@) because the table should 
    	return results for all previously run shifts.  This section remarked.
    */
    SET NOCOUNT ON
    DECLARE @StartDate DateTime
    DECLARE @EndDate DateTime
    SET @StartDate = '2010-09-30 04:59:00'		-- Need to replace this value with the 'PriVolCk' time
    SET @EndDate = '2010-09-30 04:59:00'		-- Need to replace this value with the 'PriVolCk' time
    SET NOCOUNT OFF
    
    
    SELECT 
    	BES.SortDate, 
    	BES.Sort, 
    	H.TagName, 
    	H.DateTime, 
    	H.Value, 
    	CONVERT(NVARCHAR,H.DateTime,20) AS WWDateTime, 
    	CONVERT(NVARCHAR,BES.PriVolCheck,20) AS PriVolCk
    
     FROM 
    	 POG_Reporting.dbo.AnalogHistory AS H, 
    	 dbo.v_BOS_EOS AS BES
    
     WHERE 
    		 TagName IN ('PRXC7INPUT')	--Total Primary input volume
    	 AND wwRetrievalMode = 'Full'
    	 AND CAST(CONVERT(VARCHAR(10), H.DateTime, 101) AS DATETIME) = BES.SortDate	--Match the date.
    	 AND (
    			 /*If I un-remark the two criteria below and remark the ones below that, result is returned!?*/
    			 (
    			 H.DateTime >= @StartDate
    			 AND 
    			 H.DateTime <= @EndDate
    			 )
    		 --OR
    			 --(
    			 --DATEADD(MM,0,H.DateTime) >= DATEADD(MM,0,BES.PriVolCheck)
    			 --AND 
    			 --DATEADD(MM,0,H.DateTime) <= DATEADD(MM,0,BES.PriVolCheck)
    			 --AND
    			 --BES.PriVolCheck > CAST(CONVERT(NVARCHAR,DATEADD(YY,-1,GETDATE()),112) AS DATETIME)
    			 --)
    		 )

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Why do you want to cast/convert your DATETIME values to NVARCHAR???

    You should *NOT* do that. You should always compare DATETIME values *AS* DATETIMEs.

    But in any case, I can't see how your logic makes sense, at all.

    There is only *ONE VALUE* that will *EVER* match that WHERE clause:

    If you do
    Code:
    			 (
    			 H.DateTime >= BES.PriVolCheck
    			 AND 
    			 H.DateTime <= BES.PriVolCheck
    			 )
    then that is EXACTLY the same thing as doing
    Code:
    			 (
    			 H.DateTime = BES.PriVolCheck
    			 )
    *EXACTLY* matching, to the second.

    In your example code, where you are doing
    Code:
    SET @StartDate = '2010-09-30 04:59:00'    
    SET @EndDate = '2010-09-30 04:59:00'    
    ...
    			 (
    			 H.DateTime >= @StartDate
    			 AND 
    			 H.DateTime <= @EndDate
    			 )
    that is the same as doing
    Code:
    			 (
    			 H.DateTime >= '2010-09-30 04:59:00' 
    			 AND 
    			 H.DateTime <= '2010-09-30 04:59:00' 
    			 )
    and the ONLY value of H.DateTime that will satify the is *EXACTLY* '2010-09-30 04:59:00'.

    So what is the point of using >= and <= in place of = alone?????
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Sep 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Old Pedant, I truly appreciate your reply and your time. The arguments/questions that you presented are exactly why I am asking for help.

    I should state that the table being queried (H) is plant data that is continually inserted. The most pertinent columns in the table are TagName, DateTime, Value. I appologize for the code confusion, but I was trying different things and different combinations of things that might be causing the empty set returned. I was remarking and un-remarking items systematically in the hopes that I would get the combination that worked. That hasn't happened yet.

    As the top of the code states, I based this on a working query for one particular datetime value - the IDE created the '>=' and '<=' which I agree is identical to '='. My problem is that when the specific string is used by way of the @StartTime it works. I am trying to replace the variable with the resulting list of datetime values from BES.

    I am trying to get the value of 'Value' when the TagName is 'PRXC7INPUT' AND at the 'DateTime' = the whole list of BOS (Begin Of Shift) times. It works with individual datetime values in the @StartTime variable, but not when 'bumped' up against a table of datetime values.

    I hope this helps clarify what I am trying to do.
    Last edited by chi2jjk; 10-01-2010 at 11:51 AM.

  • #4
    New to the CF scene
    Join Date
    Sep 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Okay, let me try this:

    Code:
    SET NOCOUNT ON
    DECLARE @StartDate DateTime
    DECLARE @EndDate DateTime
    SET @StartDate = '2010-09-30 04:59:00'		-- Need to replace this value with the the list of 'PriVolCk' datetimes
    SET NOCOUNT OFF
    
    
    SELECT 
    	BES.SortDate, 
    	BES.Sort,  
    	H.Value, 
    	H.DateTime AS WWDateTime, 
    	BES.PriVolCheck AS PriVolCk
    
     FROM 
    	 POG_Reporting.dbo.AnalogHistory AS H, 
    	 POG_Reporting.dbo.v_BOS_EOS AS BES
    
     WHERE 
    	 H.TagName = 'PRXC7INPUT'
    	 AND wwRetrievalMode = 'Cyclic'
    	 AND wwCycleCount = 100
    	 AND wwVersion = 'Latest'
    	 AND CAST(CONVERT(VARCHAR(10), H.DateTime, 101) AS DATETIME) = BES.SortDate	--Match the date.
    	 AND H.DateTime = @StartDate --BES.PriVolCheck--
    The result of the above query is here:
    Code:
    2010-09-30 00:00:00.000	Day	6237	2010-09-30 04:59:00.000	2010-09-30 10:04:00.000
    2010-09-30 00:00:00.000	Night	6237	2010-09-30 04:59:00.000	2010-09-30 22:24:00.000
    2010-09-30 00:00:00.000	Sunrise	6237	2010-09-30 04:59:00.000	2010-09-30 04:59:00.000
    2010-09-30 00:00:00.000	Twilight	6237	2010-09-30 04:59:00.000	2010-09-30 17:09:00.000
    If I try to use the BES.PriVolCheck (which returns the datetime values in which I am interested) I get no results, but with the singlular value used in the variable, it works!?

    I really need help and appreciate your time looking at this. Thank you.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •