Plzeee help - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Plzeee help

Author

Topic

sqldbaa
Starting Member

32 Posts

Posted - 2007-12-18 : 05:32:51

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-18 : 06:37:13

try this:-

CREATE PROC AccntDet
@Date datetime
AS

SELECT t.accountid,t.date,net.netamount,lastfive.netamount,t3.groupname
FROM 
(
SELECT Top 10 accountid,date
FROM Table1
WHERE date=@Date
AND SUM(grossamount)< 0.00
GROUP BY date,accountid
ORDER BY SUM(grossamount))t
INNER JOIN (SELECT accountid,SUM(netamount) AS 'netamount'
            FROM Table1 WHERE date > '01/' + CASE WHEN MONTH(@Date)<10 THEN '0' +MONTH(@Date) ELSE MONTH(@Date) END +'/'+YEAR(@Date) AND date <= DATEADD(d,DATEDIFF(d,0,GETDATE()),0)
GROUP BY accountid)net
ON net.accountid=t.accountid
INNER JOIN (SELECT accountid,SUM(netamount) AS 'netamount'
            FROM Table1 WHERE DATEDIFF(d,date,DATEADD(d,DATEDIFF(d,0,GETDATE()),0))<=5
GROUP BY accountid)lastfive
ON lastfive.accountid=t.accountid    
INNER JOIN  Table2 t2
ON t2.accountid=t.accountid    
INNER JOIN Table3 t3
ON t3.groupid = t2.groupid

GO

sqldbaa
Starting Member

32 Posts

Posted - 2007-12-20 : 09:15:54

Thanks a lot.

But another help.

If in my result set C3=net loss for each of the 10 losers on the current date since the beginning of the previous month till , then how to modify the script .

C1-accountid
C2-date
C3-net loss for 10 losers on the current date since the beginning of the previous month till date
C4-Sum of Net for last 5 days
C5-groupname

Please help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-20 : 09:24:42

[code]CREATE PROC AccntDet
@Date datetime
AS

SELECT t.accountid,t.date,net.netamount,lastfive.netamount,t3.groupname
FROM
(
SELECT Top 10 accountid,date
FROM Table1
WHERE date=@Date
AND SUM(grossamount)< 0.00
GROUP BY date,accountid
ORDER BY SUM(grossamount))t
INNER JOIN (SELECT accountid,SUM(netamount) AS 'netamount'
FROM Table1 WHERE date > '01/' +
CASE WHEN MONTH(@Date)=1 THEN 12
WHEN MONTH(@Date)>1 AND MONTH(@Date)-1<=10 THEN '0' + MONTH(@Date)-1 ELSE MONTH(@Date)-1
END + '/' +
CASE
WHEN MONTH(@Date)=1 THEN YEAR(@Date) -1
ELSE YEAR(@Date)
END
AND date <= DATEADD(d,DATEDIFF(d,0,GETDATE()),0)
GROUP BY accountid)net
ON net.accountid=t.accountid
INNER JOIN (SELECT accountid,SUM(netamount) AS 'netamount'
FROM Table1 WHERE DATEDIFF(d,date,DATEADD(d,DATEDIFF(d,0,GETDATE()),0))<=5
GROUP BY accountid)lastfive
ON lastfive.accountid=t.accountid
INNER JOIN Table2 t2
ON t2.accountid=t.accountid
INNER JOIN Table3 t3
ON t3.groupid = t2.groupid

GO[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources