寻找高价值交易

Question

我的桌子结构如下-

Account Number | Transaction Type | Amount | Date

交易类型可以是信用或借方。我需要检查信用卡或借方交易金额是否大于某一阈值。如果是，请检查金额是否大于收入* 10 (存储在其他表中)。

可能有多种情况(考虑信贷门槛>= 10000，借方门槛>= 20000，收入= 1000) -

1. Account Number | Transaction Type | Amount | Date
   A1             | Credit           | 10000   | 11-11-2017

本例中不存在借方行，但A1满足所有条件

2. Account Number | Transaction Type | Amount | Date
   A2             | Debit            | 20000   | 12-11-2017

本例中不存在信用行，但A2满足所有条件

3. Account Number | Transaction Type | Amount | Date
   A3             | Credit           | 10000   | 13-11-2017
   A3             | Debit            | 5000    | 13-11-2017

在这个例子中，贷方行和借方行都存在，但是A3满足信用条件

4. Account Number | Transaction Type | Amount | Date
   A4             | Credit           | 5000   | 14-11-2017
   A4             | Debit            | 20000  | 14-11-2017

在本例中，贷方行和借方行都存在，但A4满足借方条件。

5. Account Number | Transaction Type | Amount | Date
   A5             | Credit           | 10000   | 15-11-2017
   A5             | Debit            | 20000   | 15-11-2017

在这个例子中，贷方行和借方行都存在，但是A5应该满足借方条件，因为它有更大的价值(在这种情况下需要取最大值)。

6. Account Number | Transaction Type | Amount | Date
   A6             | Credit           | 10000   | 16-11-2017
   A6             | Debit            | 20000   | 16-11-2017

在本例中，假设Income = 3000，这个示例中同时存在Credit行，但是A6不应该满足任何条件，因为收入*10的价值大于值。

我已经使用了联合与左和右连接，以找到两者的信用/借方值，但找出是否有更好的方式来编写这个脚本。如果任何贷方/借方价值不存在，将其视为零。我正在使用SQL server 2012。

到目前为止，脚本已经开发(在很高的层次上)：

SELECT
temp.Number as Number,
temp.Amount1 as CrAmt,
temp.Amount2 as DrAmt
FROM
(
SELECT
    t1.Account_Number AS Number,
    t1.Amount AS Amount1,
    isnull(t2.Amount, 0) as Amount2
FROM
    TableName AS t1 WITH (NOLOCK) LEFT JOIN
    TableName AS t2 WITH (NOLOCK) on isnull(t2.Transaction_Type, 'Debit') = 'Debit' and isnull(t2.Account_Number,t1.Account_Number) = t1.Account_Number
WHERE
    t1.Transaction_Type = 'Credit'

UNION

SELECT
    t1.Account_Number AS Number,
    t1.Amount AS Amount1,
    isnull(t2.Amount, 0) as Amount2
FROM
    TableName AS t1 WITH (NOLOCK) RIGHT JOIN
    TableName AS t2 WITH (NOLOCK) on isnull(t2.Transaction_Type, 'Debit') = 'Debit' and isnull(t2.Account_Number,t1.Account_Number) = t1.Account_Number
WHERE
    t1.Transaction_Type = 'Credit'
) temp
where
(temp.Amount1 >= 10000 and temp.Amount1>= Income * 10) OR (temp.Amount2 >= 20000 and temp.Amount2>= Income * 10)

Answer 1

这里有一个更干净的方法

DECLARE @income       decimal(18, 2) = 1000*10,
        @CrThreshold  decimal(18, 2) = 10000,
        @DrThreshold  decimal(18, 2) = 20000

SELECT AccountNumber, CrAmt, DrAmt
FROM (
  SELECT AccountNumber, 
         CrAmt = SUM(CASE WHEN TransactionType = 'Credit' THEN Amount ELSE 0 END), 
         DrAmt = SUM(CASE WHEN TransactionType = 'Debit' THEN Amount ELSE 0 END)
  FROM TableName
  GROUP BY AccountNumber, [Date]) T
WHERE (CrAmt >= @CrThreshold AND CrAmt >= @income)
OR (DrAmt >= @DrThreshold AND DrAmt >= @income)