Reg: Show the Last balance of the student id, after completing it won't show in the list

Question

1.80/5 (2 votes)

See more:

HTML

Hi,


SID 	COURSE 	PAID	 BAL	 TOT 	BILLNO	 DATE

1 	MATHS	6000 	4000	10000 	001	 01/06/2013 
2 	MATHS 	5000	5000 	10000 	002	 01/06/2013
3 	MATHS 	2000 	8000 	10000 	003 	 01/06/2013
2 	MATHS	3000	2000	10000 	004 	 02/06/2013
4 	MATHS	7000 	3000 	10000 	005 	 03/06/2013
1	MATHS	4000 	0.0 	10000	006	 04/06/2013
2	MATHS	2000 	0.0 	10000	007	 05/06/2013
3 	MATHS 	5000 	3000 	10000 	003 	 06/06/2013


I want to show the record last balance of the studentid, and if he completing his total balance, it wont't show in the list. ie., I'm expecting the following o/p.

SID COURSE PAID	   BAL	  TOT   BILLNO	 DATE

3   MATHS  5000   3000   10000  003     01/06/2013
4   MATHS  7000   3000   10000  005     03/06/2013

If possible i need the code...

Posted 6-Jun-13 2:34am

drveera

Add a Solution

Comments

Maciej Los 6-Jun-13 8:42am

What condition? Date? SID?, BillNo?

gvprabu 6-Jun-13 8:53am

nice Question... U need to check only one subject or u have to list all subjects who and all having balance in their course Fees. U have any PK in this table

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-06-06T04:52:00

Please, read my comment to your question.

Have a look here:

SQL

SET DATEFORMAT dmy;

DECLARE @tbl TABLE([SID] INT, [COURSE] VARCHAR(30), [PAID] DECIMAL(8,2), [BAL] DECIMAL(8,2), [TOT]  DECIMAL(8,2), [BILLNO] VARCHAR(3), [DATE] DATETIME)

INSERT INTO @tbl ([SID], [COURSE], [PAID], [BAL], [TOT], [BILLNO], [DATE])
SELECT 1 AS [SID], 'MATHS' AS [COURSE], 6000 AS [PAID], 4000 AS [BAL], 10000 AS [TOT], '001' AS BILLNO, '01/06/2013' AS [DATE]
UNION ALL SELECT 2, 'MATHS', 5000, 5000, 10000, '002', '01/06/2013'
UNION ALL SELECT 3, 'MATHS', 2000, 8000, 10000, '003', '01/06/2013'
UNION ALL SELECT 2, 'MATHS', 3000, 2000, 10000, '004', '02/06/2013'
UNION ALL SELECT 4, 'MATHS', 7000, 3000, 10000, '005', '03/06/2013'
UNION ALL SELECT 1, 'MATHS', 4000, 0.0, 10000, '006', '04/06/2013'
UNION ALL SELECT 2, 'MATHS', 2000, 0.0, 10000, '007', '05/06/2013'
UNION ALL SELECT 3, 'MATHS', 5000, 3000, 10000, '003', '06/06/2013'


SELECT t1.[SID], t1.[COURSE], t1.[PAID], t1.[BAL], t1.[TOT], t1.[BILLNO], t1.[DATE]
FROM @tbl AS t1 INNER JOIN (
	SELECT [SID],  MAX([DATE]) AS [DATE]
	FROM @tbl
	GROUP BY [SID]
	) AS t2 ON t1.[SID] = t2.[SID] AND t1.[DATE]=t2.[DATE]
WHERE [BAL]>0 --what do you mean: TOT completed????
ORDER BY t1.[SID]

Result:

[SID]	[COURSE]	[PAID]		[BAL]		[TOT]		[BILLNO] 	[DATE]
3	MATHS		5000.00		3000.00		10000.00	003		2013-06-06 00:00:00.000
4	MATHS		7000.00		3000.00		10000.00	005		2013-06-03 00:00:00.000