| Author |
 Topic  |
|
|
sqldbaa
Starting Member
India
32 Posts |
 Posted - 12/18/2007 : 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
India
47099 Posts |
Posted - 12/18/2007 : 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 |
Edited by - visakh16 on 12/18/2007 06:42:14 |
 |
|
|
sqldbaa
Starting Member
India
32 Posts |
Posted - 12/20/2007 : 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
India
47099 Posts |
Posted - 12/20/2007 : 09:24:42
|
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 |
|
|
| |
Topic |
|
|
|
Plzeee help
