SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 CALC FOR MIN TIME INTEREST AMOUNT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kalaiselvan
Posting Yak Master

India
109 Posts

Posted - 04/08/2013 :  12:11:02  Show Profile  Reply with Quote
Hi,
Am using a Master and a child table Interest Collection. Where the New Entry will be maintained in Master and the Collection maintained in Child one.
Need to calculate Each days New Interest Collected Value. Will give an example for a Single Day data.

Ex:
TABLE: InterestMaster
[REGNO] [TRANDATE] [TIME]
225 2013-04-08 11:25:00
226 2013-04-08 12:49:45
227 2013-04-08 20:15:00

TABLE: InterestColln
[SNO] [REGNO] [COLLNDATE] [COLLNTIME] [AMOUNT]
1 220 2013-04-08 10:35:40 1000
2 221 2013-04-08 10:41:34 500
3 222 2013-04-08 10:50:32 2000
4 223 2013-04-08 10:55:25 1500
5 224 2013-04-08 11:00:00 750
6 225 2013-04-08 11:25:10 3000
7 226 2013-04-08 12:50:00 2000
8 225 2013-04-08 12:55:00 500
9 225 2013-04-08 12:58:00 500
10 227 2013-04-08 20:15:30 1000
11 226 2013-04-08 20:25:00 1000

Collection table has Prev.Interest entries with 6 Entries against Master for single day. Have to calc only New Interest Collected for the day.


Output:
SUM(AMOUNT) For the Day = 8000Rs is coming in my Calc.
Need the Output has 6000 Rs. Have to take the 1st Entry Amount and its total for the day. Please Help me to fix this with query.



Regards,
Kalai

James K
Flowing Fount of Yak Knowledge

3660 Posts

Posted - 04/08/2013 :  12:40:14  Show Profile  Reply with Quote
select
	SUM(InterestColln)
from
	InterestMaster a
	inner join
	(
		select *, ROW_NUMBER() over (partition by REGNO order by COLLNDATE,COLLNTIME) as RN
		from InterestColln
	) b on a.REGNO = b.REGNO
where
	RN=1;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000