CODEWITHSUNDEEP
mysqlsqlamazon-redshift
dtman85
dtman85

Reputation: 1

How to count the numbers of rows within a certain days

I would like to perform a query that counts the number of rows that have a close date within 60 days of open_date by store.

Sample: Table A 

Store  Open Date  Close Date
A      2017-01-01   2017-01-31
B      2017-02-02   Null
A      2017-01-02   2018-01-21

Thanks in advance.

Upvotes: 0

Views: 135

Answers (3)

T Gray
T Gray

Reputation: 712

select store, open_date, count(*)
from tableA
where close_date between date_sub(open_date, interval 60 DAY) and date_add(open_date, interval 60 DAY)
group by store, open_date;

This will work but your starting date has to either be current date or the open_date (which is what your question was). Given that the open_date appears to be different for each row, your count is going to be ... interesting.

Also, count will always return something, so unless you use an if or case statement keying on 0 (zero) it will return a 0 not a null.

Upvotes: 0

cdaiga
cdaiga

Reputation: 4937

Try this on of the queries below:

SELECT count(*) numberOfDays
FROM TableA
WHERE close_date>=CURRENT_DATE - INTERVAL 60 DAY;

SELECT count(*) numberOfDays
FROM TableA
WHERE close_date>=CURRENT_DATE - INTERVAL '60' DAY;

See it work on SQL Fiddle.

Upvotes: 0

Daniel Gale
Daniel Gale

Reputation: 663

Use datediff()

SELECT count(*) FROM [Table A] WHERE datediff(Close_date,Open_date) <= 60;

Upvotes: 2

Related Questions