Query to identify people exceeding a particular amount within an interval of 6 months

I have tried searching in Google but am just not finding the right post. I have this table with fields `trans_date`, `CID` (which is customerid), and `amount`.

How do I find the CID exceeding an amount of say \$1000 within any time period of 6 months or 180 days (eg. Jan-June or Feb-July, or November 2014-April 2015 (periods overlapping years too)) starting from the beginning of time?

```trans_date             CID        amount
22-Jun-2015            123        550
24-Aug-2015            999        200
15-Aug-2015            123        500
24-nov-2014            321        200
08-Jan-2015            321        900
24-oct-2015            999        200
```

In the result CID 123 and 321 should be the output both exceeding the total amount 1000 in a span of 6 months.

Any response would be highly appreciated.

• Edit your question and add some sample data and the expected output based on that data. I am also not sure what "within any time period of 6 months" means. Any period of 6 months since the beginning of time e.g. January 2000 to June 2000 and then again between March 2003 and August 2003 and then again between October 2007 and March 2008? Or do you mean "in the last 6 months since today"? Or any customer that exceeded that amount within a specific date range that you specify when running the query?
– user1822
Commented Dec 23, 2015 at 11:26
• i did...better ? Commented Dec 23, 2015 at 11:57

The part "within any time period of 6 months" makes things quite tricky. It is very easy to check this for a specific period:

``````select cid,
sum(amount)
from transfer
where trans_date >= date '2014-11-01'
and trans_date < date '2015-06-01'
group by cid
having sum(amount) >= 1000;
``````

But this is not what you want.

You want a "rolling running total" that covers the last 6 month relative to the the `trans_date` value of each row. This can easily be achieved using a window function (aka "analytical function")

``````select trans_date,
cid,
amount,
sum(amount) over (partition by cid order by trans_date range between interval '6' month preceding and current row) as running_total
from transfer
order by trans_date;
``````

The "trick" is the "range between ..." part: it will only evaluate the running total for the "last 6 month" relative to the month of the "current row"

The above returns the following result for your sample data:

``````TRANS_DATE          | CID | AMOUNT | RUNNING_TOTAL
--------------------+-----+--------+--------------
2014-11-24 00:00:00 | 321 |    200 |           200
2015-01-08 00:00:00 | 321 |    900 |          1100
2015-06-22 00:00:00 | 123 |    550 |           550
2015-08-15 00:00:00 | 123 |    500 |          1050
2015-08-24 00:00:00 | 999 |    200 |           200
2015-10-24 00:00:00 | 999 |    200 |           400
``````

Now to get the customers you are interested in, you can wrap this query into a derived table and then get only those CIDs that have at least one running total that exceeds 1000:

``````select distinct cid
from (
select trans_date,
cid,
amount,
sum(amount) over (partition by cid order by trans_date range between interval '6' month preceding and current row) as running_total
from transfer
) t
where running_total > 1000;
``````

The `distinct` is necessary because a customer can have more than one "6 month period" where the total amount exceeds 1000.

This is probably not the most efficient query though - but currently I can't think of anything better.

Here is a SQLFiddle example: http://sqlfiddle.com/#!4/1e26f/2

You can write such a function :

``````create or replace function get_period(v_date date)
return varchar2
is period varchar2(10);
yr varchar2(4);
mnt number;
begin
yr := to_char(v_date ,'yyyy');
mnt := to_number(to_char(v_date,'mm'));
if (mnt > 0 && mnt < 7) then
period := concat(yr ,'_1');
end if;
if (mnt > 6 && mnt < 13) then
period := concat(yr ,'_2');
end if;
return period;
end;

then in query :

select CID , get_period(trans_date) , sum(amount)
from table_name
group by CID , get_period(trans_date)
having sum(amount) > 1000
``````
• thank you for the answer but what i am looking for is not the last 180 days, but transactions of any customer in any interval of 180 days. For eg the current month is Dec, but i would like the query to find out between Appril and June if he exceeded 1000 spendings. Commented Dec 23, 2015 at 11:13
• if you need to check this AMOUNT exceeding for each period of 180 days then i would write a function which get the trans_date and return the period which this record is belong too. Commented Dec 23, 2015 at 11:14
• you can use case as well : Commented Dec 23, 2015 at 11:16
• `year(trans_date)+'-'` is invalid SQL. `+` is used to add numbers, not to concatenate strings. For character values you have to use `||` in SQL. There is also no `year()` or `month()` function in Oracle. And the expression `case true` is invalid in Oracle as well. But even if it was valid, it would make no sense at all because the second part of the `case` will never be executed
– user1822
Commented Dec 23, 2015 at 11:25
• The question was tagged `oracle` right from the beginning.
– user1822
Commented Dec 23, 2015 at 12:06