SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Plzeee help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqldbaa
Starting Member

India
32 Posts

Posted - 12/18/2007 :  05:32:51  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 12/18/2007 :  06:37:13  Show Profile  Reply with Quote
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

Edited by - visakh16 on 12/18/2007 06:42:14
Go to Top of Page

sqldbaa
Starting Member

India
32 Posts

Posted - 12/20/2007 :  09:15:54  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/20/2007 :  09:24:42  Show Profile  Reply with Quote
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

Edited by - visakh16 on 12/20/2007 09:25:50
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000