这是我当前的选择查询。我一直在尝试将“最终账龄”更新为表中的字段,但似乎无法弄清楚。另外,DATEADD
如果有人知道如何改写我会很乐意听和学的话,我一直在手动将时间调整为UTC
我当前的查询日期区分两个日期,并从中删除周末和假日。我只需要更新,[FINAL AGING]
但找不到解决方法。任何帮助将不胜感激。
SELECT
TICKETNUMBER,
CONVERT(DATE,CREATEDON),
CONVERT(DATE,DATEAPPROVED),
[WITH WEEKEND],
[WITHOUT WEEKEND],
[HOLIDAY COUNT],
[WITHOUT WEEKEND] - [HOLIDAY COUNT] AS [FINAL AGING]
FROM
(
SELECT
B.TICKETNUMBER,
A.CREATEDON,
DATEAPPROVED,
DATEDIFF(DD, DATEADD(HOUR, 8, A.CREATEDON), DATEADD(HOUR, 8, DATEAPPROVED)) AS [WITH WEEKEND],
DATEDIFF(DD, DATEADD(HOUR, 8, A.CREATEDON), DATEADD(HOUR, 8, DATEAPPROVED)) -
(DATEDIFF(WK, DATEADD(HOUR, 8, A.CREATEDON), DATEADD(HOUR, 8, DATEAPPROVED)) * 2) -
CASE WHEN DATEPART(DW, DATEADD(HOUR, 8, A.CREATEDON)) = 1 THEN 1 ELSE 0 END +
CASE WHEN DATEPART(DW, DATEADD(HOUR, 8, DATEAPPROVED)) = 1 THEN 1 ELSE 0 END AS [WITHOUT WEEKEND],
(SELECT COUNT(DATE) FROM HOLIDAYMAINTENANCE
WHERE CONVERT(DATE, DATEADD(HOUR, 8, DATE)) BETWEEN CONVERT(DATE,DATEADD(HOUR, 8, A.CREATEDON))
AND CONVERT(DATE, DATEADD(HOUR, 8, DATEAPPROVED))) AS [HOLIDAY COUNT]
FROM REQUESTMONITORING A JOIN INCIDENT B ON B.INCIDENTID = A.REQUESTID
WHERE DATEAPPROVED IS NOT NULL
) AS [TABLE]
您将update
使用join
:
with toupdate as (
<your query here>
)
update
set [Final Aging] = toupdate.[Final Aging]
from TableToUpdate ttu join
toupdate
on ttu.TicketNumber = toupdate.TicketNumber;
我猜测更新的关键是Final Aging
。另外,您没有提及要更新的表,但是您需要将其加入-即使它是要定义的表toupdate
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句