SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SELECT a specific year
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

divan
Posting Yak Master

153 Posts

Posted - 05/20/2013 :  08:00:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 05/20/2013 :  08:06:01  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 05/20/2013 :  08:14:32  Show Profile  Reply with Quote
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'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/20/2013 :  08:27:51  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/20/2013 :  08:28:57  Show Profile  Reply with Quote
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
Go to Top of Page

divan
Posting Yak Master

153 Posts

Posted - 05/20/2013 :  11:29:57  Show Profile  Reply with Quote
Thanks... It worked like a charm
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 05/21/2013 :  00:51:14  Show Profile  Reply with Quote
quote:
Originally posted by divan

Thanks... It worked like a charm


welcome

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/21/2013 :  04:47:56  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000