需要计算的是，有多少人工作超过3个月
需要计算的是，有多少人工作超过3个月
提问于 2021-02-11 08:03:29
我需要计算，有多少人工作超过3个月，6个月，9个月等等。
declare @actualDate nvarchar(10) = '2021-02-01'
select startWork
from sm.PeopleWorkPeriod
where endWork is null在这里，我需要使用上面查询的日期。我不知道该怎么做
select datediff(month, '2020/05/12', @actualDate) AS 'Total month'
我需要的例子
Month Number of people
| 3-6 month | 5 |
| 6-9 month | 7 |
| 9-12 month | 24 |回答 2
Stack Overflow用户
回答已采纳
发布于 2021-02-11 08:49:40
计算他们工作的月数。然后使用case表达式对其进行分组(您似乎没有考虑到少于3个月而大于12个月)，然后计算结果。
declare @Test table (startWork date, endWork date);
declare @ActualDate date = '2021-02-01';
insert into @Test(startWork)
values
('1 nov 2020'),
('1 dec 2020');
select MonthGroup [Months], count(*) [Number of People]
from @Test--sm.PeopleWorkPeriod
-- Calculate months worked
cross apply (select datediff(month, startWork, @ActualDate)) X (Months)
-- Group them up as required
cross apply (select case when X.Months < 3 then '< 3' when X.Months < 6 then '3 - 6' when X.Months < 9 then '6 - 9' when X.Months < 12 then '9 - 12' else '> 12' end) Y (MonthGroup)
where endWork is null
group by MonthGroup;注意:永远不要使用nvarchar来存储日期(或日期时间)，使用正确的数据类型，否则就会在项目的生命周期中产生技术债务。
在未来的问题中，请以DDL+DML的形式提供示例数据(如上面所示)--如果您不希望我们全部输入，您将得到一个更快的答案。
Stack Overflow用户
发布于 2021-02-11 08:19:00
根据我对您问题的理解，您应该能够使用以下内容：
declare @actualDate nvarchar(10) = '2021-02-01';
select 'Less than 3 Months' as 'Months',count(*) as 'Employees'
from sm.PeopleWorkPeriod where endWork is null and
datediff(month, startWork ,@actualDate)<3
union
select '3-6 Months' as 'Months',count(*) as 'Employees'
from sm.PeopleWorkPeriod where endWork is null and datediff(month, startWork , @actualDate)>=3 and
datediff(month, startWork , @actualDate)<=6
union
select '6-9 Months' as 'Months',count(*) as 'Employees'
from sm.PeopleWorkPeriod where endWork is null and
datediff(month, startWork , @actualDate)>=6 and
datediff(month, startWork , @actualDate)<=9
union
select 'More than 9 Months' as 'Months',count(*) as 'Employees'
from sm.PeopleWorkPeriod where endWork is null and
datediff(month, startWork , @actualDate)>9页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/66150841
复制相关文章
相似问题
腾讯云开发者
