ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 5762|回复: 10

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

[复制链接]

TA的精华主题

TA的得分主题

发表于 2012-10-21 21:04 | 显示全部楼层 |阅读模式
本帖最后由 VAT 于 2012-10-21 21:10 编辑

向各位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表达?谢谢!

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-10-21 21:09 | 显示全部楼层
http://hi.baidu.com/wwwzzg/item/a0f22014140391f9756a844e
链接是我搜到的相似例子,请教如何在ACCESS中实现。

TA的精华主题

TA的得分主题

发表于 2012-10-21 21:50 | 显示全部楼层
  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;
复制代码

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-10-21 22:32 | 显示全部楼层
版主,您的公式是否没有考虑到之前签订的合同:
像租赁期为2010-1-1至2010-12-31的合同,按您的合同将会计算为:ENDDate:2010-12-31减去STARTDate:2012-1-1,结果是负数!

TA的精华主题

TA的得分主题

发表于 2012-10-21 22:59 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 zhangjimfu 于 2012-10-21 23:02 编辑
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;

TA的精华主题

TA的得分主题

发表于 2012-10-22 10:41 | 显示全部楼层
[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 即可

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-10-22 22:42 | 显示全部楼层
先谢谢各位,再请高手帮忙看看我根据上述帖子回复编写的相应参数查询为什么无法运行?

代码如下:

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

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-10-22 22:48 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2012-10-23 09:22 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
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;

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2012-10-23 11:20 | 显示全部楼层
肯定不行了
数据类型不匹配
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]

评分

1

查看全部评分

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 17:59 , Processed in 0.054691 second(s), 15 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表