how to do calculations with the data

Question

userTABLE

• userName
• userReg
• classId

data in the form userName, userReg, classId

1. user1, USER_1, 10
2. user2, USER_2, 10
3. user3, USER_3, 10
4. 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?

Answer 1

Hi kimjone,

First of all, to get help it is best to create a real script that can actually be run.  This will make it far more likely that (1) you will figure it out yourself while describing the problem, and (2)  that someone will take the time to look at it.   Just text descriptions as you put in and code that will not actually execute is a big discouragement to anyone helping.   So, try something like the following:

USE tempdb;
GO
CREATE TABLE dbo.#userTABLE 
( userName varchar(100),
  userReg varchar(20),
  classId int);
  
INSERT INTO dbo.#userTABLE (userName,userReg, classId) 
SELECT 'user1', 'USER_1', 10
UNION ALL 
SELECT 'user2', 'USER_2', 10
UNION ALL 
SELECT 'user3', 'USER_3', 10
UNION ALL 
SELECT 'user4', 'USER_4', 12;
CREATE TABLE dbo.#feeTABLE
(feeAmt DECIMAL(10),
 feeType VARCHAR(20),
 classID INT);
INSERT INTO dbo.#feeTABLE (feeAmt,feeType,classID)
SELECT 9000, 'college_fee',10
UNION ALL
SELECT 5000, 'hostel_fee', 10
UNION ALL
SELECT 10000,'college_fee', 12
CREATE TABLE dbo.#paymentTABLE
(userReg varchar(20),
 classId INT,
 feeType varchar(20),
 amtPaid DECIMAL(10),
 amtRemained DECIMAL(10));
 
INSERT INTO dbo.#paymentTABLE (userReg,classId,feeType,amtPaid,amtRemained) 
SELECT  'USER_1', 10, 'college_fee', 9000, NULL
UNION ALL
SELECT  'USER_1', 10, 'hostel_fee',  3000, 2000;
-- how can i get the names who have not clear their fee by giving input @classId and @feeType
-- Sample Search for who owes what.
DECLARE @classId INT;
DECLARE @feeType VARCHAR(20);
SET @classId = 10
SET @feeType = 'college_fee'
SELECT u.userName, u.userReg,u.classId,f.feeType,
   COALESCE(p.amtPaid,0) AS amtPaid,
   f.feeAmt - COALESCE(p.amtPaid,0) AS amtRemained
FROM dbo.#userTABLE u
   FULL OUTER JOIN dbo.#feeTABLE f ON  u.classId = f.classID
   LEFT JOIN -- Aggregate all student payments by class and type
     (SELECT userReg, classId, feeType, SUM(amtPaid) AS amtPaid
      FROM dbo.#paymentTABLE
      GROUP BY userReg,classId, feeType) AS p      
      ON p.userReg = u.userReg
                 AND p.feeType = f.feeType
WHERE f.feeAmt - COALESCE(p.amtPaid,0) > 0 -- Unpaid amounts  
   -- Filter for class and feeType (Remove to see all unpaid amounts)
   AND u.classId = @classId and f.feeType = @feeType
ORDER BY userName,classID
-- ACTUALLY, as show above, you should not need to maintain an @amtRemained column.
-- You need to work out the logic of your payent stored procedure to deal with 
-- underpayments, overpayments, and so forth. 
--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?
-- Clean up test 
DROP TABLE dbo.#userTABLE;
DROP TABLE dbo.#feeTABLE;
DROP TABLE dbo.#paymentTABLE;

The query is not the ultimate model of efficiency and I have not written your stored procedure, but at least this gives a framework from which to work.

All the best,
RLF

Answer 2

Hello,

This thread is about the creation of a stored procedure.The good forum is the Transact-SQL forum where i move this thread. It will interest more people on this new forum.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 3

Russell Field

Thanks for the suggestion. I will consider your suggestion in my future posts.