I have query regarding get the dates which are not exists in database table.
I have below dates in database.
2013-08-02
2013-08-02
2013-08-02
2013-08-03
2013-08-05
2013-08-08
2013-08-08
2013-08-09
2013-08-10
2013-08-13
2013-08-13
2013-08-13
and i want the result which is expected as below,
2013-08-01
2013-08-04
2013-08-06
2013-08-07
2013-08-11
2013-08-12
as you can see result has six dates which are not present into database,
i have tried below query
SELECT
DISTINCT DATE(w1.start_date) + INTERVAL 1 DAY AS missing_date
FROM
working w1
LEFT JOIN
(SELECT DISTINCT start_date FROM working ) w2 ON DATE(w1.start_date) = DATE(w2.start_date) - INTERVAL 1 DAY
WHERE
w1.start_date BETWEEN '2013-08-01' AND '2013-08-13'
AND
w2.start_date IS NULL;
but above return following result.
2013-08-04
2013-08-14
2013-08-11
2013-08-06
as you can see its giving me back four dates from that 14 is not needed but its still not contain 3 dates its because of left join.
Now please look into my query and let me know what are the best way i can do this?
Thanks for looking and giving time.
Re: Find missing datesFill down to the end of the data range and anytime you have a missing date it will let you know.
You can use the EXCEPT operator to compare a set of continuous dates to the dates with gaps. hope it helps. And then you can loop through all dates to find missing dates.
I guess you could always generate the date sequence and just use a NOT IN
to eliminate the dates that actually exist. This will max out at a 1024 day range, but is easy to shrink or extend, the date column is called "mydate" and is in the table "table1";
SELECT * FROM (
SELECT DATE_ADD('2013-08-01', INTERVAL t4+t16+t64+t256+t1024 DAY) day
FROM
(SELECT 0 t4 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 ) t4,
(SELECT 0 t16 UNION ALL SELECT 4 UNION ALL SELECT 8 UNION ALL SELECT 12 ) t16,
(SELECT 0 t64 UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL SELECT 48 ) t64,
(SELECT 0 t256 UNION ALL SELECT 64 UNION ALL SELECT 128 UNION ALL SELECT 192) t256,
(SELECT 0 t1024 UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768) t1024
) b
WHERE day NOT IN (SELECT mydate FROM Table1) AND day<'2013-08-13';
From the "I would add an SQLfiddle if it wasn't down" dept.
Thanks for help here is the query i am end up with and its working
SELECT * FROM
(
SELECT DATE_ADD('2013-08-01', INTERVAL t4+t16+t64+t256+t1024 DAY) missingDates
FROM
(SELECT 0 t4 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 ) t4,
(SELECT 0 t16 UNION ALL SELECT 4 UNION ALL SELECT 8 UNION ALL SELECT 12 ) t16,
(SELECT 0 t64 UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL SELECT 48 ) t64,
(SELECT 0 t256 UNION ALL SELECT 64 UNION ALL SELECT 128 UNION ALL SELECT 192) t256,
(SELECT 0 t1024 UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768) t1024
) b
WHERE
missingDates NOT IN (SELECT DATE_FORMAT(start_date,'%Y-%m-%d')
FROM
working GROUP BY start_date)
AND
missingDates < '2013-08-13';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With