sql - Date continuity in a database (find missing date gaps) -

i have database of users pay monthly payment. need check if there continuity in these payments. example in table below:

+---------+------------+ | user_id |    date    | +---------+------------+ |       1 | 2015-02-01 | |       2 | 2015-02-01 | |       3 | 2015-02-01 | |       1 | 2015-03-01 | |       2 | 2015-03-01 | |       3 | 2015-03-01 | |       4 | 2015-03-01 | |       1 | 2015-04-01 | |       2 | 2015-04-01 | |       3 | 2015-04-01 | |       4 | 2015-04-01 | |       5 | 2015-04-01 | |       1 | 2015-05-01 | |       2 | 2015-05-01 | |       3 | 2015-05-01 | |       4 | 2015-05-01 | |       5 | 2015-05-01 | |       1 | 2015-06-01 | |       2 | 2015-06-01 | |       3 | 2015-06-01 | |       5 | 2015-06-01 | |       3 | 2015-07-01 | |       4 | 2015-07-01 | |       5 | 2015-07-01 | +---------+------------+ 

until may ok, in june user 4 didn't pay although paid in next month (july). in july users 1 , 2 didn't pay, ok, because resign service. in case need have information "user 4 didn't pay in june". possible using sql?

i use ms access if it's necessary information.

from experience, cannot work paid in table fill gaps. if in case of user not pay specific month, possible query leaves entire month out of equation.

this means need list dates jan dec , check against each user if have paid or not. again requires table requested date compare.

dedicated rdbms provide temporary tables, sp, functions allows create higher level/complex queries. on other hand ace/jet engine provides less possibilities there way around done. (vba)

in case, need give database specific date period in looking gaps. either can current year or between yearx , yeary.

here how work:

1. create temporary table called tbl_date
2. create vba function generate requested date range
3. create query (all_dates_all_users) select requested dates & user id's (without join) give dates x users combination
4. create query left join all_dates_all_users query user_payments query. (this produce dates users , join user_payments table)
5. perform check whether user_payments null. (if null user x hasn't paid month)

here example:

[tables]

1. tbl_date : id primary (auto number), date_field (date/time)
2. tbl_user_payments: pay_id (auto number, primary), user_id (number), pay_date (date/time) table modify per requirements. i'm not sure if have dedicated user table use payments table user_id too.

[queries]

1. qry_user_payments_all_month_all_user:

   select year([date_field]) myear, month([date_field]) mmonth, qry_user_payments_user_group.user_id qry_user_payments_user_group, tbl_date order year([date_field]), month([date_field]), qry_user_payments_user_group.user_id;

2. qry_user_payments_paid_or_not_paid

   select qry_user_payments_all_month_all_user.myear, qry_user_payments_all_month_all_user.mmonth, qry_user_payments_all_month_all_user.user_id, iif(isnull([tbl_user_payments].[user_id]),"not paid","paid") [paid?] qry_user_payments_all_month_all_user left join tbl_user_payments on (qry_user_payments_all_month_all_user.user_id = tbl_user_payments.user_id) , ((qry_user_payments_all_month_all_user.mmonth = month(tbl_user_payments.[pay_date]) , (qry_user_payments_all_month_all_user.myear = year(tbl_user_payments.[pay_date]) )) ) order qry_user_payments_all_month_all_user.myear, qry_user_payments_all_month_all_user.mmonth, qry_user_payments_all_month_all_user.user_id;

[function]

public function fn_cretae_date_table(idate_from date, optional idate_to date) '--------------------------------------------------------------------------------------- ' procedure : fn_cretae_date_table ' author    : krish km ' date      : 22/09/2015 ' purpose   : generate date period , check whether payments received. query opened results ' copyrights: more welcome edit , reuse code. i'll happy receive courtesy reference: ' contact   : krishkm@outlook.com '--------------------------------------------------------------------------------------- '      dim from_month, to_month integer     dim from_year, to_year long     dim i, j integer     dim sql_set string     dim strdoc string     strdoc = "tbl_date"      docmd.setwarnings (false)      sql_set = "delete * " & strdoc     docmd.runsql sql_set       if (ismissing(idate_to)) or (idate_to <= idate_from)         'just current year         from_month = vba.month(idate_from)         from_year = vba.year(idate_from)          = from_month 12             sql_set = "insert " & strdoc & "(date_field) values ('" & from_year & "-" & vba.format(i, "00") & "-01 00:00:00')"             docmd.runsql sql_set         next     else         from_month = vba.month(idate_from)         to_month = vba.month(idate_to)         from_year = vba.year(idate_from)         to_year = vba.year(idate_to)          j = from_year to_year             = from_month to_month                 sql_set = "insert " & strdoc & "(date_field) values ('" & j & "-" & vba.format(i, "00") & "-01 00:00:00')"                 docmd.runsql sql_set             next         next j      end if      docmd.setwarnings (true)      on error resume next     strdoc = "qry_user_payments_paid_or_not_paid"     docmd.close acquery, strdoc     docmd.openquery strdoc, acviewnormal end function 

you can call public function button or form or debug window:

?fn_cretae_date_table("2015-01-01","2015-10-01") 

this generate jan oct , check whether received payments or not.

[screen]: