Input the output of one query into second query-CodePudding

I have two sets of queries. First, I calculate the median in seconds to complete transactions for a given period of time. The second query converts the median time retrieved in the first query to date format (DD:HH:MI:SS). I manually input the output result (calculated median) of the first query into the second query to get my desired result (days hours minutes seconds), this works well. But I wanted a situation where I could combine these two queries into one without manually inputting the time in the second query. See the queries below:

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

Output MEDIAN 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'

Output 0 day(s) 2 hour(s) 2 minute(s) 13 second(s)

I tried to use the CAST function so that I could run the queries once without running the two queries separately. I got errors.

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'

Errors

Msg 1035, Level 15, State 10, Line 32
Incorrect syntax near 'CAST', expected 'AS'.
Msg 137, Level 15, State 2, Line 34
Must declare the scalar variable "@MD".

CodePudding user response：

try this

;with cte as(SELECT  TxnStartDT, TxnCompleteDT, TxnDuration,
cast (DATEADD(SECOND,PERCENTILE_CONT(.5) WITHIN GROUP (ORDER BY TxnDuration) OVER() ,0)as datetime) 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)

SELECT  
  CAST(DATEPART(DAY, MEDIAN) - 1 AS VARCHAR(2))   ' day(s) ' 
  CAST(DATEPART(HOUR, MEDIAN) AS VARCHAR(2))   ' hour(s) '
  CAST(DATEPART(MINUTE, MEDIAN) AS VARCHAR(2))   ' minute(s) ' 
  CAST(DATEPART(SECOND, MEDIAN) AS VARCHAR(2))   ' second(s)' as 'Median Time' 
from cte