1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
| 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
|
|
