I did it.... but you can see... the no name column will be with cte , but you can see that performance is not good. maybe something is wrong
SELECT CONVERT(VARCHAR(8), DATEADD(DAY, -START_TIME_DAY, (CONVERT(VARCHAR(11), GETDATE(), 120) + CONVERT(VARCHAR(8), GETDATE(), 108))), 112)
,A.WORK_TIME_ID
,A.SHIFT_ID
--,CASE WHEN IS_WORK = 'N' THEN DATEDIFF(SECOND, CAST(START_TIME AS DATETIME) + START_TIME_DAY, CONVERT(DATETIME, CONVERT(VARCHAR(8), CONVERT(VARCHAR(8), GETDATE(), 108), 114)) + TIME_DAY ) ELSE 0 END
,(SELECT (ISNULL(SUM(TIME_MINUTE),0) * 60)
FROM TB_CODE_WORK_TIMEDTL WHERE WORK_TIME_ID = WORK_TIME_ID AND SHIFT_ID = SHIFT_ID AND TIME_SEQ < TIME_SEQ AND IS_WORK = 'N')
,B.WORK_DATE
,B.WORK_TIME_ID
,B.SHIFT_ID
,CASE WHEN IS_WORK = 'N' THEN DATEDIFF(SECOND, CAST(START_TIME AS DATETIME) + START_TIME_DAY, CONVERT(DATETIME, CONVERT(VARCHAR(8), CONVERT(VARCHAR(8), GETDATE(), 108), 114)) + TIME_DAY ) ELSE 0 END
FROM TB_CODE_WORK_TIMEDTL A
CROSS APPLY
(SELECT TOP 1 WORK_DATE, SHIFT_ID, A.WORK_TIME_ID FROM (
SELECT WC.WORK_DATE + RIGHT('000' + CONVERT(VARCHAR, MAX(WTD.TIME_SEQ)),3) "WORK_DT", WC.WORK_DATE, WTD.WORK_TIME_ID, WTD.SHIFT_ID
FROM TB_WORK_CALENDAR WC LEFT JOIN TB_CODE_WORK_TIMEDTL WTD
ON WC.WORK_TIME_ID = WTD.WORK_TIME_ID
WHERE WORK_DATE BETWEEN WORK_DATE AND CONVERT(VARCHAR(8), DATEADD(DAY, -START_TIME_DAY, (CONVERT(VARCHAR(11), GETDATE(), 120) + CONVERT(VARCHAR(8), GETDATE(), 108))), 112)
GROUP BY WC.WORK_DATE, WTD.WORK_TIME_ID, WTD.SHIFT_ID
) A
WHERE A.WORK_DT < CONVERT(VARCHAR(8), DATEADD(DAY, -START_TIME_DAY, (CONVERT(VARCHAR(11), GETDATE(), 120) + CONVERT(VARCHAR(8), GETDATE(), 108))), 112) + RIGHT('000' + CONVERT(VARCHAR, TIME_SEQ),3)
ORDER BY A.WORK_DT DESC ) B
WHERE A.WORK_TIME_ID = (SELECT WORK_TIME_ID FROM TB_WORK_CALENDAR WHERE WORK_DATE =CONVERT(VARCHAR(8), DATEADD(DAY, -START_TIME_DAY, (CONVERT(VARCHAR(11), GETDATE(), 120) + CONVERT(VARCHAR(8), GETDATE(), 108))), 112))
AND CONVERT(VARCHAR(8), GETDATE(), 108) >= CAST(START_TIME AS DATETIME) + START_TIME_DAY
AND CONVERT(VARCHAR(8), GETDATE(), 108) < CAST(END_TIME AS DATETIME) + END_TIME_DAY
(1 row(s) affected)
Table '#03B3C554'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 21 ms.
(1 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_WORK_CALENDAR'. Scan count 5, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_CODE_WORK_TIMEDTL'. Scan count 3, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 137 ms.