Simple Query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Simple Query question

Author

Topic

netcop
Starting Member

12 Posts

Posted - 2002-07-24 : 12:35:20

Hi there!
How I get the lowest time from my temp-table grouped by L where U is max.?
That means tell the time 11:12 from the dataset where L=3.


L U from      to          start  end
1 1  Lakeside   StoneRiver   10:54   10:55
1 2  StoneRiver BigMountain  11:04   11:05
2 1  Lakeside   DownTown     10:54   10:56
2 2  DownTown   BigMountain  11:03   11:05
3 1  Lakeside   Parklane     10:54   10:57
3 2  Parklane   StoneRiver   11:02   11:05
3 3  StoneRiver BigMountain  11:05   11:12

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2002-07-24 : 15:32:09

What do you mean "lowest time" ?

Is it lowest start?
Is it lowest end?
Is it shortest gap between start and end per leg/overall?

11:12 is the latest time in that particular grouping

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-07-24 : 16:07:56

I'm not sure this is exactly what you mean but try the following and let me know if I have missed something.

Select a.L, a.End_time
FROM buslines a,
(Select L, Max(U) U
FROM buslines
GROUP BY L) b
WHERE a.L = b.L
AND a.U = b.U
ORDER BY a.L

Jeremy

netcop
Starting Member

12 Posts

Posted - 2002-07-25 : 02:29:11

The result of the query should give the the
block with the lowest start time.

In the example above all blocks are possible (10:54)

The next restriction is, that the block with the lowest
end time must be returned where U is max. That means in
the example that the blocks with end-time 11:05 (marked bold) should
be returned.


1 1  Lakeside   StoneRiver   10:54   10:55
1 2  StoneRiver BigMountain  11:04   11:05
2 1  Lakeside   DownTown     10:54   10:56
2 2  DownTown   BigMountain  11:03   11:05
3 1  Lakeside   Parklane     10:54   10:57
3 2  Parklane   StoneRiver   11:02   11:05
3 3  StoneRiver BigMountain  11:05   11:12

Thanks in advance
Michael

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-07-25 : 04:36:39

if i got it straight and right...

u are looking for the lowest duration between start and end time right...

BOL

DATEDIFF
Returns the number of date and time boundaries crossed between two specified dates.

Syntax
DATEDIFF ( datepart , startdate , enddate )

Arguments
datepart

Is the parameter that specifies on which part of the date to calculate the difference. The table lists dateparts and abbreviations recognized by Microsoft® SQL Server™.

Datepart Abbreviations
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms

did i get it....?

======================================
Ask to your self before u ask someone

netcop
Starting Member

12 Posts

Posted - 2002-07-25 : 04:52:17

My question was not regarding the datediff function. The question is the right grouping.

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-07-25 : 05:09:25

May be this is what you want...

Select a.L, a.End
FROM buslines a
WHERE a.start = (select min(start) from buslines b where b.L=a.L)
or (
a.End = (select MAX(end) from buslines c where c.L = a.L)
AND a.U = (select MAX(U) from buslines d where d.L = a.L)
)
ORDER BY a.L

Ramesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources