分析健康师消耗项目数差异.sql
3.39 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
-- ============================================
-- 分析健康师消耗项目数差异
-- 员工ID: 18566028067 (李芳)
-- ============================================
-- 1. 接口统计逻辑（使用耗卡时间，时间范围：2025-11-01 到 2025-11-25 11:55:32）
SELECT
jksyj.jkszh as EmployeeId,
CAST(SUM(jksyj.F_kdpxNumber) AS DECIMAL(18,2)) as ProjectCount_接口逻辑
FROM lq_xh_jksyj jksyj
INNER JOIN lq_xh_hyhk hyhk ON jksyj.glkdbh = hyhk.F_Id
WHERE jksyj.jkszh = '18566028067'
AND jksyj.F_IsEffective = 1
AND hyhk.F_IsEffective = 1
AND hyhk.hksj >= '2025-11-01 00:00:00'
AND hyhk.hksj <= '2025-11-25 11:55:32'
GROUP BY jksyj.jkszh;
-- 2. 整个11月的统计（使用业绩时间）
SELECT
jksyj.jkszh as EmployeeId,
CAST(SUM(jksyj.F_kdpxNumber) AS DECIMAL(18,2)) as ProjectCount_整个11月
FROM lq_xh_jksyj jksyj
WHERE (jksyj.jks = '18566028067' OR jksyj.jkszh = '18566028067')
AND jksyj.F_IsEffective = 1
AND DATE_FORMAT(jksyj.yjsj, '%Y%m') = '202511'
GROUP BY jksyj.jkszh;
-- 3. 查看11月25日之后的记录（可能导致差异的原因）
SELECT
jksyj.jkszh as EmployeeId,
jksyj.F_kdpxNumber as 项目数,
jksyj.yjsj as 业绩时间,
hyhk.hksj as 耗卡时间,
hyhk.F_IsEffective as 耗卡记录是否有效,
jksyj.F_IsEffective as 业绩记录是否有效
FROM lq_xh_jksyj jksyj
LEFT JOIN lq_xh_hyhk hyhk ON jksyj.glkdbh = hyhk.F_Id
WHERE (jksyj.jks = '18566028067' OR jksyj.jkszh = '18566028067')
AND jksyj.F_IsEffective = 1
AND (
(jksyj.yjsj >= '2025-11-25 11:55:32' AND jksyj.yjsj < '2025-12-01')
OR (hyhk.hksj >= '2025-11-25 11:55:32' AND hyhk.hksj < '2025-12-01')
)
ORDER BY jksyj.yjsj;
-- 4. 查看耗卡记录无效但业绩记录有效的记录（可能导致差异的原因）
SELECT
jksyj.jkszh as EmployeeId,
jksyj.F_kdpxNumber as 项目数,
jksyj.yjsj as 业绩时间,
hyhk.hksj as 耗卡时间,
hyhk.F_IsEffective as 耗卡记录是否有效,
jksyj.F_IsEffective as 业绩记录是否有效
FROM lq_xh_jksyj jksyj
LEFT JOIN lq_xh_hyhk hyhk ON jksyj.glkdbh = hyhk.F_Id
WHERE (jksyj.jks = '18566028067' OR jksyj.jkszh = '18566028067')
AND jksyj.F_IsEffective = 1
AND (hyhk.F_IsEffective != 1 OR hyhk.F_IsEffective IS NULL)
AND jksyj.yjsj >= '2025-11-01'
AND jksyj.yjsj < '2025-12-01'
ORDER BY jksyj.yjsj;
-- 5. 查看耗卡时间和业绩时间不一致的记录（可能导致差异的原因）
SELECT
jksyj.jkszh as EmployeeId,
jksyj.F_kdpxNumber as 项目数,
jksyj.yjsj as 业绩时间,
hyhk.hksj as 耗卡时间,
DATEDIFF(jksyj.yjsj, hyhk.hksj) as 时间差_天,
CASE
WHEN hyhk.hksj >= '2025-11-01 00:00:00' AND hyhk.hksj <= '2025-11-25 11:55:32' THEN '在接口时间范围内'
ELSE '不在接口时间范围内'
END as 是否在接口时间范围
FROM lq_xh_jksyj jksyj
INNER JOIN lq_xh_hyhk hyhk ON jksyj.glkdbh = hyhk.F_Id
WHERE (jksyj.jks = '18566028067' OR jksyj.jkszh = '18566028067')
AND jksyj.F_IsEffective = 1
AND hyhk.F_IsEffective = 1
AND (
(jksyj.yjsj >= '2025-11-01' AND jksyj.yjsj < '2025-12-01')
OR (hyhk.hksj >= '2025-11-01' AND hyhk.hksj < '2025-12-01')
)
AND (
jksyj.yjsj < '2025-11-01'
OR jksyj.yjsj >= '2025-12-01'
OR hyhk.hksj < '2025-11-01'
OR hyhk.hksj >= '2025-12-01'
OR DATEDIFF(jksyj.yjsj, hyhk.hksj) != 0
)
ORDER BY jksyj.yjsj;