sql - 递归查询？
问题描述
大家好，我目前一直在查询 ssrs 报告。
该数据库是帮助台的票证注册。所以每天来自 cds 团队的技术人员都会记录门票。如果他们无法解决问题，他们会将其发送给其他团队。我正在寻找一个查询谁在月底追溯每个月和每个团队的积压（非封闭票）。
例如：5 月 26 日 17 小时服务台开放票 5 月 27 日 8 小时票前往 Techsupport N2 6 月 3 日票前往 Tech N3 7 月 2 日票已关闭
我需要检索这个：Helpdesk May 0 June 0 N2 May 1 June 0 N3 May 0 June 1
数据库结构：
| 表上诉 |
|---|
| 诺佩尔 |
| C_equipe（最后一个受影响的团队） |
| 表操作 |
|---|
| 诺佩尔 |
| 无动于衷 |
| type_action （影响何时去其他团队 Clos 关闭时） |
| C_equipe（执行该操作的团队） |
我根据在网上找到的查询做了一些事情，但它可以在几周而不是几个月内工作。
DECLARE @DR DATETIME
DECLARE @SM INTEGER
SET @SM=5
SET @DR=getdate()
SELECT T.* FROM (SELECT A.IDT_APPEL AS NOTICKET,
DEBUT.C_ACTION AS C_DEBUT,
FIN.C_ACTION AS C_FIN,
RS.DF_SEM AS F_TEST,
RS.NUM_SEMAINE AS NUM_SEMAINE,
E.L_EQUIPE AS EQUIPETICKET,
EP.L_EQUIPE AS EQUIPESUP,
A.C_UTIL_INT,
CONCAT(util.N_UTIL,' ',util.PRE_UTIL) AS INTERVENANT,
CASE
WHEN Datepart(ISO_WEEK, FIN.D_CREATION) = RS.NUM_SEMAINE
AND Datediff(DAY, FIN.D_CREATION, RS.DF_SEM) < 7
AND FIN.C_TACTION = 'SYS_EQUIPE'
AND Isnull(UTIL.C_ISIPARC, '0') <> '666' THEN 1
ELSE 0
END AS COND_TR,
CASE
WHEN Datepart(ISO_WEEK, FIN.D_CREATION) = RS.NUM_SEMAINE
AND Datediff(DAY, FIN.D_CREATION, RS.DF_SEM) < 7
AND FIN.C_TACTION = 'SYS_EQUIPE'
AND Isnull(UTIL.C_ISIPARC, '0') = '666' THEN 1
ELSE 0
END AS COND_REJ,
CASE
WHEN FIN.C_TACTION IN('SYS_CLOS_TECH','SYS_CLOS')
AND Datepart(ISO_WEEK, FIN.D_CREATION) = RS.NUM_SEMAINE
AND Datediff(DAY, FIN.D_CREATION, RS.DF_SEM) < 7 THEN 1
ELSE 0
END AS COND_CLOS,
CASE
WHEN Datepart(ISO_WEEK, DEBUT.D_CREATION) = RS.NUM_SEMAINE
AND Datediff(DAY, DEBUT.D_CREATION, RS.DF_SEM) < 7 THEN 1
ELSE 0
END AS NB_TICKET_ENTRE,
CASE
WHEN ( ( FIN.D_CREATION IS NULL
OR FIN.D_CREATION > RS.DF_SEM )
AND DEBUT.D_CREATION < RS.DF_SEM ) THEN ( CASE
WHEN Datediff(DAY, DEBUT.D_CREATION, RS.DF_SEM) - 2 < 5 THEN '05'
WHEN Datediff(DAY, DEBUT.D_CREATION, RS.DF_SEM) - 2 >= 5
AND Datediff(DAY, DEBUT.D_CREATION, RS.DF_SEM) - 2 < 10 THEN '10'
WHEN Datediff(DAY, DEBUT.D_CREATION, RS.DF_SEM) - 2 >= 10
AND Datediff(MONTH, DEBUT.D_CREATION, RS.DF_SEM) < 1 THEN '30'
WHEN Datediff(MONTH, DEBUT.D_CREATION, RS.DF_SEM) >= 1 THEN '30+'
ELSE NULL
END )
ELSE NULL
END AS F_NONCLOS,
CASE
WHEN Datepart(ISO_WEEK, FIN.D_CREATION) = RS.NUM_SEMAINE
AND Datediff(DAY, FIN.D_CREATION, RS.DF_SEM) < 7 THEN ( CASE
WHEN Datediff(DAY, DEBUT.D_CREATION, FIN.D_CREATION) < 1 THEN '01'
WHEN Datediff(DAY, DEBUT.D_CREATION, FIN.D_CREATION) >= 1
AND Datediff(DAY, DEBUT.D_CREATION, FIN.D_CREATION) < 2 THEN '02'
WHEN Datediff(DAY, DEBUT.D_CREATION, FIN.D_CREATION) >= 2
AND Datediff(DAY, DEBUT.D_CREATION, FIN.D_CREATION) < 5 THEN '05'
WHEN Datediff(DAY, DEBUT.D_CREATION, FIN.D_CREATION) >= 5
AND Datediff(DAY, DEBUT.D_CREATION, FIN.D_CREATION) < 15 THEN '15'
WHEN Datediff(DAY, DEBUT.D_CREATION, FIN.D_CREATION) >= 15 THEN '15+'
ELSE NULL
END )
ELSE NULL
END AS F_CLOS,
DEBUT.D_CREATION AS DEBUT,
FIN.D_CREATION AS FIN,
RS.YEAR AS YEARS
FROM APPEL A
INNER JOIN (SELECT B.NO_APPEL,
B.C_ACTION,
B.D_CREATION,
B.C_EQUIPE,
B.C_TACTION,
Lead(B.C_ACTION, 1)
OVER (
PARTITION BY B.NO_APPEL
ORDER BY B.C_ACTION) AS FIN_C_ACTION
FROM ACTIONS B WITH (NOLOCK)
WHERE B.C_TACTION IN ( 'SYS_EQUIPE', 'SYS_CLOS_TECH', 'SYS_REOUVERT','SYS_CLOS' )
AND ( CAST(B.D_CREATION AS DATE)< Dateadd(week, Datediff(week, 0, Dateadd(WEEK, 0, @DR)), 0) )) DEBUT
ON ( A.NO_APPEL = DEBUT.NO_APPEL
AND DEBUT.C_EQUIPE IN('ETUD_MET_BILIZI')
AND DEBUT.C_TACTION IN( 'SYS_EQUIPE', 'SYS_REOUVERT' ) )
LEFT JOIN ACTIONS FIN WITH (NOLOCK)
ON ( FIN.C_ACTION = DEBUT.FIN_C_ACTION )
LEFT JOIN UTILISATEUR UTIL WITH (NOLOCK)
ON ( UTIL.C_UTIL = FIN.C_UTIL )
INNER JOIN EQUIPE E WITH (NOLOCK)
ON DEBUT.C_EQUIPE = E.C_EQUIPE
INNER JOIN EQUIPE EP WITH (NOLOCK)
ON E.C_EQUIPE_PERE = EP.C_EQUIPE
INNER JOIN (SELECT DISTINCT Datepart(ISO_WEEK, P.D_JOUR) AS Num_semaine,
Dateadd(SECOND, 59, Dateadd(MINUTE, 59, Dateadd(HOUR, 23, Dateadd(DAY, -1, Dateadd(week, Datediff(week, 0, P.D_JOUR) + 1, 0))))) AS DF_SEM,
YEAR(P.D_JOUR) AS YEAR
FROM PLANNINGS P WITH (NOLOCK)
INNER JOIN TCALEND TC WITH (NOLOCK)
ON TC.C_TCALEND = P.C_TCALEND
AND TC.F_TCALENDSTD = 1
WHERE ( C_TPERIOD <> 3
AND C_TPERIOD <> 9 )) RS
ON ( ( FIN.D_CREATION > Dateadd(DAY, -7, RS.DF_SEM)
OR FIN.D_CREATION IS NULL )
AND (CAST( RS.DF_SEM AS DATE) >= Dateadd(week, Datediff(week, 0, Dateadd(WEEK, - @SM, @DR)), 0)
AND CAST(RS.DF_SEM AS DATE) <= Dateadd(week, Datediff(week, 0, Dateadd(WEEK, 0,@DR)), 0) ) )
WHERE (A.C_NATURE = 'INC' OR A.C_NATURE = 'DMD')
AND ( A.D_CLOTTECH >= Dateadd(week, Datediff(week, 0, Dateadd(WEEK, - @SM, @DR)), 0)
OR A.D_CLOTTECH IS NULL )
UNION
SELECT DISTINCT NULL IDT_APPEL,
NULL C_DEBUT,
NULL C_FIN,
Dateadd(SECOND, 59, Dateadd(MINUTE, 59, Dateadd(HOUR, 23, Dateadd(DAY, -1, Dateadd(week, Datediff(week, 0, P.D_JOUR) + 1, 0))))) AS DF_SEM,
Datepart(ISO_WEEK, P.D_JOUR) AS Num_semaine,
NULL EQUIPETICKET,
NULL EQUIPESUP,
NULL C_UTIL_INT,
NULL INTERVENANT,
0 COND_TR,
0 COND_REJ,
0 COND_CLOS,
0 NB_TICKET_ENTRE,
NULL F_NONCLOS,
NULL F_CLOS,
NULL,
NULL,
--YEAR(P.D_JOUR) AS YEARS
case when Datepart(ISO_WEEK, P.D_JOUR)=1 then YEAR(getdate()) ELSE YEAR(P.D_JOUR) end AS YEARS
FROM PLANNINGS P WITH (NOLOCK)
INNER JOIN TCALEND TC WITH (NOLOCK)
ON TC.C_TCALEND = P.C_TCALEND
AND TC.F_TCALENDSTD = 1
WHERE ( C_TPERIOD <> 3
AND C_TPERIOD <> 9 )
AND Dateadd(SECOND, 59, Dateadd(MINUTE, 59, Dateadd(HOUR, 23, Dateadd(DAY, -1, Dateadd(week, Datediff(week, 0, P.D_JOUR) + 1, 0))))) >= Dateadd(week, Datediff(week, 0, Dateadd(WEEK, - @SM, @DR)), 0)
AND Dateadd(SECOND, 59, Dateadd(MINUTE, 59, Dateadd(HOUR, 23, Dateadd(DAY, -1, Dateadd(week, Datediff(week, 0, P.D_JOUR) + 1, 0))))) <= Dateadd(week, Datediff(week, 0, Dateadd(WEEK, 0, @DR)), 0)) T ORDER BY T.YEARS ASC
谢谢
解决方案
推荐阅读
- javascript - D3.js x 轴以天为单位
- testing - 空手道框架 - 有没有办法在执行期间使用我自己的 application.properties 文件或用我自己的值替换单个属性？
- python - 如何在python中对彼此靠近的集合中的部分重复进行排序
- testng - 有没有办法在超类的方法之前将类变量放入 testNG
- css - 渐变以跨越整个背景，同时保持图像原始大小
- python - 为什么这个 tensorflow 训练需要这么长时间？
- excel - 比较两个列表，如果找到，则将数据添加到单元格中
- javascript - 使用 Spring Boot 将 JavaScript 对象的请求发布到 JSON
- vba - VBA 将 Xmlhttp 对象作为 x-www-form-urlencoded 发送
- python - 为什么子进程在给定任务期间失败？