February 22, 2004 at 1:42 pm
How can I do this in one query
I've got a table like this
ID RESOURCEID DATE QUANTITY
The Id is unique (but just a counter not of interest here), there may be one resourceid for each date and an asscoiated quantity.
I want to select all resourceid (distinct) and I just want just the resourceid and the date and quantity associated
with that post that is less than today but nearast in time.
To examplifY
if some records look like like this
ID RESOURCEID DATE QUANTITY
1 1 2003-12-01 1.3
2 1 2004-01-25 1.4
3 2 2002-01-01 1.7
4 2 2003-07-27 2.8
I want my select statement to return only the two following post
2 1 2004-01-25 1.4
4 2 2003-07-27 2.8
That is one post for each resource and the post should be the one were the date is less than today but closest in timeI want to do this without using stored procedure or view, since I'm not allowed to create objects in the database.
If someone could show me a way of doing this with somekind of self joins or temporary tables I'd really appreciate it.
(The question will be executed in a vb.net app but using ADO since the applications SDK just support ADO connections- of course I could do ado loops but doesn't seem like an elegant solution)
Thanks in advance
Kim
February 22, 2004 at 7:20 pm
Try this:
select t2.id, t2.resourceid, t2.date, t2.quantity
from
(
select resourceid, max(date)
from YourTable
where date < cast(floor(cast(GetDate as float)) as datetime)
group by resourceid
) t1
inner join YourTable t2 on (t1.resourceid = t2.resourceid and t1.Date = t2.date)
February 23, 2004 at 6:47 am
Here's another possibility:
select res3.id, res3.resourceid, res3.workdate, res3.quantity
from tblresource res3 inner join
(select id from tblresource res1
inner join (select resourceid, max(workdate) as 'workdate'
from tblresource
where workdate < GetDate()
group by resourceid) res2
on res1.resourceid = res2.resourceid
and res1.workdate = res2.workdate) res4
on res3.id = res4.id
Darcy's is probably more efficient since it uses fewer joins though.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply