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.
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:00TABLE: 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 1000Collection 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.REGNOwhere RN=1;[/code] |
|
|
|
|
|