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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 CALC FOR MIN TIME INTEREST AMOUNT

Author  Topic 

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2013-04-08 : 12:11:02
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-08 : 12:40:14
[code]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;[/code]
Go to Top of Page
   

- Advertisement -