从基本日期查询到计划日期条目查询的SQL

Question

我们手动运行脚本来提取员工在特定日期输入的总时间，并使用大于和小于来选择日期范围。谁能提供我的信息，如何使它如此调度脚本?

如何将日期设置为"脚本运行后2周输入的条目">

select SMEMBER.SLNAME, sum(TTIME.TTIME) as TotalTime
from TTIME
inner join SMEMBER on SMEMBER.SNUM = TTIME.SNUM
where TIDATE >= '2/15/2021' and TIDATE <= '2/19/2021'
group by SMEMBER.SLNAME;

Answer 1

使用

select SMEMBER.SLNAME, SUM(DATEDIFF(MINUTE, '00:00:00', TTIME.TTIME)) as TotalTime
from TTIME
inner join SMEMBER on SMEMBER.SNUM = TTIME.SNUM
where TIDATE >= '2/15/2021' and TIDATE <= '2/19/2021'
group by SMEMBER.SLNAME;

Answer 2

我想你需要这样的东西:

select m.SLNAME, sum(t.TTIME) as TotalTime
from TTIME t inner join
SMEMBER m
on m.SNUM = t.SNUM
where TIDATE >= dateadd(day, -14, convert(date, getdate())
group by m.SLNAME;

假设没有未来的日期。如果有，则添加:

where TIDATE < convert(date, getdate())

当然，这并不包括当前日期。如果你想把不平等包括进去，就调整一下。