sql - Efficient way of storing date ranges -
i need store simple data - suppose have products codes primary key, properties , validity ranges. data this:
products code value begin_date end_date 10905 13 2005-01-01 2016-12-31 10905 11 2016-01-01 null
those ranges not overlapping, on every date have list of unique products , properties. ease use of i've created function:
create function dbo.f_products ( @date date ) returns table return ( select dbo.products p @date >= p.begin_date , @date <= p.end_date )
this how i'm going use it:
select * <some table product codes> t left join dbo.f_products(@date) p on p.code = t.product_code
this fine, how can let optimizer know rows unique have better execution plan?
i did googling, , found couple of nice articles ddl prevents storing overlapping ranges in table:
- self-maintaining, contiguous effective dates in temporal tables
- storing intervals of time no overlaps
but if try constraint see optimizer cannot understand resulting recordset return unique codes.
what i'd have approach gives me same performance if stored products list on date , selected date = @date
.
i know rdmbs (like postgresql) have special data types (range types
). sql server doesn't have this.
am missing or there're no way in sql server?
a solution without gaps might this:
declare @tbl table(id int identity,[start_date] date); insert @tbl values({d'2016-10-01'}),({d'2016-09-01'}),({d'2016-08-01'}),({d'2016-07-01'}),({d'2016-06-01'}); select * @tbl; declare @datefilter date={d'2016-08-13'}; select top 1 * @tbl [start_date]<=@datefilter order [start_date] desc
important: sure there (unique) index on start_date
update: different products
declare @tbl table(id int identity,productid int,[start_date] date); insert @tbl values --product 1 (1,{d'2016-10-01'}),(1,{d'2016-09-01'}),(1,{d'2016-08-01'}),(1,{d'2016-07-01'}),(1,{d'2016-06-01'}) --product 1 ,(2,{d'2016-10-17'}),(2,{d'2016-09-16'}),(2,{d'2016-08-15'}),(2,{d'2016-07-10'}),(2,{d'2016-06-11'}); declare @datefilter date={d'2016-08-13'}; partitionedcount ( select row_number() over(partition productid order [start_date] desc) nr ,* @tbl [start_date]<=@datefilter ) select * partitionedcount nr=1
Comments
Post a Comment