Solved

SQL - check on the stored procedure with extended date query

Posted on 2013-05-27
6
380 Views
Last Modified: 2013-07-08
Hi

I have inherited stored procedure below.  
The WHERE part seems a bit overly complicated for what is actually needed with each DatePart split out....
I wonder if there's reason why query has been written as is and not simpler?

INHERITED STORED PROCEDURE


parameters
@date datetime,
@PIE2IID as smallint
....

SELECT   tblInputs.value
FROM     tblInputs INNER JOIN
         tblTimeStamp ON tblInputs.timeStampKey = tblTimeStamp.timeStampKey
where DATEPART (D , tblTimeStamp.timeStamp ) =  DATEPART (D , @date)AND
        DATEPART (M , tblTimeStamp.timeStamp ) =  DATEPART (M , @date)AND
        DATEPART (YYYY , tblTimeStamp.timeStamp ) =  DATEPART (YYYY , @date)  AND
        DATEPART (HH , timeStamp ) =  DATEPART (HH , @date)AND
        DATEPART (MI , timeStamp ) =  DATEPART (MI , @date)
      AND tblInputs.PIE2_I_ID = @PIE2IID ;


SUGGESTED REVISED VERSION

SELECT   tblInputs.value
FROM     tblInputs INNER JOIN
         tblTimeStamp ON tblInputs.timeStampKey = tblTimeStamp.timeStampKey
where tblTimeStamp.timeStamp = @date
      AND tblInputs.PIE2_O_ID = @@PIE2IID ;
0
Comment
Question by:rwallacej
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 167 total points
ID: 39199507
I think you don't want to compare the seconds part. If so, you can have the WHERE condition like this.

SELECT   tblInputs.value
FROM     tblInputs INNER JOIN
         tblTimeStamp ON tblInputs.timeStampKey = tblTimeStamp.timeStampKey
where CONVERT(varchar(16),tblInputs.PIE2_O_ID,120) = CONVERT(varchar(16),@date,120)
      AND tblInputs.PIE2_O_ID = @@PIE2IID ;
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 166 total points
ID: 39199788
The reason for that where clause is trying to get rid of the SS.mmm part of the date, which is the seconds and milliseconds. You can do that like this:

SELECT   tblInputs.value
FROM     tblInputs INNER JOIN
         tblTimeStamp ON tblInputs.timeStampKey = tblTimeStamp.timeStampKey
where
		DATEDIFF(SECOND,@date,tblTimeStamp) = 0
		AND tblInputs.PIE2_I_ID = @PIE2IID;

Open in new window


As an observation, in any of the cases the SQL engine will not be able to use any index on the tblTimeStamp column because is wrapped in function(s). However if you don't have to many dates for one timeStampKey and PIE2_I_ID combination you should be fine.
0
 

Author Comment

by:rwallacej
ID: 39199809
Thanks for helps, I'll look more in depth tomor.

There is only one date per timestampKey (I.e. each day has its own key, so for 1 year there are 365 rows in tblTimeStamp) timestampkey is UID in this tblTimeStamp
0
Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

 
LVL 27

Expert Comment

by:Zberteoc
ID: 39201389
Actually I just realized that the query should look at the minutes difference and not seconds :

SELECT   tblInputs.value
FROM     tblInputs INNER JOIN
         tblTimeStamp ON tblInputs.timeStampKey = tblTimeStamp.timeStampKey
where
		DATEDIFF(MINUTE,@date,tblTimeStamp) = 0
		AND tblInputs.PIE2_I_ID = @PIE2IID;

Open in new window


what you want is to ignore the part after minutes so the date time value should be the same to the minute. That is what the original code does.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 167 total points
ID: 39201849
If any function or computation is done to a table column, SQL cannot do any keyed index lookup on that column.  Therefore, it's best to avoid using functions/computations on the table column if reasonably possible.

For date/time comparisons, it's best to use ">=" and "<" rather than = or between.

Given all of that, and adding aliases to make the query easier to read/maintain, the final query should be:


SELECT   i.value
FROM     tblInputs i INNER JOIN
         tblTimeStamp ts ON i.timeStampKey = ts.timeStampKey
                                                 --strip seconds and subseconds from @date
WHERE ts.timeStamp >= DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @date), 0)
                                             --strip seconds and subseconds from @date and add 1 minute
      AND ts.timeStamp < DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @date) + 1, 0)
      AND ts.PIE2_O_ID = @PIE2IID ;
0
 

Author Closing Comment

by:rwallacej
ID: 39307864
split points, thanks all for help
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question