-3

Is there any way to list the dates on which user didn't submit the report?

In my application Users are submitting report everyday. I want to list out on which dates user didn't submit the report. Is there a way I can get this using mysql?

I can do like this: Getting records on which user submitted the dates, and process them to get non-submitted dates.

But it effects the performance,

Here is my schema: 

TableName : Reports
Fields: id, date, user_id

Please suggest me the solution, How can I do this to improve the performance.

Thanks in advance.

Jeeva Balan
  • 383
  • 2
  • 14
Anil kumar
  • 4,107
  • 1
  • 21
  • 36
  • @cimmanon, as for "possible duplicate" - I think the issue is not about just filling dates between certain dates, it's about EXTRACTING ALL "missed" dates for certain user – RomanPerekhrest Mar 16 '16 at 13:06
  • @Anilkumar, what type has `date` column in your table ? – RomanPerekhrest Mar 16 '16 at 13:11
  • it's of type `date` @RomanPerekhrest – Anil kumar Mar 16 '16 at 13:12
  • @RomanPerekhrest How is that any different? You have to know an upper and lower bounds of dates to consider, otherwise there are literally millions of dates that existed before today (including dates that occurred before the company even existed) where the user could have technically failed to submit a report. If you can't figure out from there how to list stuff that *doesn't* exist in another table, that's another question. – cimmanon Mar 16 '16 at 13:25
  • ofcourse i need to fetch the dates of certain range – Anil kumar Mar 16 '16 at 14:11

1 Answers1

-2

add another column in table named status

for each user insert 31 records for each day of the month like this below

id | date     | user_id | status
1  | 01/01/16 | 001     | 0
2  | 02/01/16 | 001     | 1
3  | 03/01/16 | 001     | 0
4  | 01/01/16 | 002     | 0

and finish that way for all days and users

and now the query, use this code

select * from Reports where user_id ='001' and status = 0

where 0 equal report not submitted

IFON26
  • 8
  • 3