Create store porcedure- Plzee help

[ Follow Ups ] [ Post Followup ] [ SQL Script Builder Forum ]

Posted by sqldba 202.54.173.50 on December 18 2007 05:35:17

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 to 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 help 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.

Follow Ups:

Post a Followup

* : (Required)

* Name:
E-Mail:

* Subject:

* Comments:
> 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 to 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 help 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. >

Optional Link URL:
Link Title:
Optional Image URL:

Enter the code shown :

[ Follow Ups ] [ Post Followup ] [ SQL Script Builder Forum ]