469,600 Members | 2,211 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,600 developers. It's quick & easy.

Minimum values

11
hi,

I'm making a query that has quite some joins and there is no other way in linking the tables to get to my preferred value.

Because of this I get a resultset that has x-amount of values.
From this recordset I only need the one(s) where the date is lowest. So I thought using min(date) I'd get there. Or even using top(1). The problem now is, it is possible that the resultset contains more than one record with the same date. When this date is the same I do need both or more of these records because that means they have another action.

Anyone has a clue how I can solve this without splitting up my query. Like I was planning to do. get my joins to get that lowest date. And then link that date to the other tables to get the resultset that returns all those lines.

This might sound complicated :p and it kind of is.

This is my query that returns multiple lines thx in advance

select o.Order_id, v.Voertuig, o.DatumVoertuigIn ,op.Planningscode_id ,p.Omschrijving, v.Mileage
from [Order] o
inner join Order_Planningscode op on convert(varchar(50),op.Order_id) = convert(varchar(50),o.Order_id)
inner join Vosp v on v.PlanningscodeID = op.Planningscode_id and v.VoertuigID = o.Voertuig_id
inner join Planningscode p on p.Planningscode_id = v.PlanningscodeID
inner join Planning pl on o.Order_id = pl.Order_id
where convert(varchar(10), o.DatumVoertuigIn, 20) + ' ' + '00:00' < '2008-03-07 00:00'
and v.Datum > '2008-05-09'
--group by o.Order_id, v.Voertuig, o.DatumVoertuigIn, op.Planningscode_id,p.Omschrijving, v.Mileage
Mar 20 '08 #1
1 1281
deepuv04
227 Expert 100+
hi,

I'm making a query that has quite some joins and there is no other way in linking the tables to get to my preferred value.

Because of this I get a resultset that has x-amount of values.
From this recordset I only need the one(s) where the date is lowest. So I thought using min(date) I'd get there. Or even using top(1). The problem now is, it is possible that the resultset contains more than one record with the same date. When this date is the same I do need both or more of these records because that means they have another action.

Anyone has a clue how I can solve this without splitting up my query. Like I was planning to do. get my joins to get that lowest date. And then link that date to the other tables to get the resultset that returns all those lines.

This might sound complicated :p and it kind of is.

This is my query that returns multiple lines thx in advance

select o.Order_id, v.Voertuig, o.DatumVoertuigIn ,op.Planningscode_id ,p.Omschrijving, v.Mileage
from [Order] o
inner join Order_Planningscode op on convert(varchar(50),op.Order_id) = convert(varchar(50),o.Order_id)
inner join Vosp v on v.PlanningscodeID = op.Planningscode_id and v.VoertuigID = o.Voertuig_id
inner join Planningscode p on p.Planningscode_id = v.PlanningscodeID
inner join Planning pl on o.Order_id = pl.Order_id
where convert(varchar(10), o.DatumVoertuigIn, 20) + ' ' + '00:00' < '2008-03-07 00:00'
and v.Datum > '2008-05-09'
--group by o.Order_id, v.Voertuig, o.DatumVoertuigIn, op.Planningscode_id,p.Omschrijving, v.Mileage
Hi,
In the above query you are using two dates in the where clause, which date must be the least date.( [o.DatumVoertuigIn] or v.Datum ).

Thanks
Mar 20 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Lobang Trader | last post: by
34 posts views Thread by Sona | last post: by
3 posts views Thread by Sona | last post: by
3 posts views Thread by Frank Rizzo | last post: by
13 posts views Thread by Ioannis Vranos | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.