selecting different entities and do calculations

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.

All Forums

General SQL Server Forums

Database Design and Application Architecture

selecting different entities and do calculations

Author

Topic

jonekim
Starting Member

35 Posts

Posted - 2012-04-07 : 11:24:33

userTABLE

- userName
- userReg
- classId

data in the form userName, userReg, classId
user1, USER_1, 10
user2, USER_2, 10
user3, USER_3, 10
user4, USER_4, 12

feeTABLE
- feeAmt
- feeType
- classId
insert into @feeAmt 9000, @feeType 'college_fee',@classId 10

insert into @feeAmt 5000, @feeType 'hostel_fee',@classId 10

insert into @feeAmt 10000, @feeType 'college_fee',@classId 12

paymentTABLE
- userReg
- classId
- feeType
- amtPaid
- amtRemained (can be NULL)
******************************
insert into @userReg 'USER_1', @classId 10, @feeType 'college_fee', @amtPaid 9000, @amtRemained NULL

insert into @userReg 'USER_1', @classId 10, @feeType 'hostel_fee', @amtPaid 3000, @amtRemained 2000
*********************************
My queries are;

1. how can i get the names who have not clear their fee by giving input @classId and @feeType

2. the value of amtRemained should be done by the sql query itself i.e when I enter @amtPaid 3000 the @amtRemained should automatically be 2000, I should not be entering it manually.

how can I write stored procedure for the above conditions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-08 : 18:16:58

looks like an assignment question. can we see what you tried yet?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jonekim
Starting Member

35 Posts

Posted - 2012-04-10 : 00:01:09

@ visakh16.
I've solved the problem 1.
and for the problem 2, one solution can be to make entities of all the months of a year and make entry. but it can not be optimal, if I go with years also i.e it is not possible to make entities of the months of 2010,2011,2012... how can i solve the problem 2?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-10 : 15:41:32

join onto feeTABLE on classid and feeType and subtract the amtpaid from feeAmt to get remainingamt

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources