How can I exclude those record

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)

How can I exclude those record

Author

Topic

SQLHunter
Starting Member

1 Post

Posted - 2002-06-07 : 10:33:19

This is my first submission.

I am having problem with writing a query which will exclude some record depending upon some other record. Let me explaine what I need.
I have two tables with the following data. First table contains following data
Table #1
equip_id Begin End prod_value
223 2002-03-02 09:01:00.000 2002-03-02 09:05:00.000 15
223 2002-03-02 09:05:00.000 2002-03-10 10:05:00.000 9
223 2002-03-10 10:05:00.000 2002-03-10 10:25:00.000 7
223 2002-03-10 10:25:00.000 2002-03-13 21:30:00.000 19
223 2002-03-13 21:30:00.000 2002-03-13 23:02:00.000 17
223 2002-03-13 23:02:00.000 2002-03-18 07:50:00.000 11
223 2002-03-18 07:50:00.000 2002-03-18 07:55:00.000 10
223 2002-03-18 07:55:00.000 2002-03-21 19:12:00.000 4
223 2002-03-21 19:12:00.000 2002-03-21 22:25:00.000 3
223 2002-03-21 22:25:00.000 2002-03-22 13:05:00.000 19
223 2002-03-22 13:05:00.000 2002-03-22 14:45:00.000 20
223 2002-03-22 14:45:00.000 2002-03-25 04:50:00.000 18
223 2002-03-25 04:50:00.000 2002-03-25 05:10:00.000 19
and second table have following records :
Table #2
equip_id Begin End intrpt_value
313 2002-03-10 09:00:00.000 2002-03-10 10:25:00.000 1
313 2002-03-10 10:25:00.000 2002-03-21 19:05:00.000 0
313 2002-03-21 19:05:00.000 2002-03-21 19:05:00.000 1
Now I want a sql to eliminate all those record from table #1 which are in the time ranges of table #2 with intrpt_value=1 . I realy want avoid UNION. Many thanks in advance for help.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-07 : 10:48:21

First, I appreciate the effort, but in the future, its much easier for ppl on message boards to provide free help if instead of posting your own psudo table definition/population code, you post actual ddl and dml. It makes it much easier if I can cut and paste a create table... statement and a series of insert into... statements, rather than having to retype all this crap . . .

Second, using reserved words such as BEGIN and END as column names if very poor design.

Third, while I agree that a UNION is not necessary for this solution, it should be noted that there is nothing inherently evil about a UNION such that it should be avoided at all costs . . .

I think this may work, but I haven't test it.


delete [table #1]
from [table #1] a
where exists (
    select 1
    from [table #2] b
    where a.[begin] >= b.[begin] and
          a.[end] <= b.[end] and
          b.intrpt_value = 1 )

<O>

Edited by - Page47 on 06/07/2002 10:49:10

Edited by - Page47 on 06/07/2002 10:51:58

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-07 : 12:24:01

Hmmm, page.. Not sure if he wants to delete them or just select the ones not appearing between those dates.

Simple mod to what page47 wrote

select a.*
from [table #1] a
where not exists (
select 1
from [table #2] b
where a.[begin] >= b.[begin] and
a.[end] <= b.[end] and
b.intrpt_value = 1 )

(bueatiful bit o code there by the way page)

-----
edit... Begin and End really should be changed, and hunter just as a note, it helps us so much more when you post the create table statements in there entire (use script generators to make the create statment if need be). Saves us time.

Last bit... I'm unsure of how a union would possibly used to do this query

Edited by - M.E. on 06/07/2002 12:26:06

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources