Monday, April 09, 2012 4:16 PM
- Moved by Papy Normand Tuesday, April 10, 2012 9:01 AM Thread related to the creation of a stored procedure (From:SQL Server Data Access)
Monday, April 09, 2012 8:51 PM
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,
- Marked As Answer by KJian_ Monday, April 16, 2012 5:21 AM
Tuesday, April 10, 2012 8:46 AM
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.
Tuesday, April 24, 2012 11:00 AM
Thanks for the suggestion. I will consider your suggestion in my future posts.