I know there were several articles about this topic already and I went through them but I still am not able to get this to run properly. I also used DATEDIFF_BIG
, but then I get this error:
DATEDIFF_BIG is not a recognized built-in function name.
This used to be able to run but I think there was a SQL server update and now all of the TAT queries are breaking.
SELECT
MSTR.FIRST_OF_MTH AS [MONTH],
COALESCE(R.[AREA],T.[AREA]) AS [AREA],
COALESCE(R.NUM_REQUESTS,0) AS NUM_REQUESTS,
COALESCE(R.DURATION_REQ,0) AS DURATION_REQ,
COALESCE(T.NUM_TRANS,0) AS NUM_TRANS,
COALESCE(T.DURATION_TRANS,0) AS DURATION_TRANS
FROM
(
SELECT FIRST_OF_MTH
FROM [EPICDW].[DBO].[DATE_EXT]
WHERE (YEAR([DATE_EXT].[THEDATE]) BETWEEN YEAR(GETDATE()) - 1 AND YEAR(GETDATE()))
GROUP BY FIRST_OF_MTH
) MSTR
LEFT JOIN
(
--transactions
SELECT
DATEADD(month, DATEDIFF(month, 0, [CLOSE_DT]), 0) AS [MONTH],
[TEAM] AS AREA,
SUM(1) AS NUM_TRANS,
SUM(B.[Calendar_Duration]) AS DURATION_TRANS
FROM [dbo].[v_tab_config_bl_trans_comb]
CROSS APPLY fn_Ben_Ops_Turnaround ([OPEN_DT], [CLOSE_DT]) B
WHERE Year([CLOSE_DT]) BETWEEN YEAR(GETDATE()) - 1 AND YEAR(GETDATE())
GROUP BY
DATEADD(month, DATEDIFF(month, 0, [CLOSE_DT]), 0),
[TEAM]
) T
ON MSTR.FIRST_OF_MTH = T.[MONTH]
LEFT JOIN
(
--requests
SELECT
DATEADD(month, DATEDIFF(month, 0, [CLOSED_DT]), 0) AS [MONTH],
TEAM AS AREA,
SUM(1) AS NUM_REQUESTS,
SUM([DUR_CAL]) AS DURATION_REQ
FROM [dbo].[v_tab_config_requests]
WHERE year([CLOSED_DT]) BETWEEN YEAR(GETDATE()) - 1 AND YEAR(GETDATE())
GROUP BY DATEADD(month, DATEDIFF(month, 0, [CLOSED_DT]), 0), TEAM
) R
ON MSTR.FIRST_OF_MTH = R.[MONTH]
AND T.AREA = R.AREA
question from:
https://stackoverflow.com/questions/65835968/sql-server-update-caused-datediff-to-not-work 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…