September 24, 2007 at 1:06 pm
hi
I am involved in the developing of a hostel management system. At present, I am trying to find a way of identifying all the residents who have not paid their rents.
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 rentals, 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 account. 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 have 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 payments(
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 accounts
( 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
)
September 25, 2007 at 7:03 am
Select s.sessionnumber, sum(datediff(dd,s.startdate,s.enddate)*a.rate) as TotalOwed,
min(isnull(p.totpaid, 0)) as TotalPaid, sum(datediff(dd,s.startdate,s.enddate)*a.rate)-min(isnull(p.totpaid, 0)) as Balance
from sessions s inner join accounts a on s.sessionnumber=a.sessionnumber
left join (select sessionnumber, sum(amountpaid) Totpaid from payments group by sessionnumber) p
on s.sessionnumber =p.sessionnumber
Group by s.sessionnumber
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 26, 2007 at 7:45 am
I think you need to check the current date instead of automatically using s.enddate in the calcs. For example:
DATEDIFF(DAY, s.startdate, CASE WHEN GETDATE() < s.enddate THEN GETDATE() ELSE s.enddate END)
Btw, if you get a chance, this needs some serious re-design 🙂 .
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 27, 2007 at 2:14 pm
Sounds like more homework. Chiko what do you have so far? You have posted the question, but now why you are having trouble with this.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply