SQL Query: Select Container Information by CTNREF and Date Range

This SQL query selects various fields from the CTNPRES and DCTNREC tables, including CTNREF, CTNNO, RSUCD, INYARD, ESTNO, CTNLOC, CTNOPE, WWLB, OPECFCD, REPEDDT, and DT. It joins the two tables on the CTNREF field and filters the results based on certain conditions including SURPCACD, OWNCFCD, REPEDDT, and CTNNO. The results are then ordered by REPEDDT in descending order.

SELECT
	CTNPRES.CTNREF,
	CTNPRES.CTNNO,
	DCTNREC.RSUCD,
	CTNPRES.INYARD AS YARD,
	DCTNREC.ESTNO,
	CTNPRES.CTNLOC,
	CTNPRES.CTNOPE,
	DCTNREC.WWLB,
	DCTNREC.OPECFCD,
	DCTNREC.REPEDDT,
	CASE CTNPRES.REMARK WHEN 'AV' THEN (SELECT TOP 1 JBPLSDT FROM MVJBPL WHERE CTNREF = CTNPRES.CTNREF AND REMARK = 'AV' ORDER BY JBPLSDT DESC) ELSE DCTNREC.REPEDDT END DT
FROM
	CTNPRES 
	INNER JOIN DCTNREC ON CTNPRES.CTNREF = DCTNREC.CTNREF 
WHERE
	DCTNREC.SURPCACD = '4'
	AND DCTNREC.OWNCFCD IN ( '1', '2', '3' )
	AND DCTNREC.REPEDDT IS NOT NULL
	AND DCTNREC.REPEDDT >= '2023-04-21 00:00' 
	AND DCTNREC.REPEDDT <= '2023-04-25 11:01' 
	AND CTNPRES.CTNNO= 'OOLU6897794'
ORDER BY
	DCTNREC.REPEDDT DESC