如何编写sql查询以获取“天以来” [英] How do I write sql query to get "days since"

问题描述

我是SQL的新手，但我觉得我差点就是这个。我有一个带有名为SicknessLog的表的ms访问数据库。字段是ID，StaffName，[开始/返回]，日期。



当一名工作人员因疾病而下班时，会在表格中添加一条记录， [开始/返回]字段中的值为1.当他们返回工作时，添加了具有相同详细信息的新记录，但[开始/返回]字段为0.



我正在尝试编写一个查询，显示员工生病时（[开始/返回]值为-1）和他们返回工作之间的天数（[开始/返回]值0）



到目前为止我有这个



SELECT [staffname]，DATEDIFF（d， min（[date]），max（[date]））[Days Sick]

FROM sicknesslog

WHERE [date]> （现在（） - 30）

GROUP BY [staffname]



这显示了每个工作人员在最后一天休息了多少天30天。我唯一的问题是，目前离开的员工也列出0天。如果可能的话，我希望它能显示自从他们生病以来的天数，对于尚未返回的工作人员而言。



提前感谢您的帮助

Hi, I am new to SQL but I think I'm nearly there with this one. I have an ms access database with a table called SicknessLog. The fields are ID, StaffName, [Start/Return], Date.

When a member of staff is off work for sickness then a record is added to the table and the value in the [Start/Return] field is 1. When they return to work a new record is added with the same details except the [Start/Return] field is 0.

I am trying to write a query that shows the number of days between when a member of staff went sick ([Start/Return] value of -1) and when they returned to work ([Start/Return] value of 0)

So far I have this

SELECT [staffname], DATEDIFF("d", min([date]), max([date])) as [Days Sick]
FROM sicknesslog
WHERE [date] > (now()-30)
GROUP BY [staffname]

This shows how many days off each staff member has had in the last 30 days. The only problem I have is that it is also listing 0 days for staff who are currently away. If possible I would like it to show the number of days since they went sick up intil now for staff who have not returned.

Thanks in advance for any help

推荐答案

替换now（）为max（[date]当min和max相等时：

Substtute now() for max([date] when min and max are equal :

IIF(min([date])=max([date]),now(),max([date]))

干杯

Cheers

这篇关于如何编写sql查询以获取“天以来”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！