Solved

SQL - check on the stored procedure with extended date query

Posted on 2013-05-27
6
359 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
6 Comments
 
LVL 40

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 26

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 26

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:
ScottPletcher 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now