这个薪资问题仍然需要天才解决方案 [英] Still This Payroll Problem Really Requires a Genius Solution
问题描述
Hello Experts，
我有一个表格，可以为员工分配期限。
数据如下
名称StartDate EndDate
X 14/11 / 07 17/12/07
Y 18/11/07 10/12/07
我的问题是我要计算每个员工工作的日子
但是我只想计算与这段时间相交的天数：（b / 11 // 07） - （12/12/07）或一般在当月15日和上个月的16日之间的
之间。
换句话说，我只需计算与本月相关的天数
忽略超出范围的天数。
我确实需要你的帮助谢谢
Hello Experts,
I have a Table that assigns periods to employees.
The data are like the following
Name StartDate EndDate
X 14/11/07 17/12/07
Y 18/11/07 10/12/07
My problem is that I want to calculate the days that each employee worked
but I ONLY want to calculate the days that are
intersecting with this period : (16/11//07) - ( 15/12/07) or generally between the
15th of the current month and the and the 16th of the previous month.
In other words, I need to calculate only the days that are related to this month
Ignoring the days that are out of the range.
I do need your HELP THANK YOU
推荐答案
你好专家，
我有一张表给员工分配期限。
数据如下
名称StartDate EndDate
X 14/11 / 07 17/12/07
Y 18/11/07 10/12/07
我的问题是我要计算每个员工工作的日子
但是我只想计算与这段时间相交的天数：（b / 11 // 07） - （12/12/07）或一般在当月15日和上个月的16日之间的
之间。
换句话说，我只需计算与本月相关的天数
忽略超出范围的天数。
我需要你的帮助谢谢
Hello Experts,
I have a Table that assigns periods to employees.
The data are like the following
Name StartDate EndDate
X 14/11/07 17/12/07
Y 18/11/07 10/12/07
My problem is that I want to calculate the days that each employee worked
but I ONLY want to calculate the days that are
intersecting with this period : (16/11//07) - ( 15/12/07) or generally between the
15th of the current month and the and the 16th of the previous month.
In other words, I need to calculate only the days that are related to this month
Ignoring the days that are out of the range.
I do need your HELP THANK YOU
选择姓名，DateDiff（d，BeginDate，EndDate）作为DaysWorke d来自YourTableName。
DateDiff将为您提供日期，月份，年份等2个日期之间的差异 - d =天。
Select Name, DateDiff("d", BeginDate, EndDate) as DaysWorked from YourTableName.
DateDiff will give you the difference between 2 dates in days, months, years, etc - d = days.
谢谢Jax，但问题要比那复杂得多。
我需要计算天数的差异，但仅限于一个月的范围内
正如我在第一篇文章中所述。
Thanks Jax but the problem is much more complicated than that.
I need to calculae the difference in days but only that are in the range of a month
as I said in first post.
感谢Jax，但问题要复杂得多。
我需要计算天数的差异，但只是在一个月的范围内，正如我在第一篇文章中所说的那样。
Thanks Jax but the problem is much more complicated than that.
I need to calculae the difference in days but only that are in the range of a month
as I said in first post.
需要3个查询。 StateDate的第1个（qry1）。 EndDate的第2名（qry2）。第3次到联盟。
选择名称，月份（StartDate）作为月份，iif月份（StartDate）<>月（EndDate），DateDiff（d，StartDate，DateAdd（d， - 1，CDate（1 /& Month（StartDate）+1&" /"& Year （StartDate））），DateDiff（" d"，StartDate，EndDate）as YourWorkable from YourTableName
从qry1中选择qry1。*;
UNION
选择qry2 。*来自qry2;
如果在同一个月，第一个查询将处理从StartDate到Start Month End或StartDate到EndDate的时间范围。
第二个查询将处理从End Month Begin到EndDate的时间，如果StartDate和EndDate与第一个查询中的计算结果相同，则为0。
Need 3 queries. The 1st for StateDate (qry1). 2nd for EndDate (qry2). 3rd to Union together.
Select Name, Month(StartDate) as Month,iif Month(StartDate) <> Month(EndDate), DateDiff("d",StartDate,DateAdd("d",-1,CDate("1/" & Month(StartDate) +1 & "/" & Year(StartDate))), DateDiff("d", StartDate, EndDate) as DaysWorked from YourTableName
Select Name, Month(StartDate) as Month,iif Month(StartDate) <> Month(EndDate), DateDiff("d",DateAdd("d",-1,CDate("1/" & Month(EndDate) & "/" & Year(EndDate)),EndDate), 0) as DaysWorked from YourTableName
Select qry1.* from qry1;
UNION
Select qry2.* from qry2;
The 1st query will handle the time frame from the StartDate to Start Month End or StartDate to EndDate if in same month.
The 2nd query will handle the time from End Month Begin to EndDate or 0 if StartDate and EndDate are in the same month as this is calc''d in the 1st query.
这篇关于这个薪资问题仍然需要天才解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
