Office中国论坛/Access中国论坛

标题: [求助]请SQL高手相助：不用自定义函数，如何计算员工每月的请假天数(已解决） [打印本页]

作者: zyp 时间: 2005-11-2 18:12
标题: [求助]请SQL高手相助：不用自定义函数，如何计算员工每月的请假天数(已解决）
这个贴本来是发在ADP版块的，因去ADP版块的朋友较少，所以借这个人事旺盛的版块一用，请版主误删，谢谢！

[求助]不用自定义函数，如何计算员工每月的请假天数

在ADP中，有如下的资料表（员工请假记录）：

Id

员工

起始日期

结束日期

1

张三

2005/10/1

2005/10/3

2

张三

2005/10/12

2005/10/12

3

张三

2005/10/24

2005/10/28

4

李四

2005/9/21

2005/10/15

5

李四

2005/10/25

2005/10/26

6

李四

2005/10/30

2005/11/15

7

王二

2005/9/15

2005/11/14

现在需计算员工在10月份的请假记录，应该为如下结果：<FONT face="Times New Roman">

<TABLE cellSpacing=0 cellPadding=0 border=1>

员工

请假天数

<TR>

张三

<TD vAlign=top width=96>

<FONT colo

作者: LucasLynn 时间: 2005-11-2 20:02
这个问题和昨天有人问我的一个生日查询的问题有些类似。因为我对ADP不熟悉，无法很快提供给你一个答案，在此提供一个思路：

你可以先对于本月内的跨月假期重新构建起始日期为均在当月内，然后对重新构建后的日期进行统计。

例如查询10月：

记录1：10.2-10.5

重新构建后为10.2-10.5

记录2：9.8-10.6

重新构建后为10.1-10.6

记录3：10.9-11.2

重新构建后为10.9-10.31

记录4：9.25-11.6

重新构建后为：10.1-10.31

[此贴子已经被作者于2005-11-2 12:05:06编辑过]

作者: secowu 时间: 2005-11-2 21:38
这种变通的想法真的很好，牧人，经常有特别有用的思路呵呵。。。。受益良多

作者: zyp 时间: 2005-11-3 06:31
LucasLynn版主果然不同凡响，谢谢！我只考虑该用什么的SQL内建函数和方法，而没有考虑到版主的方法我先测试一下是否可以实现。

作者: eyewitnes 时间: 2005-11-3 23:34

以下是引用zyp在2005-11-2 22:31:00的发言：

LucasLynn版主果然不同凡响，谢谢！

我只考虑该用什么的SQL内建函数和方法，而没有考虑到版主的方法

我先测试一下是否可以实现。

测试后能否分享一下？我琢磨了半天都没办法通过视图实现，以下是我目前能想到，而且在SQL2000中通过了的：

SELECT 姓名, 起始日期, 结束日期

FROM dbo.请假明细表

WHERE (MONTH(GETDATE()) BETWEEN MONTH(起始日期) AND MONTH(结束日期))

作者: zyp 时间: 2005-11-4 00:54
好的，因临时有点任务，可能会推迟几天才会有结果eyewitnes 的方法尚不能实现的我目的。谢谢！

作者: 海狸先生 时间: 2005-11-4 04:18
SELECT 员工, sum(IIf(结束日期>#10/31/2005#,#10/31/2005#,结束日期)-IIf(起始日期<#10/1/2005#,#10/1/2005#,起始日期)+1)

FROM Sheet1

GROUP BY 员工;

作者: 海狸先生 时间: 2005-11-4 04:20
我搞不懂　1)起始日和终止日均为当月的：请假天数　＝　终止日　－　起始日　＋　１　　：如　10月1日到10月5日

(2)起始日为当月之前，终止日为当月：请假天数　＝　终止日　－　当月１号　（或day（终止日））　：　如9月1日到10月5日两个　10月1日到10月5日都没上班，为什么一个　算　5天　，一个却只算4天　？

作者: zyp 时间: 2005-11-4 21:52

以下是引用海狸先生在2005-11-3 20:20:00的发言：

我搞不懂　

1)起始日和终止日均为当月的：请假天数　＝　终止日　－　起始日　＋　１　　：如　10月1日到10月5日

(2)起始日为当月之前，终止日为当月：请假天数　＝　终止日　－　当月１号　（或day（终止日））　：　如9月1日到10月5日

两个　10月1日到10月5日都没上班，为什么一个　算　5天　，一个却只算4天　？

谢谢海狸版主

对不起，我是不小心打漏了，应该是两个都是5天：

1)起始日和终止日均为当月的：请假天数　＝　终止日　－　起始日　＋　１　　：如　10月1日到10月5日

(2)起始日为当月之前，终止日为当月：请假天数　＝　终止日　－　当月１号　＋　１　（或day（终止日））　：　如9月1日到10月5日

[此贴子已经被作者于2005-11-4 13:53:55编辑过]

作者: zyp 时间: 2005-11-4 21:59

以下是引用海狸先生在2005-11-3 20:18:00的发言：

SELECT 员工, sum(IIf(结束日期>#10/31/2005#,#10/31/2005#,结束日期)-IIf(起始日期<#10/1/2005#,#10/1/2005#,起始日期)+1)

FROM Sheet1

GROUP BY 员工;

版主的方法在MDB中绝对没问题，但我是用在ADP中的，没用iif函数

作者: 海狸先生 时间: 2005-11-4 22:20
在ADP中 true 还是等于-1的吗？如果是，那么SELECT 员工, sum((起始日期<#10/1/2005#)*#10/1/2005#+(起始日期>=#10/1/2005#)*起始日期-(结束日期>#10/31/2005#)*#10/31/2005#-(结束日期<=#10/31/2005#)*结束日期+1)

FROM Sheet1

GROUP BY 员工;

作者: 海狸先生 时间: 2005-11-4 22:23
再加一个 where 结束日期>=#2005-10-1# 更保险

作者: eyewitnes 时间: 2005-11-5 02:41
SUM

返回表达式中所有值的和，或只返回 DISTINCT 值。SUM 只能用于数字列。空值将被忽略

日期型的。。。估计不行，最令我不解的是SQL2000提示“<”附近有错误。。。按说SQL2000不可能不支持比较运算吧？

作者: 海狸先生 时间: 2005-11-5 04:52
呵呵，在MDB中是可以的

作者: eyewitnes 时间: 2005-11-5 17:01
这个难在怎样将跨月的日期转变成本月的第一日或者最后一日，，想不出来哇

作者: zyp 时间: 2005-11-6 01:25
多谢LucasLynn 版主的指点，问题已解决

下面是在ADP中写的存储过程，其中@firstDay为要查询月的第一天，@LastDay为要查询的最后一天

Alter Procedure "JiaTsM"

(@FirstDay datetime,@LastDay DateTime)

As

   declare @Y int,@M int

   set @Y=year(@FirstDay)

   set @M=month(@FirstDay)

   SELECT 员工,

               Convert(int,Case When 终止日期>@LastDay Then @LastDay Else 终止日期

               End-Case When 起始日期<@FirstDay Then @FirstDay Else 起始日期 End)

            As 天数

   From 请假表

   WHERE

            ((year(起始日期)=@Y and month(起始日期)=@M) or

            (year(终止日期)=@Y and month(终止日期)=@M) or

            (起始日期<@FirstDay and 终止日期>@LastDay))

return

[此贴子已经被作者于2005-11-5 17:26:58编辑过]

作者: zyp 时间: 2005-11-6 01:30

以下是引用海狸先生在2005-11-4 14:20:00的发言：

在ADP中 true 还是等于-1的吗？如果是，那么

SELECT 员工, sum((起始日期<#10/1/2005#)*#10/1/2005#+(起始日期>=#10/1/2005#)*起始日期-(结束日期>#10/31/2005#)*#10/31/2005#-(结束日期<=#10/31/2005#)*结束日期+1)

FROM Sheet1

GROUP BY 员工;

谢谢海狸版主，在ADP中True为1，False为0

再次谢谢两位版主的提点，及各位网友的热情帮助[em24][em24][em24][em24][em25][em25][em25][em25][em26][em26][em26][em26][em27][em27][em27][em27]

[此贴子已经被作者于2005-11-5 17:31:53编辑过]

作者: 海狸先生 时间: 2005-11-8 15:35
那这样应该可以吧SELECT 员工, sum((结束日期>#10/31/2005#)*#10/31/2005#+(结束日期<=#10/31/2005#)*结束日期-(起始日期<#10/1/2005#)*#10/1/2005#-(起始日期>=#10/1/2005#)*起始日期+1)

FROM Sheet1

GROUP BY 员工;

作者: eyewitnes 时间: 2005-11-8 18:17

以下是引用海狸先生在2005-11-8 7:35:00的发言：

那这样应该可以吧

SELECT 员工, sum((结束日期>#10/31/2005#)*#10/31/2005#+(结束日期<=#10/31/2005#)*结束日期-(起始日期<#10/1/2005#)*#10/1/2005#-(起始日期>=#10/1/2005#)*起始日期+1)

FROM Sheet1

GROUP BY 员工;

SELECT 子句错误：'>' 附近的表达式。

SELECT 子句错误：'FROM' 附近的表达式。

丢失 FROM 子句。

无法对查询文本做语法分析。

不行呢SQL2000中通不过

作者: 海狸先生 时间: 2005-11-8 19:19
啊。sql2000不支持比较？

作者: zyp 时间: 2005-11-8 22:38

以下是引用eyewitnes在2005-11-8 10:17:00的发言：

SELECT 子句错误：'>' 附近的表达式。

SELECT 子句错误：'FROM' 附近的表达式。

丢失 FROM 子句。

无法对查询文本做语法分析。

不行呢SQL2000中通不过

海狸版主的代碼我在SQL 7.0中也沒通過，提示與你的差不多．

謝謝海狸版主，你所寫的表達式太高深了，我看不太明白（如整數＊日期是什麼），能不能注解一下，或許順著你的思路，在ａｄｐ中變通一下，就能實現目的了．

謝謝！

[此贴子已经被作者于2005-11-8 16:40:07编辑过]

作者: zyp 时间: 2005-11-9 05:38
在SQL中，不支持用日期*数字，报告数据类型不同不可以进行这样的操作

作者: 海狸先生 时间: 2005-11-9 06:05
'日期其实就是　数字　如　2005-10-1就是38636SELECT 员工, sum(

(结束日期>#10/31/2005#) '大于则为1，否则为0

*#10/31/2005#

+

(结束日期<=#10/31/2005#)　'大于则为0，否则为1 ，结合一下，可以推出结果

*结束日期

-

(起始日期<#10/1/2005#)

*#10/1/2005#

-

(起始日期>=#10/1/2005#)

*起始日期

+1)

FROM Sheet1 GROUP BY 员工;

作者: eyewitnes 时间: 2005-11-9 18:44
现在看来，好像是只能在触发器或者存储过程才支持比较运算。。视图中不支持？应该不可能吖。。是不是我们的语法搞错了？？

作者: eyewitnes 时间: 2005-11-9 18:50
存储过程是通过了，不过如果用存储过程的话，我想在表里边增加一个字段记录实际请假天数，用触发器来刷新可能会更好

作者: zyp 时间: 2005-11-10 07:23

以下是引用海狸先生在2005-11-8 22:05:00的发言：

'日期其实就是　数字　如　2005-10-1就是38636

谢谢海狸版主，在ADP中好像不支持这个算法。

作者: zyp 时间: 2005-11-10 07:25

以下是引用eyewitnes在2005-11-9 10:50:00的发言：

存储过程是通过了，不过如果用存储过程的话，我想在表里边增加一个字段记录实际请假天数，用触发器来刷新可能会更好

直接用计算字段不行吗？

作者: eyewitnes 时间: 2005-11-12 18:24
计算字段？

作者: eyewitnes 时间: 2005-11-12 18:31
CREATE PROCEDURE dbo.ABC

@Month as int

AS

BEGIN

Declare @Start as smalldatetime  -- '10/01/2005'

Declare @End  as smalldatetime -- '11/01/2005'

SET @Start = Cast((cast(@Month as char(2)) + '/01/' + year(getdate())) as smalldatetime)

SET @Start = Cast((cast((@Month+1) as char(2)) + '/01/' + year(getdate())) as smalldatetime)

SELECT 姓名 ,

DATEDIFF(day,

CASE 起始日期

  when 起始日期>=@End  then @End

  when 起始日期<@Start  then @Start

  Else 起始日期

END

,

CASE 结束日期

  when 结束日期>=@End  then @End

  when 结束日期<@Start  then @Start

  Else 结束日期

END

)

AS 请假日期

FROM 表名

END

-- 如果是五天工作制，还要考虑消除周末

原帖来自于网易社区:http://p5.club.163.com/viewArticleByWWW.m?boardId=database&articleId=database_1075e61c

抄回来得7444390 以上均为抄袭，呵呵

[此贴子已经被作者于2005-11-12 11:03:35编辑过]

作者: fuyvn 时间: 2005-11-12 20:01
好象忘了一件小事. 那就是 ACCESS中日期是 # 开头和结尾. SQL中是以 ' 开头结尾.

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)