Hello,
I need to express the following query using the Criteria API, but I'm not
sure how to construct it:
SELECT *
FROM MyTable a
INNER JOIN
(
SELECT MyTable.IdForeignKey, max(Date) AS Date
FROM MyTable
Where Date > @SomeDate
GROUP BY IdForeignKey
) MostRecentRecords
ON
a.IdForeignKey = MostRecentRecords.IdForeignKey AND Skews.Date=
MostRecentRecords.Date
Basically what I'm trying to achieve is the following:
I have a table that stores a bunch of historical readings Foo for a
particular set of records Bar. Presently, for each Bar, there are numerous
dated Foo records, and currently I'm using a bit flag to determine which Foo
is active for each Bar. However, sometimes i need to run my algorithm for a
date in the past, and it's a manual process to go and find for each Bar
which Foo would have been active at that date, and set their flag, and clear
the flag on the currently active ones - and then still remember to reverse
those changes when I want to run for today again.
So, what I'm trying to achieve is a query that says, for a given date, what
is the most recent record that was put in the table for each of the Bar's as
at that date?
A watered down version of the domain classes is as follows:
class Foo
{
public int IdFoo { get; set; }
public Bar Bar { get; set; }
public DateTime Date { get; set; }
public int Value1 { get; set; }
public int Value2 { get; set; }
// etc
}
class Bar
{
public int IdBar { get; set; }
}
Thanks
Terence
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
