help me with outstanding rental payments
-
Hi I am currently developing hostel management software for a local hostel. I am trying to find a way of identifying those residents who have outstanding rent payments. The hotel/ youth hostel requires that its guests pay their rentals at the beginning of the month. The residents often stay at the hostel for periods that are often over 5 months.I need to find a way of finding out which residents have paid their renatals, and which ones have not paid their rents. This is further complicated by the fact that some residents will make one large payment at the beginning of their session and this is meant to cover them for the duration of their stay. Each session has one account, and the total payments made are stored in the acount. I have also included the rate charged per day and the startdate of the session into the table, such that it is possible to calculate the amount that is owed and this figure can be some how be compared to the amount that has been paid. Given these circumstances, how can I find the residents who have outstanding rents. I havce included the table defs below. Please do feel free to help me. create table sessions ( sessionNumber int Identity Not null Primary Key, startdate smalldatetime not null, enddate smalldatetime not null, sessionType char(15) not null, duration int not null, roomNumber char(5) not null references Rooms (roomNumber), idNumber char(15) Not null references guestinfo (idnumber), ) create table accounts ( receiptNumber int Identity Not Null primary key, amountpaid money not Null, datepaid smalldatetime not null, roomNumber char(5) not null references rooms(roomNumber), sessionNumber int Not null references sessions(sessionNumber), paymentType char(10), userName char(15) Not Null references users (username), ) ---create table accpayments--- ( accountNumber int identity Not Null primary key, sessionNumber int not null references sessions(sessionNumber), totalpaymentsmade money not null, amountowing money not null, rate money not null ) twsted f8
-
Hi I am currently developing hostel management software for a local hostel. I am trying to find a way of identifying those residents who have outstanding rent payments. The hotel/ youth hostel requires that its guests pay their rentals at the beginning of the month. The residents often stay at the hostel for periods that are often over 5 months.I need to find a way of finding out which residents have paid their renatals, and which ones have not paid their rents. This is further complicated by the fact that some residents will make one large payment at the beginning of their session and this is meant to cover them for the duration of their stay. Each session has one account, and the total payments made are stored in the acount. I have also included the rate charged per day and the startdate of the session into the table, such that it is possible to calculate the amount that is owed and this figure can be some how be compared to the amount that has been paid. Given these circumstances, how can I find the residents who have outstanding rents. I havce included the table defs below. Please do feel free to help me. create table sessions ( sessionNumber int Identity Not null Primary Key, startdate smalldatetime not null, enddate smalldatetime not null, sessionType char(15) not null, duration int not null, roomNumber char(5) not null references Rooms (roomNumber), idNumber char(15) Not null references guestinfo (idnumber), ) create table accounts ( receiptNumber int Identity Not Null primary key, amountpaid money not Null, datepaid smalldatetime not null, roomNumber char(5) not null references rooms(roomNumber), sessionNumber int Not null references sessions(sessionNumber), paymentType char(10), userName char(15) Not Null references users (username), ) ---create table accpayments--- ( accountNumber int identity Not Null primary key, sessionNumber int not null references sessions(sessionNumber), totalpaymentsmade money not null, amountowing money not null, rate money not null ) twsted f8
You would need to write a query that returns: i) the total rent incurred thus far by each guest. This would presumably be the the number of days elapsed since the guest began their stay, multiplied by their daily rate ii) the total amount paid by the guest thus far Subtracting ii) from i) should give you the total amount currently owed by each guest. Regards Paul