This should do it:
SELECT test.* ;
FROM Test ;
INNER JOIN ;
(select cjobtype, MAX(dActive) as dactive FROM Test WHERE
dactive <= DATE() GROUP BY cjobtype) as subtest ;
ON test.cjobtype = subtest.cjobtype AND test.dactive =
subtest.dactive
Frank.
Frank Cazabon
Samaan Systems Ltd. - Developing Solutions
www.SamaanSystems.com
Referrals are important to us.
If you know of anyone who would benefit from our services, please contact me.
We would appreciate the opportunity to work with them.
On 23/12/2010 04:02 PM, MB Software Solutions, LLC wrote:
> CREATE CURSOR Test (iid i, cjobtype c(1), nrate1 n(5,2), nrate2 n(5,2),
> nrate3 n(5,2), dactive d)
> INSERT INTO Test VALUES (1, "O", 25.00, 25.00, 25.00, DATE(2009,1,1))
> INSERT INTO Test VALUES (2, "C", 35.00, 35.00, 35.00, DATE(2009,1,1))
> INSERT INTO Test VALUES (3, "R", 25.00, 25.00, 25.00, DATE(2009,1,1))
> INSERT INTO Test VALUES (4, "O", 25.00, 25.00, 25.00, DATE(2010,1,1))
> INSERT INTO Test VALUES (5, "O", 45.00, 45.00, 45.00, DATE(2012,1,1))
>
>
> I want to get the latest "Rates" record for each cJobType identifier.
> So in this example dataset, I want to return iid's 2 ("C"), 3 ("R"), and
> 4 ("O"). IID 1 isn't selected because IID 4 is more "active" -- meaning
> it's before today's date. IID 5 isn't selected because it's a future
> rate setting.
>
> What's the SQL for this? Thanks! And Merry Christmas to those
> celebrating it...happy holidays.
>
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
