我有两组查询。首先，我计算在给定时间段内完成事务处理的中位数（秒）。第二个查询将第一个查询中检索到的中位数时间转换为日期格式（DD：HH：MI：SS）。我将第一个查询的输出结果（计算的中位数）手动输入到第二个查询中，以获得所需的结果（天小时分钟秒），此方法效果良好。但我希望能够将这两个查询合并为一个查询，而无需在第二个查询中手动输入时间。请参见以下查询：

SELECT  TxnStartDT, TxnCompleteDT, TxnDuration,
PERCENTILE_CONT(.5) WITHIN GROUP (ORDER BY TxnDuration) OVER() AS MEDIAN 
FROM(
SELECT DISTINCT TxnStartDT, TxnCompleteDT, 
DATEDIFF(SECOND,  TxnStartDT, TxnCompleteDT) AS TxnDuration
FROM MyTable
WHERE (TxnStartDT >='2023-01-02 00:00' and TxnStartDT <= '2023-01-08 23:59') and  TxnCompleteDT is not null) AS D

输出中位数7333

DECLARE @MD DATETIME = DATEADD(SECOND, 7333,  0)
SELECT --CAST(DATEPART(MONTH, @VARDT) - 1 AS VARCHAR(2)) + ' month(s) '
+ CAST(DATEPART(DAY, @MD) - 1 AS VARCHAR(2)) + ' day(s) ' 
+ CAST(DATEPART(HOUR, @MD) AS VARCHAR(2)) + ' hour(s) '
+ CAST(DATEPART(MINUTE, @MD) AS VARCHAR(2)) + ' minute(s) ' 
+ CAST(DATEPART(SECOND, @MD) AS VARCHAR(2)) + ' second(s)' as 'Median Time'

输出0天2小时2分钟13秒
我尝试使用CAST函数，这样我就可以运行一次查询，而不必分别运行两个查询。

SELECT  TxnStartDT, TxnCompleteDT, TxnDuration,
PERCENTILE_CONT(.5) WITHIN GROUP (ORDER BY TxnDuration) OVER() AS MEDIAN 
FROM(
SELECT DISTINCT TxnStartDT, TxnCompleteDT, 
DATEDIFF(SECOND,  TxnStartDT, TxnCompleteDT) AS TxnDuration
FROM MyTable
WHERE (TxnStartDT >='2023-01-02 00:00' and TxnStartDT <= '2023-01-08 23:59') and  TxnCompleteDT is not null) AS D

DECLARE @MD DATETIME = CAST(DATEADD(SECOND, MEDIAN,  0))
SELECT --CAST(DATEPART(MONTH, @VARDT) - 1 AS VARCHAR(2)) + ' month(s) '
+ CAST(DATEPART(DAY, @MD) - 1 AS VARCHAR(2)) + ' day(s) ' 
+ CAST(DATEPART(HOUR, @MD) AS VARCHAR(2)) + ' hour(s) '
+ CAST(DATEPART(MINUTE, @MD) AS VARCHAR(2)) + ' minute(s) ' 
+ CAST(DATEPART(SECOND, @MD) AS VARCHAR(2)) + ' second(s)' as 'Median Time'

错误

消息1035，级别15，状态10，第32行
“CAST”附近的语法不正确，应为“AS”。
消息137，级别15，状态2，第34行
必须声明标量变量“@MD”。