CODEWITHSUNDEEP
pythonmysqlsqlsql-serveroracle-database
user6863891
user6863891

Reputation:

SQL select specific column which are present for all date range

How should I write a SQL queries to find all unique value of a column that are present in all date range.

+-------------+--------+------------+
| primary_key | column |    date    |
+-------------+--------+------------+
|           1 | a      | 2020-03-01 |
|           2 | a      | 2020-03-02 |
|           3 | a      | 2020-03-03 |
|           4 | a      | 2020-03-04 |
|           5 | b      | 2020-03-01 |
|           6 | b      | 2020-03-02 |
|           7 | b      | 2020-03-03 |
|           8 | b      | 2020-03-04 |
|           9 | c      | 2020-03-01 |
|          10 | c      | 2020-03-02 |
|          11 | c      | 2020-03-03 |
|          12 | d      | 2020-03-04 |
+-------------+--------+------------+

In the above example if query date range is 2020-03-01 to 2020-03-04 output should be

a
b

since only a and b are present for that range similarly if query date range is 2020-03-01 to 2020-03-03 output should be

a
b
c

I could do this in an python script by fetching all rows and using a set. Is is possible to write a SQL query yo achieve same result?

Upvotes: 3

Views: 998

Answers (2)

praveen
praveen

Reputation: 75

One More way to solve above Problem.

select data from (
select data,count(data) as datacnt from unique_value group by data ) a,
(select count(distinct present_date ) as cnt  from unique_value) b
where a.datacnt=b.cnt;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522311

You may aggregate by column value and then assert the distinct date count:

SELECT col
FROM yourTable
WHERE date BETWEEN '2020-03-01' AND '2020-03-04'
GROUP BY col
HAVING COUNT(DISTINCT date) = 4;

Upvotes: 2

Related Questions