日期:2014-05-17 浏览次数:20660 次
CREATE TABLE test (id INT ,begindate DATE,enddate date)
INSERT INTO test
SELECT 1,'2012-11-21','2012-11-22'
UNION ALL
SELECT 1,'2012-11-23','2012-12-22'
UNION ALL
SELECT 1,'2012-12-23','2999-12-31'
UNION ALL
SELECT 2,'2012-11-21','2999-12-31'
SELECT * FROM test
/*
id begindate enddate
----------- ---------- ----------
1 2012-11-21 2012-11-22
1 2012-11-23 2012-12-22
1 2012-12-23 2999-12-31
2 2012-11-21 2999-12-31
*/
DECLARE @date DATETIME
SET @date='2012-11-30'
SELECT * FROM test WHERE @date BETWEEN begindate AND enddate
/*
id begindate enddate
----------- ---------- ----------
1 2012-11-23 2012-12-22
2 2012-11-21 2999-12-31
*/
if OBJECT_ID('单价表') is not null
drop table 单价表
go
create table 单价表
(
单号 varchar(10),
建立日期 datetime,
客户 varchar(10)
)
go
insert 单价表
select '10001','2012-10-06 18:24:32','U001' union all
select '10002','2012-10-14 09:32:53','U001'
go
if OBJECT_ID('单价明细表')is not null
drop table 单价明细表
go
create table 单价明细表
(
单号 varchar(10),
品名 varchar(10),
单价 numeric(8,2),
生效日 datetime,
失效日 datetime
)
go
insert 单价明细表
select '10001','test01',18.50,'2012-09-30','2012-10-07' union all
select '10001','test02',19.50,'2012-10-30','2012-11-07' union all
select '10002','test01',25.50,'2012-11-08','2012-11-30' union all
select '10002','test02',18.50,'2012-12-01','2012-12-05' union all
select '10001','test01',24.50,'2012-12-06','2012-12-31'
go
select
a.*,
b.单价,
b.生效日,
b.失效日
from
单价表 a
inner join
单价明细表 b
on
a.单号=b.单号
where
a.建立日期 between b.生效日 and b.失效日
/*
单号 建立日期 客户 单价 生效日 失效日
10001 2012-10-06 18:24:32.000 U001 18.50 2012-09-30 00:00:00.000 2012-10-07 00:00:00.000
*/
我觉得没什么问题