SELECT a specific year - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT a specific year

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2013-05-20 : 08:00:22

I have this part of the script

INNER JOIN #TEMP1 T ON M.CLIENT_NUMBER = T.CLIENT_NUMBER
AND (M.POLICY_DATE_TIME =
(SELECT MAX (M2.POLICY_DATE_TIME)
FROM MPL_EXPOSURE M2
WHERE M2.CLIENT_NUMBER = M.CLIENT_NUMBER))

Which works perfect it selects the latest record with the SELECT MAX COMMAND Now I want to make sure that the MAX M2.POLICY_DATE_TIME selected is for records that have a certain year say 2012..

Hope this makes sense

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-20 : 08:06:01

DECLARE @year INT = 2012
.
.
INNER JOIN #TEMP1 T ON M.CLIENT_NUMBER = T.CLIENT_NUMBER
AND (M.POLICY_DATE_TIME =
(SELECT MAX (M2.POLICY_DATE_TIME)
FROM MPL_EXPOSURE M2
WHERE M2.CLIENT_NUMBER = M.CLIENT_NUMBER
AND YEAR(M2.POLICY_DATE_TIME) = @year)) -- otherwise hardcode with 2012

--
Chandu

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-20 : 08:14:32

To add to what Chandu suggested, if you are hardcoding the year, use the following which is logically equivalent, but would allow the use of any indexes you may have on M2.POLICY_DATE_TIME.

....
AND M2.POLICY_DATE_TIME >= '20120101' AND M2.POLICY_DATE_TIME < '20130101'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-20 : 08:27:51

even otherwise you could follow the same approach


DECLARE @year INT = 2012

.
.
INNER JOIN #TEMP1 T ON M.CLIENT_NUMBER = T.CLIENT_NUMBER 
AND (M.POLICY_DATE_TIME = 
(SELECT MAX (M2.POLICY_DATE_TIME) 
FROM MPL_EXPOSURE M2 
WHERE M2.CLIENT_NUMBER = M.CLIENT_NUMBER 
AND M2.POLICY_DATE_TIME > = DATEADD(yy,@year-1900,0)
AND M2.POLICY_DATE_TIME < DATEADD(yy,@year-1899,0)))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-20 : 08:28:57

see

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

divan
Posting Yak Master

153 Posts

Posted - 2013-05-20 : 11:29:57

Thanks... It worked like a charm

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-21 : 00:51:14

quote:
Originally posted by divan

Thanks... It worked like a charm

welcome

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-21 : 04:47:56

quote:
Originally posted by divan

Thanks... It worked like a charm

which one you used?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources