最近手上的Bug不多，终于有时间把OneNote里那堆脚本拾掇拾掇。

脚本背景：需要处理的数据有Customer/Order/Invoice；这三方面的信息要发送到SAP终端去算税。从字面上你应该可以理解它们之间的关系咯，Invoice是基于Customer/Order，如果Invoice的提交时间早于Customer/Order,那么就属于异常数据，需要清理。之所以有两种表来存储Customer/Order,即frs_rmca_customer,cf_customer 是因为前后使用了两种application向SAP终端提交数据。

NOTE: COLLATE DATABASE_DEFAULT 是我第一次玩，COLLATE 是用于定义排序规则的。如果这里不用它，就会出现“Cannot resolve collation conflict for equal to operation“. 因为等号两边使用的排序规则是不同的。

with AffectedInvoice#559558 as (

select i.bi_BillableAcctId, i.c_InvoiceNumber,MIN(i.dt_InsertedDateTime) as InvoiceInserted

,i.nvc_CustomerId, MIN(c.dt_InsertedDateTime) as CustomerInserted

, i.nvc_OrderId , MIN(o.dt_InsertedDateTime) as OrderInserted

from [Billing1_bizop]..cf_invoice i (nolock)

join [Subscription1_bizop]..cf_customer c (nolock)

on i.bi_BillableAcctId = c.bi_BillableAcctId

and i.nvc_CustomerId COLLATE DATABASE_DEFAULT = c.nvc_CustomerId COLLATE DATABASE_DEFAULT

join [Subscription1_bizop]..cf_order o (nolock)

on i.bi_BillableAcctId = o.bi_BillableAcctId

and i.nvc_OrderId = o.nvc_OrderId

where i.si_Status = 4

and c.si_Status = 4 and o.si_Status = 4

group by i.bi_BillableAcctId, i.c_InvoiceNumber,i.nvc_CustomerId, i.nvc_OrderId

having (MIN(i.dt_InsertedDateTime) < MIN(c.dt_InsertedDateTime)

or MIN(i.dt_InsertedDateTime) < MIN(o.dt_InsertedDateTime)))

select * from AffectedInvoice#559558 i

where not exists(

                select 1 from [Subscription1_bizop]..frs_rmca_order fro (nolock)

                where fro.nvc_order_id = i.nvc_OrderId

                and fro.si_status = 3

)

and not exists(

                select 1 from [Subscription1_bizop]..frs_rmca_customer frc (nolock)

                where frc.nvc_customer_id COLLATE DATABASE_DEFAULT = i.nvc_CustomerId COLLATE DATABASE_DEFAULT

                and frc.si_status = 3

)