Hard to find this date in query

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 2005 Forums

Transact-SQL (2005)

Hard to find this date in query

Author

Topic

djorre
Yak Posting Veteran

94 Posts

Posted - 2011-02-25 : 11:16:44

Hello,

I am struggling to write a query that extracts the latest date where no transfers where running. My table looks like this:


TransferID StartDate EndDate
1          12:00     13:00
2          12:45     13:30
3          13:35     14:00
4          13:40     13:45
5          13:40     null
6          13:50     null

I need to find the latest date where no Transfers were running.
So I need the query to reply 13:35.

Please how can I do this? Somebody can help me?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-02-25 : 11:47:35

select max(TransferID)
from MyTable
where StartDate <> 'current'

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

djorre
Yak Posting Veteran

94 Posts

Posted - 2011-02-25 : 12:01:38

quote:
Originally posted by Bustaz Kool

select max(TransferID)
from MyTable
where StartDate <> 'current'

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

I am sorry if I was unclear but it is a bit harder then that

I added some more rows maybe that makes it more clear.

TransferID StartDate EndDate
1          12:00     13:00
2          12:45     13:30
3          13:35     14:00
4          13:40     13:45
5          13:40     null
6          13:50     null

You cannot say 13:40 was the latest because transferid 3 was running during this time.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-02-25 : 12:15:10

So what is the expected outcome and why?

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-02-25 : 12:18:23

select StartDate
from MyTable
where EndDate = (select max(EndDate) from MyTable)

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

djorre
Yak Posting Veteran

94 Posts

Posted - 2011-02-25 : 12:54:43

quote:
Originally posted by Bustaz Kool

So what is the expected outcome and why?

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

The expected outcome is 13:35 because this is the latest time were no transferid was running.

djorre
Yak Posting Veteran

94 Posts

Posted - 2011-02-25 : 12:58:35

quote:
Originally posted by Bustaz Kool

select StartDate
from MyTable
where EndDate = (select max(EndDate) from MyTable)

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Maybe I need to add even more rows to get this clear:

TransferID StartDate EndDate
1          12:00     13:00
2          12:45     13:30
3          13:35     14:00
4          13:40     13:45
5          13:42     13:47
6          13:43     null

This has to have 13:35 as outcome because that is the latest starttime while no other transfer was running.
13:43 is incorrect because transferid 5 was running on that time
13:42 is incorrect because transferid 4 was running on that time
...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-02-25 : 16:04:22

So you want to find the lastest [StartDate] that doesn't fall between any other row's [startDate] and [EndDate]?
What is the datatype of [StartDate] and [EndDate] ?

Be One with the Optimizer
TG

djorre
Yak Posting Veteran

94 Posts

Posted - 2011-02-26 : 03:45:49

quote:
Originally posted by TG

So you want to find the lastest [StartDate] that doesn't fall between any other row's [startDate] and [EndDate]?
What is the datatype of [StartDate] and [EndDate] ?

Be One with the Optimizer
TG

Yes that is completely correct.

The datatypes are datetime.

I thought of someting like

Select max(StartDate) from tbl_Transfers
Where (Select count(*) from tbl_Transfers where '@startdate' between startdate and enddate)=1
and enddate is not null

But as you see that wont work.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-26 : 04:27:15

this ?


select max(startdate)
from  tbl_Transfers t
where not exists
      (
          select *
          from   tbl_Transfers x
          where  x.startdate <= t.startdate
          and    x.enddate   >= t.enddate
          and    x.transferid <> t.transferid
      )

KH
[spoiler]Time is always against us[/spoiler]

djorre
Yak Posting Veteran

94 Posts

Posted - 2011-02-26 : 04:51:00

quote:

Originally posted by khtan

this ?


select max(startdate)
from  tbl_Transfers t
where not exists
      (
          select *
          from   tbl_Transfers x
          where  x.startdate <= t.startdate
          and    x.enddate   >= t.enddate
          and    x.transferid <> t.transferid
      )

KH
[spoiler]Time is always against us[/spoiler]

That looks great thanks!

I will try


select max(startdate)
from  tbl_Transfers t
where not exists
      (
          select *
          from   tbl_Transfers x
          where  t.startdate between x.startdate and x.enddate
          and    x.transferid <> t.transferid
      )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources