CODEWITHSUNDEEP
sql
Eric H.
Eric H.

Reputation: 2574

SQL Select excluding some ranges

I have a set of stock data records.

I also have a set of dates for which, even though I might have data, I cannot trade stocks.

Here's the sample DDL for the setup:

create table #stock_data
(
   symbol varchar (10) NOT NULL,
   asof datetime NOT NULL,
   price float NOT NULL
)
go

insert into #stock_data values ('IBM', '7/1/09', 100)
insert into #stock_data values ('IBM', '7/2/09', 100)
insert into #stock_data values ('IBM', '7/3/09', 100)
insert into #stock_data values ('IBM', '7/4/09', 100)
insert into #stock_data values ('IBM', '7/5/09', 100)
insert into #stock_data values ('IBM', '7/6/09', 100)
insert into #stock_data values ('IBM', '7/7/09', 100)
insert into #stock_data values ('IBM', '7/8/09', 100)
insert into #stock_data values ('IBM', '7/9/09', 100)

insert into #stock_data values ('MSFT', '7/1/09', 50)
insert into #stock_data values ('MSFT', '7/2/09', 50)
insert into #stock_data values ('MSFT', '7/3/09', 50)
insert into #stock_data values ('MSFT', '7/4/09', 50)
insert into #stock_data values ('MSFT', '7/5/09', 50)
insert into #stock_data values ('MSFT', '7/6/09', 50)
insert into #stock_data values ('MSFT', '7/7/09', 50)
insert into #stock_data values ('MSFT', '7/8/09', 50)
insert into #stock_data values ('MSFT', '7/9/09', 50)
go

create table #exclude_ranges
(
    symbol varchar (10) NOT NULL,
    asof_start datetime NOT NULL,
    asof_end datetime NOT NULL
)
go


insert into #exclude_ranges values ('IBM', '7/2/09', '7/2/09')
insert into #exclude_ranges values ('IBM', '7/6/09', '7/8/09')
insert into #exclude_ranges values ('MSFT', '7/1/09', '7/8/09')

go

And here's the SELECT I want. I know there's got to be some clever way to do this, but I can't quite figure it out.....

select * from #stock_data
join ????
where ????

to return

('IBM', '7/1/09', 100)
('IBM', '7/3/09', 100)
('IBM', '7/4/09', 100)
('IBM', '7/5/09', 100)
('IBM', '7/9/09', 100)
('MSFT', '7/9/09', 50)

So what magic goes into the ???? blocks?

Upvotes: 1

Views: 449

Answers (2)

CJM
CJM

Reputation: 12016

Try this...

select * from stock_data s
left join exclude_ranges e
    on e.symbol = s.symbol
    and s.asof between e.asof_start and e.asof_end
Where e.symbol is null

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416049

SELECT * 
FROM #stock_data sd
LEFT JOIN #exclude_ranges er
    ON sd.symbol=er.symbol and sd.asof BETWEEN er.asof_start AND er.asof_end
WHERE er.symbol IS NULL

Upvotes: 5

Related Questions