create procedure mypro
@min int
as
select d.emp_id,d.日期,d.班別, case when d.班別='3' then n.上班時間1 else d.上班時間1 end as 上班時間1,
case when d.班別='3' then n.下班時間1 else d.下班時間1 end as 下班時間1,
d.上班時間2,d.下班時間2,d.上班時間3,d.下班時間3
from
(----白班
select m.日期,m.emp_id,case when m.班別 is null and m.上班時間1 is not null then '節假日加班' else m.班別 end as 班別,m.上班時間1,m.下班時間1,m.上班時間2,m.下班時間2,n.加上1 as 上班時間3,n.加下1 as 下班時間3
from
(select p.日期,p.emp_id ,p.班別,case when p.上班時間1 is not null then p.上班時間1 else f.加上1 end as 上班時間1,
case when p.下班時間1 is not null then p.下班時間1 else f.加下1 end as 下班時間1,
case when p.上班時間2 is not null then p.上班時間2 else f.加上2 end as 上班時間2,
case when p.下班時間2 is not null then p.下班時間2 else f.加下2 end as 下班時間2
from
(SELECT a.*, v1.上班時間1, v4.下班時間1, v2.上班時間2, v5.下班時間2, v3.上班時間3,
v6.下班時間3
FROM (SELECT a.*, b.班別
FROM (SELECT DISTINCT
TOP 100 PERCENT CONVERT(varchar(10), field9, 111)
AS 日期, field21 AS emp_id
FROM dbo.T093F040
ORDER BY field21) AS a LEFT JOIN
(SELECT field33 AS 日期, field31 AS 工號,
field32 AS 班別
FROM T075F080) AS b ON a.日期 = b.日期 AND
a.emp_id = b.工號) a LEFT OUTER JOIN
(SELECT a.日期, a.emp_id, a.班別, a.上班時間1
FROM (SELECT a.日期, a.emp_id, b.班別, CASE WHEN a.door_id = 2 AND
a.passtime < dateadd(mi, @min, b.上班時間1) THEN a.passtime END AS 上班時間1,
CASE WHEN a.door_id = 2 AND a.passtime < dateadd(mi, @min, b.上班時間2) AND
a.passtime > dateadd(mi, @min, b.上班時間1)
THEN a.passtime END AS 上班時間2, CASE WHEN a.door_id = 2 AND
a.passtime < dateadd(mi, @min, b.上班時間3) AND a.passtime > dateadd(mi, @min,
b.上班時間2) THEN a.passtime END AS 上班時間3,
CASE WHEN a.door_id = 7 AND a.passtime < dateadd(mi, @min, b.下班時間1)
THEN a.passtime END AS 下班時間1, CASE WHEN a.door_id = 7 AND
a.passtime < dateadd(mi, @min, b.下班時間2) AND a.passtime > dateadd(mi, @min,
b.下班時間1) THEN a.passtime END AS 下班時間2,
CASE WHEN a.door_id = 7 AND a.passtime < dateadd(mi, @min, b.下班時間3) AND
a.passtime > dateadd(mi, @min, b.下班時間2)
THEN a.passtime END AS 下班時間3
FROM (SELECT CONVERT(varchar(10), p.field9, 111) AS 日期, p.field21 AS emp_id,
p.field3 AS door_id, p.field9 AS passtime
FROM T093F040 p) a INNER JOIN
(SELECT b.field31 AS 工號, b.field33 AS 日期, b.field32 AS 班別,
b.field16 AS 上班時間1, b.field13 AS 下班時間1,
b.field18 AS 上班時間2, b.field14 AS 下班時間2,
b.field17 AS 上班時間3, b.field15 AS 下班時間3
FROM T075F080 b) b ON a.日期 = b.日期 AND a.emp_id = b.工號) a
WHERE a.上班時間1 IS NOT NULL) v1 ON v1.日期 = a.日期 AND
a.emp_id = v1.emp_id LEFT OUTER JOIN
(SELECT a.日期, a.emp_id, a.班別, a.上班時間2
FROM (SELECT a.日期, a.emp_id, b.班別, CASE WHEN a.door_id = 2 AND
a.passtime < dateadd(mi, @min, b.上班時間1) THEN a.passtime END AS 上班時間1,
CASE WHEN a.door_id = 2 AND a.passtime < dateadd(mi, @min, b.上班時間2) AND
a.passtime > dateadd(mi, @min, b.上班時間1)
THEN a.passtime END AS 上班時間2, CASE WHEN a.door_id = 2 AND
a.passtime < dateadd(mi, @min, b.上班時間3) AND a.passtime > dateadd(mi, @min,
b.上班時間2) THEN a.passtime END AS 上班時間3,
CASE WHEN a.door_id = 7 AND a.passtime < dateadd(mi, @min, b.下班時間1)
THEN a.passtime END AS 下班時間1, CASE WHEN a.door_id = 7 AND
a.passtime < dateadd(mi, @min, b.下班時間2) AND a.passtime > dateadd(mi, @min,
b.下班時間1) THEN a.passtime END AS 下班時間2,
CASE WHEN a.door_id = 7 AND a.passtime < dateadd(mi, @min, b.下班時間3) AND
a.passtime > dateadd(mi, @min, b.下班時間2)
THEN a.passtime END AS 下班時間3
FROM (SELECT CONVERT(varchar(10), p.field9, 111) AS 日期, p.field21 AS emp_id,
p.field3 AS door_id, p.field9 AS passtime
FROM T093F040 p) a INNER JOIN
(SELECT b.field31 AS 工號, b.field33 AS 日期, b.field32 AS 班別,
b.field16 AS 上班時間1, b.field13 AS 下班時間1,
b.field18 AS 上班時間2, b.field14 AS 下班時間2,
b.field17 AS 上班時間3, b.field15 AS 下班時間3
FROM T075F080 b) b ON a.日期 = b.日期 AND a.emp_id = b.工號) a
WHERE a.上班時間2 IS NOT NULL) v2 ON v2.日期 = a.日期 AND
a.emp_id = v2.emp_id LEFT OUTER JOIN
(SELECT a.日期, a.emp_id, a.班別, a.上班時間3
FROM (SELECT a.日期, a.emp_id, b.班別, CASE WHEN a.door_id = 2 AND
a.passtime < dateadd(mi, @min, b.上班時間1) THEN a.passtime END AS 上班時間1,
CASE WHEN a.door_id = 2 AND a.passtime < dateadd(mi, @min, b.上班時間2) AND
a.passtime > dateadd(mi, @min, b.上班時間1)
THEN a.passtime END AS 上班時間2, CASE WHEN a.door_id = 2 AND
a.passtime < dateadd(mi, @min, b.上班時間3) AND a.passtime > dateadd(mi, @min,
b.上班時間2) THEN a.passtime END AS 上班時間3,
CASE WHEN a.door_id = 7 AND a.passtime < dateadd(mi, @min, b.下班時間1)
THEN a.passtime END AS 下班時間1, CASE WHEN a.door_id = 7 AND
a.passtime < dateadd(mi, @min, b.下班時間2) AND a.passtime > dateadd(mi, @min,
b.下班時間1) THEN a.passtime END AS 下班時間2,
CASE WHEN a.door_id = 7 AND a.passtime < dateadd(mi, @min, b.下班時間3) AND
a.passtime > dateadd(mi, @min, b.下班時間2)
THEN a.passtime END AS 下班時間3
FROM (SELECT CONVERT(varchar(10), p.field9, 111) AS 日期, p.field21 AS emp_id,
p.field3 AS door_id, p.field9 AS passtime
FROM T093F040 p) a INNER JOIN
(SELECT b.field31 AS 工號, b.field33 AS 日期, b.field32 AS 班別,
b.field16 AS 上班時間1, b.field13 AS 下班時間1,
b.field18 AS 上班時間2, b.field14 AS 下班時間2,
b.field17 AS 上班時間3, b.field15 AS 下班時間3
FROM T075F080 b) b ON a.日期 = b.日期 AND a.emp_id = b.工號) a
WHERE a.上班時間3 IS NOT NULL) v3 ON v3.日期 = a.日期 AND
a.emp_id = v3.emp_id LEFT OUTER JOIN
(SELECT a.日期, a.emp_id, a.班別, a.下班時間1
FROM (SELECT a.日期, a.emp_id, b.班別, CASE WHEN a.door_id = 2 AND
a.passtime < dateadd(mi, @min, b.上班時間1) THEN a.passtime END AS 上班時間1,
CASE WHEN a.door_id = 2 AND a.passtime < dateadd(mi, @min, b.上班時間2) AND
a.passtime > dateadd(mi, @min, b.上班時間1)
THEN a.passtime END AS 上班時間2, CASE WHEN a.door_id = 2 AND
a.passtime < dateadd(mi, @min, b.上班時間3) AND a.passtime > dateadd(mi, @min,
b.上班時間2) THEN a.passtime END AS 上班時間3,
CASE WHEN a.door_id = 7 AND a.passtime < dateadd(mi, @min, b.下班時間1)
THEN a.passtime END AS 下班時間1, CASE WHEN a.door_id = 7 AND
a.passtime < dateadd(mi, @min, b.下班時間2) AND a.passtime > dateadd(mi, @min,
b.下班時間1) THEN a.passtime END AS 下班時間2,
CASE WHEN a.door_id = 7 AND a.passtime < dateadd(mi, @min, b.下班時間3) AND
a.passtime > dateadd(mi, @min, b.下班時間2)
THEN a.passtime END AS 下班時間3
FROM (SELECT CONVERT(varchar(10), p.field9, 111) AS 日期, p.field21 AS emp_id,
p.field3 AS door_id, p.field9 AS passtime
FROM T093F040 p) a INNER JOIN
(SELECT b.field31 AS 工號, b.field33 AS 日期, b.field32 AS 班別,
b.field16 AS 上班時間1, b.field13 AS 下班時間1,
b.field18 AS 上班時間2, b.field14 AS 下班時間2,
b.field17 AS 上班時間3, b.field15 AS 下班時間3
FROM T075F080 b) b ON a.日期 = b.日期 AND a.emp_id = b.工號) a
WHERE a.下班時間1 IS NOT NULL) v4 ON v4.日期 = a.日期 AND
a.emp_id = v4.emp_id LEFT OUTER JOIN
(SELECT a.日期, a.emp_id, a.班別, a.下班時間2
FROM (SELECT a.日期, a.emp_id, b.班別, CASE WHEN a.door_id = 2 AND
a.passtime < dateadd(mi, @min, b.上班時間1) THEN a.passtime END AS 上班時間1,
CASE WHEN a.door_id = 2 AND a.passtime < dateadd(mi, @min, b.上班時間2) AND
a.passtime > dateadd(mi, @min, b.上班時間1)
THEN a.passtime END AS 上班時間2, CASE WHEN a.door_id = 2 AND
a.passtime < dateadd(mi, @min, b.上班時間3) AND a.passtime > dateadd(mi, @min,
b.上班時間2) THEN a.passtime END AS 上班時間3,
CASE WHEN a.door_id = 7 AND a.passtime < dateadd(mi, @min, b.下班時間1)
THEN a.passtime END AS 下班時間1, CASE WHEN a.door_id = 7 AND
a.passtime < dateadd(mi, @min, b.下班時間2) AND a.passtime > dateadd(mi, @min,
b.下班時間1) THEN a.passtime END AS 下班時間2,
CASE WHEN a.door_id = 7 AND a.passtime < dateadd(mi, @min, b.下班時間3) AND
a.passtime > dateadd(mi, @min, b.下班時間2)
THEN a.passtime END AS 下班時間3
FROM (SELECT CONVERT(varchar(10), p.field9, 111) AS 日期, p.field21 AS emp_id,
p.field3 AS door_id, p.field9 AS passtime
FROM T093F040 p) a INNER JOIN
(SELECT b.field31 AS 工號, b.field33 AS 日期, b.field32 AS 班別,
b.field16 AS 上班時間1, b.field13 AS 下班時間1,
b.field18 AS 上班時間2, b.field14 AS 下班時間2,
b.field17 AS 上班時間3, b.field15 AS 下班時間3
FROM T075F080 b) b ON a.日期 = b.日期 AND a.emp_id = b.工號) a
WHERE a.下班時間2 IS NOT NULL) v5 ON v5.日期 = a.日期 AND
a.emp_id = v5.emp_id LEFT OUTER JOIN
(SELECT a.日期, a.emp_id, a.班別, a.下班時間3
FROM (SELECT a.日期, a.emp_id, b.班別, CASE WHEN a.door_id = 2 AND
a.passtime < dateadd(mi, @min, b.上班時間1) THEN a.passtime END AS 上班時間1,
CASE WHEN a.door_id = 2 AND a.passtime < dateadd(mi, @min, b.上班時間2) AND
a.passtime > dateadd(mi, @min, b.上班時間1)
THEN a.passtime END AS 上班時間2, CASE WHEN a.door_id = 2 AND
a.passtime < dateadd(mi, @min, b.上班時間3) AND a.passtime > dateadd(mi, @min,
b.上班時間2) THEN a.passtime END AS 上班時間3,
CASE WHEN a.door_id = 7 AND a.passtime < dateadd(mi, @min, b.下班時間1)
THEN a.passtime END AS 下班時間1, CASE WHEN a.door_id = 7 AND
a.passtime < dateadd(mi, @min, b.下班時間2) AND a.passtime > dateadd(mi, @min,
b.下班時間1) THEN a.passtime END AS 下班時間2,
CASE WHEN a.door_id = 7 AND a.passtime < dateadd(mi, @min, b.下班時間3) AND
a.passtime > dateadd(mi, @min, b.下班時間2)
THEN a.passtime END AS 下班時間3
FROM (SELECT CONVERT(varchar(10), p.field9, 111) AS 日期, p.field21 AS emp_id,
p.field3 AS door_id, p.field9 AS passtime
FROM T093F040 p) a INNER JOIN
(SELECT b.field31 AS 工號, b.field33 AS 日期, b.field32 AS 班別,
b.field16 AS 上班時間1, b.field13 AS 下班時間1,
b.field18 AS 上班時間2, b.field14 AS 下班時間2,
b.field17 AS 上班時間3, b.field15 AS 下班時間3
FROM T075F080 b) b ON a.日期 = b.日期 AND a.emp_id = b.工號) a
WHERE a.下班時間3 IS NOT NULL) v6 ON v6.日期 = a.日期 AND
a.emp_id = v6.emp_id
) as p left join (SELECT j.emp_id, j.日期, v1.加上1, v2.加下1, v3.加上2, v4.加下2
FROM (SELECT CONVERT(varchar(10), field14, 111) AS 日期,
field31 AS emp_id
FROM T074F080
WHERE field32 != 1.5) j LEFT OUTER JOIN
(SELECT a.emp_id, a.日期, a.加上1
FROM (SELECT c.emp_id, c.日期, CASE WHEN c.door_id = 2 AND
abs(datediff(mi, c.加上1, c.passtime))
< @min THEN c.passtime ELSE NULL END AS 加上1,
CASE WHEN c.door_id = 7 AND abs(datediff(mi,
c.加下1, c.passtime))
< @min THEN c.passtime ELSE NULL END AS 加下1,
CASE WHEN c.door_id = 2 AND abs(datediff(mi,
c.加上2, c.passtime))
< @min THEN c.passtime ELSE NULL END AS 加上2,
CASE WHEN c.door_id = 7 AND abs(datediff(mi,
c.加下2, c.passtime))
< @min THEN c.passtime ELSE NULL
END AS 加下2
FROM (SELECT a.日期, a.emp_id, a.加上1, a.加下1,
a.加上2, a.加下2, b.passtime,
b.door_id
FROM (SELECT CONVERT(varchar(10),
field14, 111) AS 日期,
field13 AS 加上1,
field15 AS 加下1,
field17 AS 加上2,
field18 AS 加下2,
field31 AS emp_id
FROM T074F080
WHERE field32 != 1.5) AS a LEFT
JOIN
(SELECT field9 AS passtime,
field3 AS door_id,
field21 AS emp_id,
CONVERT(varchar(10),
field9, 111)
AS 日期
FROM T093F040) AS b ON
a.emp_id = b.emp_id AND
a.日期 = b.日期) AS c) AS a
WHERE a.加上1 IS NOT NULL) v1 ON j.emp_id = v1.emp_id AND
j.日期 = v1.日期 LEFT OUTER JOIN
(SELECT a.emp_id, a.日期, a.加下1
FROM (SELECT c.emp_id, c.日期, CASE WHEN c.door_id = 2 AND
abs(datediff(mi, c.加上1, c.passtime))
< @min THEN c.passtime ELSE NULL END AS 加上1,
CASE WHEN c.door_id = 7 AND abs(datediff(mi,
c.加下1, c.passtime))
< @min THEN c.passtime ELSE NULL END AS 加下1,
CASE WHEN c.door_id = 2 AND abs(datediff(mi,
c.加上2, c.passtime))
< @min THEN c.passtime ELSE NULL END AS 加上2,
CASE WHEN c.door_id = 7 AND abs(datediff(mi,
c.加下2, c.passtime))
< @min THEN c.passtime ELSE NULL
END AS 加下2
FROM (SELECT a.日期, a.emp_id, a.加上1, a.加下1,
a.加上2, a.加下2, b.passtime,
b.door_id
FROM (SELECT CONVERT(varchar(10),
field14, 111) AS 日期,
field13 AS 加上1,
field15 AS 加下1,
field17 AS 加上2,
field18 AS 加下2,
field31 AS emp_id
FROM T074F080
WHERE field32 != 1.5) AS a LEFT
JOIN
(SELECT field9 AS passtime,
field3 AS door_id,
field21 AS emp_id,
CONVERT(varchar(10),
field9, 111)
AS 日期
FROM T093F040) AS b ON
a.emp_id = b.emp_id AND
a.日期 = b.日期) AS c) AS a
WHERE a.加下1 IS NOT NULL) v2 ON j.emp_id = v2.emp_id AND
j.日期 = v2.日期 LEFT OUTER JOIN
(SELECT a.emp_id, a.日期, a.加上2
FROM (SELECT c.emp_id, c.日期, CASE WHEN c.door_id = 2 AND
abs(datediff(mi, c.加上1, c.passtime))
< @min THEN c.passtime ELSE NULL END AS 加上1,
CASE WHEN c.door_id = 7 AND abs(datediff(mi,
c.加下1, c.passtime))
< @min THEN c.passtime ELSE NULL END AS 加下1,
CASE WHEN c.door_id = 2 AND abs(datediff(mi,
c.加上2, c.passtime))
< @min THEN c.passtime ELSE NULL END AS 加上2,
CASE WHEN c.door_id = 7 AND abs(datediff(mi,
c.加下2, c.passtime))
< @min THEN c.passtime ELSE NULL
END AS 加下2
FROM (SELECT a.日期, a.emp_id, a.加上1, a.加下1,
a.加上2, a.加下2, b.passtime,
b.door_id
FROM (SELECT CONVERT(varchar(10),
field14, 111) AS 日期,
field13 AS 加上1,
field15 AS 加下1,
field17 AS 加上2,
field18 AS 加下2,
field31 AS emp_id
FROM T074F080
WHERE field32 != 1.5) AS a LEFT
JOIN
(SELECT field9 AS passtime,
field3 AS door_id,
field21 AS emp_id,
CONVERT(varchar(10),
field9, 111)
AS 日期
FROM T093F040) AS b ON
a.emp_id = b.emp_id AND
a.日期 = b.日期) AS c) AS a
WHERE a.加上2 IS NOT NULL) v3 ON j.emp_id = v3.emp_id AND
j.日期 = v3.日期 LEFT OUTER JOIN
(SELECT a.emp_id, a.日期, a.加下2
FROM (SELECT c.emp_id, c.日期, CASE WHEN c.door_id = 2 AND
abs(datediff(mi, c.加上1, c.passtime))
< @min THEN c.passtime ELSE NULL END AS 加上1,
CASE WHEN c.door_id = 7 AND abs(datediff(mi,
c.加下1, c.passtime))
< @min THEN c.passtime ELSE NULL END AS 加下1,
CASE WHEN c.door_id = 2 AND abs(datediff(mi,
c.加上2, c.passtime))
< @min THEN c.passtime ELSE NULL END AS 加上2,
CASE WHEN c.door_id = 7 AND abs(datediff(mi,
c.加下2, c.passtime))
< @min THEN c.passtime ELSE NULL
END AS 加下2
FROM (SELECT a.日期, a.emp_id, a.加上1, a.加下1,
a.加上2, a.加下2, b.passtime,
b.door_id
FROM (SELECT CONVERT(varchar(10),
field14, 111) AS 日期,
field13 AS 加上1,
field15 AS 加下1,
field17 AS 加上2,
field18 AS 加下2,
field31 AS emp_id
FROM T074F080
WHERE field32 != 1.5) AS a LEFT
JOIN
(SELECT field9 AS passtime,
field3 AS door_id,
field21 AS emp_id,
CONVERT(varchar(10),
field9, 111)
AS 日期
FROM T093F040) AS b ON
a.emp_id = b.emp_id AND
a.日期 = b.日期) AS c) AS a
WHERE a.加下2 IS NOT NULL) v4 ON j.emp_id = v4.emp_id AND
j.日期 = v4.日期) as f
on p.emp_id =f.emp_id and p.日期=f.日期
) as m left join (SELECT a.emp_id, a.日期, b.加上1, c.加下1--加入一般性加班
FROM (SELECT field31 AS emp_id, CONVERT(varchar(10), field14, 111)
AS 日期
FROM T074F080
WHERE field32 = '1.5') a LEFT OUTER JOIN
(SELECT a.*
FROM (SELECT j.emp_id, j.日期, CASE WHEN abs(datediff(mi,
p.passtime, j.加上1)) < @min AND
p.door_id = 2 THEN p.passtime END AS 加上1
FROM (SELECT field31 AS emp_id,
CONVERT(varchar(10), field14, 111)
AS 日期, field13 AS 加上1,
field15 AS 加下1
FROM T074F080
WHERE field32 = '1.5') AS j LEFT JOIN
(SELECT CONVERT(varchar(10), p.field9, 111)
AS 日期, p.field21 AS emp_id,
p.field3 AS door_id,
p.field9 AS passtime
FROM T093F040 p) AS p ON
j.emp_id = p.emp_id AND j.日期 = p.日期) a
WHERE a.加上1 IS NOT NULL) b ON a.emp_id = b.emp_id AND
a.日期 = b.日期 LEFT OUTER JOIN
(SELECT b.*
FROM (SELECT j.emp_id, j.日期, CASE WHEN abs(datediff(mi,
p.passtime, j.加下1)) < @min AND
p.door_id = 7 THEN p.passtime END AS 加下1
FROM (SELECT field31 AS emp_id,
CONVERT(varchar(10), field14, 111)
AS 日期, field13 AS 加上1,
field15 AS 加下1
FROM T074F080
WHERE field32 = '1.5') AS j LEFT JOIN
(SELECT CONVERT(varchar(10), p.field9, 111)
AS 日期, p.field21 AS emp_id,
p.field3 AS door_id,
p.field9 AS passtime
FROM T093F040 p) AS p ON
j.emp_id = p.emp_id AND j.日期 = p.日期)
AS b
WHERE b.加下1 IS NOT NULL) c ON a.emp_id = c.emp_id AND
a.日期 = c.日期) as n
on m.emp_id =n.emp_id and m.日期=n.日期
) as d
left join
(--晚班
select p.日期,p.工號,p.班別,f.上班時間1,f.下班時間1
from
(select field33 as 日期,field31 as 工號,field32 as 班別,field16 as 上班時間1 , field13 as 下班時間1 from T075F080 where field32='3') as p left join
(
select a.日期,a.工號,a.班別,a.上班時間1,b.下班時間1
from
(
select d.*
from
(
select c.日期,c.工號,c.班別,case when abs(datediff(mi,c.上班時間1,c.passTime))<@min and door_id='2' then c.passTime end as 上班時間1
from
(
select a.*,b.door_id,b.passTime,b.emp_id from
(select field33 as 日期,field31 as 工號,field32 as 班別,field16 as 上班時間1 , field13 as 下班時間1 from T075F080 where field32='3') as a left join
(select field21 as emp_id,convert(varchar(10),field9,111) as 日期,field3 as door_id,field9 as passTime from T093F040) as b
on a.工號=b.emp_id
) as c
) as d
where d.上班時間1 is not null
) as a left join (
select d.*
from
(
select c.日期,c.工號,c.班別,case when abs(datediff(mi,c.下班時間1,c.passTime))<@min and door_id='7' then c.passTime end as 下班時間1
from
(
select a.*,b.door_id,b.passTime,b.emp_id from
(select field33 as 日期,field31 as 工號,field32 as 班別,field16 as 上班時間1 , field13 as 下班時間1 from T075F080 where field32='3') as a left join
(select field21 as emp_id,convert(varchar(10),field9,111) as 日期,field3 as door_id,field9 as passTime from T093F040) as b
on a.工號=b.emp_id
) as c
) as d
where d.下班時間1 is not null
) as b
on a.日期=b.日期 and a.工號=b.工號
) as f
on p.日期=f.日期 and p.工號=f.工號
) as n
on d.日期=n.日期 and d.emp_id=n.工號
--排除因夜班跨天而引起的沒有上班反而有記錄(盡管都是null) 如2008/12/04 XZ0068
where not (d.班別='3' and n.上班時間1 is null and n.下班時間1 is null and d.上班時間2 is null and d.下班時間2 is null )
334
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
