请教如何计算租赁合同在某一时间段内有效天数

VAT

向各位ACCESS高手求教：
       个人正在做一个租赁管理系统，其中的租赁合同表以合同号为主键，还包括租赁开始日期和租赁终止日期。
我现在的问题是：想计算合同在特定的时间段内的有效天数，如合同表有以下三条记录
合同号   租赁开始日期      租赁终止日期
1              2011-12-1                2013-11-30
2              2012-5-1                  2012-5-31
3              2012-7-1                 2013-6-30

如果我想得出以上三份合同在2012年度的有效执行天数各是多少
答案应该为：
合同号   12年有效天数
1              12年全年的天数
2              12年5月份全月天数
3              12年下半年的天数

请教如何在ACCESS中用SQL表达？谢谢！

VAT

http://hi.baidu.com/wwwzzg/item/a0f22014140391f9756a844e
链接是我搜到的相似例子，请教如何在ACCESS中实现。

zhangjimfu

1. SELECT 表1.ID, IIf([ENDDate]>#12/31/2012#,#12/31/2012#,[ENDDate])-IIf([STARTDate]<#1/1/2012#,#1/1/2012#,[STARTDate]) AS 表达式1
   
2. FROM 表1;

复制代码

VAT

版主,您的公式是否没有考虑到之前签订的合同:
像租赁期为2010-1-1至2010-12-31的合同,按您的合同将会计算为:ENDDate:2010-12-31减去STARTDate:2012-1-1,结果是负数!

zhangjimfu

VAT 发表于 2012-10-21 22:32
版主,您的公式是否没有考虑到之前签订的合同:
像租赁期为2010-1-1至2010-12-31的合同,按您的合同将会计算为 ...

是的

SELECT 表1.ID, IIf([ENDDate]<#1/1/2012# And [STARTDate]<#1/1/2012#,0,IIf([ENDDate]>#12/31/2012# And [STARTDate]>#12/31/2012#,0,IIf([ENDDate]>#12/31/2012#,#12/31/2012#,[ENDDate])-IIf([STARTDate]<#1/1/2012#,#1/1/2012#,[STARTDate]))) AS 表达式1
FROM 表1;

amazeyeli

[code=sql]USE tempdb
IF OBJECT_ID('TEST') IS NOT NULL
  DROP TABLE TEST;      --> 如果表TEST不为空,删除表
GO  
CREATE TABLE TEST       --> 创建表,字段及类型如下
(
  id           int,
  stardate     date,
  enddate      date,
);
GO
INSERT INTO TEST VALUES --> 向表中插入数据
(1,'2011-12-1','2013-11-30'),
(2,'2012-5-1','2012-5-31'),
(3,'2012-7-1','2013-6-30');
GO

--------------------开始查询--------------------

select *,DATEDIFF(day,stardate,enddate)+1 as yearday
from
(
  select id,
        case
          when stardate < '2012-1-1' then '2012-1-1'
          when stardate > '2012-12-31' then null
        else   stardate
        end as stardate,
        case
          when enddate < '2012-1-1' then null
          when enddate > '2012-12-31' then '2012-12-31'
        else   enddate
        end as enddate  
  from test
) as a   

--------------------运行结果--------------------
/*
id          stardate   enddate    yearday
----------- ---------- ---------- -----------
1           2012-01-01 2012-12-31 366
2           2012-05-01 2012-05-31 31
3           2012-07-01 2012-12-31 184

(3 行受影响)
*/[/code]

case when 换成 iif  或 switch 即可

VAT

先谢谢各位,再请高手帮忙看看我根据上述帖子回复编写的相应参数查询为什么无法运行?

代码如下:

SELECT 租赁合同表.ID, 租赁合同表.租赁期开始, 租赁合同表.租赁期结束, IIf(租赁期结束<[期间开始?] And 租赁期开始<[期间开始?],0,IIf(租赁期结束>[期间结束?] And 租赁期开始>[期间结束?],0,IIf(租赁期结束>[期间结束?],[期间结束?],租赁期结束)-IIf(租赁期开始<[期间开始?],[期间开始?],租赁期开始))) AS 期间有效天数
FROM 租赁合同表;

VAT

上述语句运行如下:

zhangjimfu

VAT 发表于 2012-10-22 22:48
上述语句运行如下:

SELECT 表2.ID, IIf([ENDDate]<datevalue([START?]) And [STARTDate]<datevalue([START?]),0,IIf([ENDDate]>datevalue([END?]) And [STARTDate]>datevalue([END?]),0,IIf([ENDDate]>#12/31/2012#,#12/31/2012#,[ENDDate])-IIf([STARTDate]<#1/1/2012#,#1/1/2012#,[STARTDate]))) AS 表达式1
FROM 表2;

amazeyeli

肯定不行了
数据类型不匹配
0不是日期格式
数据转换出错
[code=sql]USE tempdb
IF OBJECT_ID('TEST') IS NOT NULL
  DROP TABLE TEST;      --> 如果表TEST不为空,删除表
GO  
CREATE TABLE TEST       --> 创建表,字段及类型如下
(
  id           int,
  stardate     date,
  enddate      date,
);
GO
INSERT INTO TEST VALUES --> 向表中插入数据
(1,'2011-12-1','2013-11-30'),
(2,'2012-5-1','2012-5-31'),
(3,'2012-7-1','2013-6-30');
GO

--------------------开始查询--------------------

select *,DATEDIFF(day,stardate,enddate)+1 as yearday
from
(
  select id,
        case
          when stardate < '2012-1-1' then '2012-1-1'
          when stardate > '2012-12-31' then 0
        else   stardate
        end as stardate,
        case
          when enddate < '2012-1-1' then 0
          when enddate > '2012-12-31' then '2012-12-31'
        else   enddate
        end as enddate  
  from test
) as a   

--------------------运行结果--------------------
/*
消息 206，级别 16，状态 2，第 1 行
操作数类型冲突: int 与 date 不兼容
*/[/code]