December 18, 2007 at 2:46 am
Hi All,
I am new to SQL programming, i have only a fair knowledge on sql programmin.So, I apologies for any silly questions:)-
I have a Table1 which contains
C1-acountid
C2-date
C3-grossamount(postivie and negative decimal values)
C4-netamount
Table2
C1-groupid
C2-accountid
Table 3
C1-groupid
C2-groupname
I need create a store procedure to retrieve the following on a single table
1. top 10 losers of the day i.e. 10 AccountIDs with the greatest negative Grossamount for the day
NOTE:These 10 AccountIDs may be sam or differing each day
2.sum of Netamount for each AccountIDs listed in STEP 1 since the beginning of the month.
NOTE:These 10 AccountIDs may be same or differing each day and each day sum of netamount should be from beginning of the month till current date.
3.Sum of Netamount for the last 5 days for each accountids in STEP1
The result set must contain the columns as below
C1-accountid
C2-date
C3-net loss for 10 losers on the current date since the beginning of the month
C4-Sum of Net for last 5 days
C5-groupname
Please haep me.
Below is the script that i have written, without calculating the sum
(
select top 10 a.date, a.accountid, a.gross, a.net, c.groupname
from GBSys_Sum_EOD a
join server2.dbname.dbo.table2 b on a.accontid=b.accounit=id
join server2.dbname.dbo.Table3 c on b.groupid=c.groupid
where date> getdate()-1
and gross< (floor(-00.00)) order by gross
)
Thanks in advance.
December 19, 2007 at 2:59 am
Please, provide proper DDL, sample data and expected results, otherwise we're all looking at days of guessing and not really helping anyone.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply