Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chikoshumba
Starting Member
6 Posts |
 Posted - 2007-09-23 : 18:29:43
|
| Hi Can anyone out there help me? I am developing a hotel management system. I am trying to come up with a way of identifying all the residents with outstanding payments. In order to do this I have designed a session table where the session for each guest are staored. Each of the sessions has a session balance, and when a guest make a payment, this balance is then incremented.For every day that passes I would like to subtract the hotels rent from the session balance automatically. Is this feasible, and how can this be done.I would be very greatful to anyone who would be able to help me or give me advice, coz at the moment I am clueless. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-09-23 : 19:56:56
|
| I would hold the payments (and possibly total) separately.Why do you accrue the rents(?) daily? They will have booked in for a period and agreed the rate then so why not add that at the time of booking or checking in. This could be as a daily amount (similar to payment transactions) so the balance could be given by at any date by totalling the values up to that date if wanted - but the exposure would be given by the totals without consideration of date.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
chikoshumba
Starting Member
6 Posts |
Posted - 2007-09-24 : 11:07:19
|
| Hi Thanks for the quick reply. I have modified my table such that I now have a table that contains information about the sessions and a seperate accounts table and a seperate payments table.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, balance money not Null, DEFAULT 0, 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 ) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-09-24 : 11:42:46
|
| Where is the rate? I've called it DayRateTo get the state of the stayselect sessions.sessionNumber, AmountOutstanding = sessions.duration * DayRate - coalesce(amountpaid.amount)from sessions left join select sessionNumber, amount = sum(amountpaid) from accounts group by sessionNumber) amountpaidon sessions.sessionNumber = amountpaid.sessionNumberwhere sessions.duration * DayRate < coalesce(amountpaid.amount)To get the state of the stay to dateselect sessions.sessionNumber, AmountOutstanding = datediff(dd,sessions.startdate, sessions.enddate) * DayRate - coalesce(amountpaid.amount)from sessions left join select sessionNumber, amount = sum(amountpaid) from accounts group by sessionNumber) amountpaidon sessions.sessionNumber = amountpaid.sessionNumberwhere datediff(dd,sessions.startdate, sessions.enddate) * DayRate < coalesce(amountpaid.amount)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
chikoshumba
Starting Member
6 Posts |
Posted - 2007-09-24 : 14:54:52
|
| Hi there?Thank you for the very quick reply, I must be honest & admit that I aint got the slightest idea how the script u posted works. I have tried to replacing ur table names with mine but my efforst have been in vain. I am getting an error at the end of line one close to the closing bracket. I don't mean to be a nuiscance, but cld u pliz explain ur script. I am very new to sql server |
|
|
|
|
|
|
|
|
Hotel Manager