Sql: count days between values over 1000 for 5 days

Question

0.00/5 (No votes)

See more:

I would like to select the number of days between first time a cell in the "value" coulum is under -1000 until it is above, but only if the number of days is over 5 days:
So in this exaple if would like to get 6 out on the "value colum" but only 2 and 3 in the value2 colum

Date	      Value	             Value2
25-01-2017	600	           600
23-01-2017	-400,00 	-400,00 
20-01-2017	-2300,00 	-2300,00 
19-01-2017	-1200,00 	-1200,00 
18-01-2017	-1600,00 	-1600,00 
17-01-2017	-3400,00 	500,00 
16-01-2017	-1333,00 	-1333,00 
13-01-2017	-4567,00 	-4567,00 
12-01-2017	-453,00 	-453,00 
11-01-2017	300,00 	300,00

Table name: balance

What I have tried:

SQL

select (a.date-b.date) as count
from  balance a left join balance b on a.date=b.date
where value>1000 and count>4

Posted 22-Aug-17 21:52pm

Member 13374159

Updated 5-Sep-17 1:55am

Andy Lanng

v2

Add a Solution

Comments

RAMASWAMY EKAMBARAM 23-Aug-17 5:32am

Your requirement will be clearer if you explain with reference to the above data, what you mean by getting 6 wrt column 'value' and 2 & 3 wrt 'value2'. In fact there is no reference to column 'value2' in your query.

GKP1992 23-Aug-17 9:21am

Shouldn't it be zero for Value2 column? Since the value comes back to 500 on 17-01-2017 and it is never below -1000 for 5 or more days.

Member 13374159 4-Sep-17 7:55am

Basically I would only like to identity customers with a overdraft more negative than -1000 for 5 days in a row.
So in this example a customer with data like in the "value" colum should either get a count of 6 or just a binary 1 while a customer as in the "value2" colum should get a count of 2 and 3 OR a binary 0 value.

Member 12528773 5-Sep-17 3:06am

If I understand your request well, I think you may need to introduce the CustomerID, so in your illustration there will be 2 customers, customer 1 with value and customer 2 with value 2. When you say Overdraft, you mean negative balance? Confirm if this is the case. Then I will attempt identifying customers with overdraft of 1000 or more for 5 days consecutively.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RAMASWAMY EKAMBARAM · Answer 1 · 2017-09-05T01:54:00

create table accountbalance
(
 custid         varchar2(5) not null,
 adate          date not null,
 balance        number not null,
 constraint pk_accountbalance primary key(custid, balance)
);

SQL

I have inserted the values in the first column for customer id (C1) and the values in the second column for customer id (C2).

The query:

with
a as
(
select custid selid, adate seldate, balance selbal
from accountbalance
),
b as
(
select selid testid, seldate testdate, nvl(max(adate), to_date('01-jan-1900')) prevnormaldate
from a, accountbalance
where custid = selid
and adate < seldate
and balance  > -1000
group by selid, seldate
),
c as
(
select selid, seldate, selbal, prevnormaldate
from a, b
where selid = testid
and seldate = testdate
)
select selid custid, seldate trandate, min(selbal) balance, sum(case when selbal > -1000 then 0 else 1 end) odcount
from c, accountbalance
where selid = custid
and adate between prevnormaldate + 1 and seldate
group by selid, seldate
order by 1, 2
;