Solved

Comparing date times start time and end time using between

Posted on 2010-09-09
6
347 Views
Last Modified: 2012-05-10
I cant seem to get the right combination to generate the proper results.  I need to compare the start date and end date to determine if rows are overlaping.  Example

RowID        StartTime                           EndTime
1                9/11/2010  08:00:00         9/11/2010  09:00:00
2                9/11/2010  07:30:00         9/11/2010  08:30:00
3                9/11/2010  08:30:00         9/11/2010  09:30:00
4                9/11/2010  09:30:00         9/11/2010  10:30:00

I need to return row 1,2 and 3 but not row 4 since the start time is the same as the end time of the previous appointment. Any help is greatly appreciated.
0
Comment
Question by:clintnash
6 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 33638162
Try this:
select RowID,
       StartTime,
       EndTime
from   MyTable
where  StartTime NOT IN (select EndTime from MyTable)

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33638280
what about this:
select t.*

 from yourtable t

 where exists( select null from yourtable o where o.starttime < t.endtime and o.endtime > t.starttime )

Open in new window

0
 
LVL 3

Accepted Solution

by:
mehdi_javan earned 500 total points
ID: 33638466
I think you need this:

Select t1.* 
from YourTable t1
where exists (select null from YourTable t2
              where (t1.StartTime > t2.StartTime and t1.StartTime < t2.EndTime)
              or (t1.EndTime > t2.StartTime and t1.EndTime < t2.EndTime)
              or (t1.StartTime < t2.StartTime and t1.EndTime > t2.EndTime)

Open in new window

0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 1

Author Closing Comment

by:clintnash
ID: 33638666
Short of a missing ) at the end, this query returns the correct results. Thanks to each of your for your help this morning.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33638677
did you try mine? did it not work?
0
 
LVL 1

Author Comment

by:clintnash
ID: 33639633
Angel, Actually I tried them all, the first returned two of the three rows, yours returned all four rows.  Thank you...
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
format nvarchar field as mm/dd/yyyy 4 61
Problem with SqlConnection 4 160
Sql Query join multiple table and distinct records 7 29
CREATE DATABASE ENCRYPTION KEY 1 58
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now