How to find records when the value change in a column?

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Tourist

How to find records when the value change in a column?

I have some sample data below. I would like to calculate the # of days the account balance is negative.  So for example, on 8/2 account 123 is 1 day with negative balance and on 8/3 account 123 is 2 days with negative balance.  How would I acheive that in a query? Thanks.  

 

account Id   businessdate   balance

123               8/1/2017            100

123               8/2/2017            -50

123               8/3/2017            -60

123               8/4/2017            10

123               8/5/2017             20

123               8/6/2017             -10

222               8/1/2017            0

222               8/2/2017           50

222               8/3/2017            -40

222              8/4/2017            10

222               8/5/2017            -20

222               8/6/2017            10

 

Thanks.


Accepted Solutions
Junior Contributor

Re: How to find records when the value change in a column?

You need nested OLAP-functions:

SELECT ...
   Sum(CASE WHEN balance < 0 THEN 1 ELSE 0 end)    -- only count negative values
   Over (PARTITION BY account_id, grp
         ORDER BY businessdate
         ROWS Unbounded Preceding)
FROM
 (
   SELECT ...
      Sum(CASE WHEN balance >=0 THEN 1 ELSE 0 end) -- assign a group number and reset it for positive values
      Over (PARTITION BY account_id
            ORDER BY businessdate
            ROWS Unbounded Preceding) AS grp
   FROM tab
 ) AS dt

 In Teradata there's a nice extension RESET WHEN for this case:

Sum(CASE WHEN balance < 0 THEN 1 ELSE 0 end)  -- only count negative values
Over (PARTITION BY account_id
      ORDER BY businessdate
      RESET WHEN balance >=0                  -- reset the count for positive values
      ROWS Unbounded Preceding)

 This results in exactly the same Explain, if you need additional OLAP-functions it might be easier to add them to the former synax.

1 ACCEPTED SOLUTION
1 REPLY
Junior Contributor

Re: How to find records when the value change in a column?

You need nested OLAP-functions:

SELECT ...
   Sum(CASE WHEN balance < 0 THEN 1 ELSE 0 end)    -- only count negative values
   Over (PARTITION BY account_id, grp
         ORDER BY businessdate
         ROWS Unbounded Preceding)
FROM
 (
   SELECT ...
      Sum(CASE WHEN balance >=0 THEN 1 ELSE 0 end) -- assign a group number and reset it for positive values
      Over (PARTITION BY account_id
            ORDER BY businessdate
            ROWS Unbounded Preceding) AS grp
   FROM tab
 ) AS dt

 In Teradata there's a nice extension RESET WHEN for this case:

Sum(CASE WHEN balance < 0 THEN 1 ELSE 0 end)  -- only count negative values
Over (PARTITION BY account_id
      ORDER BY businessdate
      RESET WHEN balance >=0                  -- reset the count for positive values
      ROWS Unbounded Preceding)

 This results in exactly the same Explain, if you need additional OLAP-functions it might be easier to add them to the former synax.