473,396 Members | 1,773 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

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 1341
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

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

Similar topics

4
by: Lobang Trader | last post by:
Hi all, I am trying to create a username and a password class. I would like to know what are the RECOMMENDED minimum and maximum length for both fields? These fields will be something like...
0
by: Kasp | last post by:
Hi there, I am trying to make an OLAP cube on a table having two columns (datetime, Number_of_times_an_event_occured). My dimension is time and I want to measure the Min and Max times an event...
4
by: Porthos | last post by:
Hi All, I'm trying to find the minimum value of a set of data (see below). I want to compare the lengths of these attribute values and display the lowest one. This would be simple if I could...
2
by: Hennie de Nooijer | last post by:
Because of an error in google or underlying site i can reply on my own issue. Therefore i copied the former entered message in this message....
3
by: RobG | last post by:
I would like a query that will tell me the minimum non-zero value in a row. Say I have a table with a column called recordID that contains unique record IDs, and have a set of values named V1,...
34
by: Sona | last post by:
I need to find a minimum of three float values.. what would be the most efficient way of doing this? Can someone please share a #define macro or something with me for doing this? Thanks Sona
3
by: Sona | last post by:
I need to find a minimum of three float values.. what would be the most efficient way of doing this? Can someone please share some code with me for doing this? Thanks Sona
2
by: Pugi! | last post by:
hi, I am using this code for checking wether a value (form input) is an integer and wether it is smaller than a given maximum and greater then a given minimum value: function...
3
by: Frank Rizzo | last post by:
I am trying to do some monitoring of some PerfMon counters and have a question. Does PerfMon figure out the Minimum, Maximum, Average values for each counter? Or are those values part of the...
13
by: Ioannis Vranos | last post by:
Is there any mentioning in the standard of the number of bits of the various built in types, apart from char/signed char/unsigned char types? Or only about the minimum value ranges of them?
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.