USE [cool]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

WITH dedup_detail AS (
    SELECT
        CONVERT(char(10), d.io_time, 23) AS ymd,
        LTRIM(RTRIM(d.order_no)) AS order_no,
        MAX(ISNULL(d.teu, 0)) AS teu_total
    FROM dbo.asr_wrk_detl_log AS d
    WHERE d.io_time >= DATEADD(day, -11, CONVERT(date, GETDATE()))
      AND d.io_time < DATEADD(day, 1, CONVERT(date, GETDATE()))
      AND d.order_no IS NOT NULL
      AND LTRIM(RTRIM(d.order_no)) <> ''
    GROUP BY
        CONVERT(char(10), d.io_time, 23),
        LTRIM(RTRIM(d.order_no))
)
SELECT
    ymd,
    SUM(teu_total) AS teu_total
FROM dedup_detail
GROUP BY ymd
GO
