负余额

Question

我有三张桌子。账号。转换和平衡。

Trans表中的Sum(金额)等于每个帐户在任何给定时间的余额。

我需要一个查询，检查余额是负的连续3个月或更长时间，从今天的日期。(所以每当我要运行这个的时候，sysdate )。

我想知道如何查询它将在哪里显示账号和余额，如果它连续3个月或更长时间为负值，而不管交易发生的时间。

表：

Trans Table
Select * from trans where accountid = 1;

Transdate  Merchant    Amount   AccountID
10/1/16    Employer    50       1
10/4/16    Walmart     -20      1
10/7/16    Kroger      -50      1

现在，他的账户在2016年10月7日的-$20上变为负值。

Transdate  Merchant    Amount   AccountID
12/01/16   Employer    10       1

他的帐户仍然是负面的。如果我今天(2017年1月16日)或以后运行查询，他的帐户应该会被提取，因为他的余额至少在90天内仍为负。

Balance Table

每个账号只保留1条记录。截至今天，它显示了以下内容：

AccountID    Balance    LastUpdate
1            -10        12/01/2016

LastUpdate与该帐户的交易表中的事务处理的最后日期相同。

Answer 1

尝试以下操作。顶部部分只是创建了一些测试数据...

 WITH 
 trans (transdate,merchant,amount,accountID)
 AS
 (SELECT sysdate-100,'A',100,1 FROM dual UNION ALL
  SELECT sysdate-99,'B',-101,1 FROM dual UNION ALL
  SELECT sysdate-91,'C',-50,1 FROM dual UNION ALL
  SELECT sysdate-10,'D',30,1 FROM dual UNION ALL
  SELECT sysdate-100,'E',100,2 FROM dual UNION ALL
  SELECT sysdate-99,'F',-100,2 FROM dual UNION ALL
  SELECT sysdate-91,'G',-50,2 FROM dual UNION ALL
  SELECT sysdate-10,'H',200,2 FROM dual UNION ALL
  SELECT sysdate-10,'I',100,3 FROM dual UNION ALL
  SELECT sysdate-9,'J',-50,3 FROM dual UNION ALL
  SELECT sysdate-8,'K',-75,3 FROM dual 
 )
SELECT DISTINCT
 accountId
FROM
 (SELECT
   accountid
  ,transdate
  ,amount
   --get the maximum balance in the dataset
  ,MAX(balance) OVER (PARTITION BY accountID) max_balance
  FROM
   --this query gets raw transaction data and calculates cumulative balance
   (SELECT
     accountid
    ,transdate
    ,amount
     --cumulative balance
    ,SUM(amount) OVER (PARTITION BY accountID ORDER BY transdate)   balance   
     --works the dateof the next transaction - ths determines how long the balance is current 
    ,LEAD(transdate) OVER (PARTITION BY accountID ORDER BY transdate) - 1  bal_end_date
    FROM
     trans
   )
  WHERE 1=1
  --only interested in balances that are 'current' in the past three months
  AND bal_end_date >= ADD_MONTHS(sysdate,-3)
 )
WHERE 1=1
--only want accounts where the maximum balance is negative
AND max_balance < 0
;