|
|
Posted by alanchinese on 10/06/66 11:26
i've got a table, named DataCollection, with two columns, 1) DataName,
2) StartingDate
sample data:
DataName | StartingDate
row 1: A | 05/05/2001
row 2: A | 05/05/2002
row 3: A | 05/05/2003
row 4: B | 05/05/2001
row 5: B | 05/05/2004
row 6: C | 05/05/2003
row 7: C | 05/05/2005
of course it has "EndingDate" column, but it could be calculated from
the next StartingDate.
provided by a "GivenDate", i want to select rows on each DataName, with
and only with its last "StartingDate" (that means the current effective
data)
for example, "GivenDate" is 01/01/2004
result set:
row 1: A | 05/05/2003
row 2: B | 05/05/2001
row 3: C | 05/05/2003
for example, "GivenDate" is 01/01/2002
result set:
row 1: A | 05/05/2001
row 4: B | 05/05/2001
how do solve this problem?
i could do
"select * from DataCollection
where StartingDate <= GivenDate
order by StartingDate"
and loop through each row and remove the "earlier" StartingDate rows...
Navigation:
[Reply to this message]
|