adding dates where the client didnt pay

[basilyos]

hello

i'm making a daily renting cars system

the client should pay every day ( day by day)

everything is ok but

if the client pay at these days

dd/mm/yyyy
01/01/2015
02/01/2015
03/01/2015
10/01/2015
11/01/2015
12/01/2015

at the date 15/01/2015 i want to check the payments

how to let the system show me that the dates

04-05-06-07-08-09-13-14-15/01/2015

the client didn't pay the fees

i have a solution

opening the payment form and make a new record everyday even if the client didn't pay and finally by a query i'm getting a list where the payment date field Is Null

but is there any other solution without adding the dates manually

 

[CJ_London]

you can also do this by comparing the date in the current record with the date in the previous record - if the difference is greater than 1 then there are some missing dates.

Alternatively you can have a calendar table, just containing all dates for a relevant period of time and use a left join to your hire table.

Which is more appropriate depends on what you want to see as an output

use the first method if you want to see something your example or like

'not paid between 04/01/2015 and 09/01/2015'
'not paid between 13/01/2015 and 15/01/2015'

use the second if you want to see something like

01/01/2015 paid
02/01/2015 paid
03/01/2015 paid
04/01/2015
15/01/2015
06/01/2015
07/01/2015
08/01/2015
09/01/2015
10/01/2015 paid
11/01/2015 paid
12/01/2015 paid
13/01/2015
14/01/2015
15/01/2015

(you could filter out the 'paid' to only see the not paid)

 

[basilyos]

Thank you I'll try the two option

 

[namliam]

You can do a cartesian product assuming you have somewhere in a table Startdate and Enddate.

Where offcourse if Enddate is null , then use today's date.

What you do is make a table that simply has the numbers 0 thru 731 (good for 2 years worth should be enough) lets call it tblDummyDays, lets call the column DayNumber
Then make a query

Select Startdate + tblDummydays.DayNumber
from   YourTable
, tblDummyDays
where startdate + tblDummydays.DayNumber between Startdate and nvl(Enddate, Date())

Save this query, which lists all days that would require a payment and outer join it back to your payments table to find out if they have a matching payment or not...

This works kindoff like the calander table suggested by CJ, but calander tables need to be filled till infinity or be maintained. This solution should work till infinity without an infinite table or maintenance.