将我的嵌套Select语句转换为Update语句

Mikee 发表于 Dev

迈克

这是我当前的选择查询。我一直在尝试将“最终账龄”更新为表中的字段，但似乎无法弄清楚。另外，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] 删除。