I want to perform a check "is there an entry for each of the last 100 days in a table" where the table has something like a reference date column and was thinking about joining with a subquery that returns sysdate - 0, sysdate - 1, ... sysdate - 100.
Updates (for clarification):
Is this a good approach?
Assuming your Oracle table looks like this...
CREATE TABLE DATE_TABLE (
D DATE,
-- And other fields, PK etc...
)
...and assuming D contains "round" dates (i.e. no time-of-day), the following query will give you all the missing dates between :min_date and :min_date + :day_count:
SELECT *
FROM (
SELECT (TO_DATE(:min_date) + LEVEL - 1) GENERATED_DATE
FROM DUAL
CONNECT BY LEVEL <= :day_count
)
WHERE
GENERATED_DATE NOT IN (SELECT D FROM DATE_TABLE)
In plain English:
This is what you are looking for:
Select seqnum.date, count(issues.id)
from
(
SELECT
Curdate() - interval (TENS.SeqValue + ONES.SeqValue) day Date
FROM
(
SELECT 0 SeqValue
UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) ONES
CROSS JOIN
(
SELECT 0 SeqValue
UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50
UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90
) TENS
) seqnum
left join issues on (cast(issues.created_on as date) = seqnum.date)
group by seqnum.date
I ran it against a Redmine instance to see how many issues were created in the last 100 days, day by day, including days where no issue was created. Adjust to your data structures accordingly. Enjoy :)
This might help you out:
create table your_table (a_date date not null);
insert into your_table values (date(now()-interval 0 day));
insert into your_table values (date(now()-interval 1 day));
insert into your_table values (date(now()-interval 2 day));
insert into your_table values (date(now()-interval 3 day));
insert into your_table values (date(now()-interval 5 day));
insert into your_table values (date(now()-interval 6 day));
insert into your_table values (date(now()-interval 9 day));
insert into your_table values (date(now()-interval 10 day));
insert into your_table values (date(now()-interval 50 day));
insert into your_table values (date(now()-interval 60 day));
insert into your_table values (date(now()-interval 70 day));
insert into your_table values (date(now()-interval 80 day));
insert into your_table values (date(now()-interval 90 day));
select a_date,ifnull(datediff(next_date ,a_date)-1,0) as number_of_days_missing_after_date
from
(
select dt.a_date,
(select min(a_date) from your_table dtp where dtp.a_date > dt.a_date and dtp.a_date >= date(now()-interval 100 day)) as next_date
from
(select a_date
from your_table
union select date(now()-interval 100 day)) dt
where dt.a_date >= date(now()-interval 100 day)
) a;
Should give you an indication as to which dates are missing.
I have created table your_table in place of the table that you are planning to check for missing dates (so that I could illustrate my example more clearly to you).
Perhaps the only problem with this solution is that it will not give you a list of the missing dates - though they can be easily derived by looking at the (in the example) a_date column and the number of days missing after a_date.
Hope it helps & good luck!
If there aren’t any gaps (e.g., weekends), a trick you can use is
SELECT COUNT(DISTINCT somedate)=100 FROM sometable -- evaluates to boolean
WHERE somedate>=sysdate-100;
Depending on your RDBMS, a GROUP BY may work faster than COUNT DISTINCT.
[Post clarification comment]
As you have explained it now, I think you want a LEFT JOIN between a dense date list and your table, then COUNT and GROUP BY. If you are using MySQL, the way to generate the date list in a subquery is covered by this earlier stackoverflow (I don't know MySQL nearly well enough to have thought of the accepted answer). It is easier in most other DB systems.
The solution mentioned at that link of a permanent calendar table is not so bad, either.
In MySQL:
SELECT * FROM `table_name` WHERE `date_column` > DATE_SUB(CURDATE(), INTERVAL 99 DAY)
