sql除星期六日根據入職日期計算天數,離職根據離職日期計算天數
- 2023-01-19
展開全部
——建議構建一個日曆表,能夠體現出工作日和非工作日,這樣算的話更準;
——下面是簡單剔除雙休的SQL方法
——mssql:
select a,
b,
COUNT(case
when d in (1, 7) then
null
else
1
end)
from (select a, b, c, DATEPART(dw, DATEADD(DAY, t1。number, a)) d
from (select a, b, DATEDIFF(day, a, b) as c
from (select ‘2012-01-01’ as a, ‘2012-12-31’ as b) t) t,
master 。。 spt_values t1
where t1。type = ‘P’
and t1。number group by a, b;
——oracle:
select a,
b,
count(case
when d in (1, 7) then
null
else
1
end)
from (select a, b, a + level - 1 as c, to_char(a + level - 1, ‘d’) d
from (select to_date(‘2012-01-01’, ‘yyyy-mm-dd’) a,
to_date(‘2012-12-31’, ‘yyyy-mm-dd’) b
from dual)
connect by level <= b - a)
group by a, b