Need SQL Script

[sqldba]

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 table which contains
C1-acountid
C2-date
C3-grossamount(pos tivie and negative decimal values)
C4-netamount

I need 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 sam 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

I have wirtten a script for retrieving the data for the STEP1. But how can i proceed with STEP 2 and 3.
I need all the three results in the same table


Please help me in doing it.

Thanks in advance

[cyberking]

Hi,

Probably posting ur script till STEP 1 would help in helping u to complete the script!

Regards
CyberKing

Hi All,

I am new to SQL programming, i have only a fair knowledge on sql programmin.So, I apologies for any silly questions:)-
.
.
.
Thanks in advance

[debasisdas]

Kindly post your code you are working on for further discussion on the topic.

[sqldba]

Kindly post your code you are working on for further discussion on the topic.

Hi,

The sql statement for step1 is

select top 10* from table_name where date > getdate()-1 and grossamount < (floor(-00.00)) order by grossamount

How to proceed further.

Please help

[nedu]

-- Current 1 Month
Select A.acountid,sum( A.net) from test3 A where
convert(varchar ,col2,111)
between Convert(varchar ,DATEADD(mm, DATEDIFF(mm,0,g etdate()), 0) ,111)
and convert(varchar ,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,ge tdate() )+1, 0)),111)
and A.acountid in
(
Select top 10 acountid from test3
where convert(varchar ,getdate(),111) = convert(varchar ,col2,111) --convert(varchar ,'11/22/2007',111)
order by net asc
)
group by acountid order by 1
-- Current 1 Month

-- Last 5 days
Select A.acountid,sum( A.net) from test3 A where
convert(varchar ,col2,111) > dateadd(day,-5,convert(varch ar,getdate(),11 1))
and A.acountid in
(
Select top 2 acountid from test3
where convert(varchar ,getdate(),111) = convert(varchar ,col2,111) --convert(varchar ,'11/22/2007',111)
order by net asc
)
group by acountid order by 1
-- Last 5 days


Kindly let me know for any other queries . .

Regards,
Nedu. M

[sqldba]

-- Current 1 Month
Select A.acountid,sum( A.net) from test3 A where
convert(varchar ,col2,111)
between Convert(varchar ,DATEADD(mm, DATEDIFF(mm,0,g etdate()), 0) ,111)
and convert(varchar ,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,ge tdate() )+1, 0)),111)
and A.acountid in
(
Select top 10 acountid from test3
where convert(varchar ,getdate(),111) = convert(varchar ,col2,111) --convert(varchar ,'11/22/2007',111)
order by net asc
)
group by acountid order by 1
-- Current 1 Month

-- Last 5 days
Select A.acountid,sum( A.net) from test3 A where
convert(varchar ,col2,111) > dateadd(day,-5,convert(varch ar,getdate(),11 1))
and A.acountid in
(
Select top 2 acountid from test3
where convert(varchar ,getdate(),111) = convert(varchar ,col2,111) --convert(varchar ,'11/22/2007',111)
order by net asc
)
group by acountid order by 1
-- Last 5 days


Kindly let me know for any other queries . .

Regards,
Nedu. M

Hi Nedu,

Thanks for the script.

I still need your help. I need to create a store procedure with doing some modification on (make join) your script to retrieve some more data.

I will elaborate.

Table 1
C1-accountid
C2-date
C3-gross(postivie and negative decimal values)
C4-net(postivie and negative decimal values)

Table2
C1-groupid
C2-accountid

Table 3
C1-groupid
C2-groupname

Note: Table 1 is in Server1 and Table 2 and Table 3 are in Server2


Please can you modify your script to get the result set with 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

The script i have written is

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.ac counit=id
join server2.dbname. dbo.Table3 c on b.groupid=c.gro upid

where date> getdate()-1
and gross< (floor(-00.00)) order by gross


In this script i have not calcualted the sum. I require your script modified to retrieve the accounntname, groupname and separate columns for netamount for 10 losers since the beginning of the month and sum of that 10 loser for last 5 days.

Please help.

Thanks in advance.

[sqldba]

-- Current 1 Month
Select A.acountid,sum( A.net) from test3 A where
convert(varchar ,col2,111)
between Convert(varchar ,DATEADD(mm, DATEDIFF(mm,0,g etdate()), 0) ,111)
and convert(varchar ,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,ge tdate() )+1, 0)),111)
and A.acountid in
(
Select top 10 acountid from test3
where convert(varchar ,getdate(),111) = convert(varchar ,col2,111) --convert(varchar ,'11/22/2007',111)
order by net asc
)
group by acountid order by 1
-- Current 1 Month

-- Last 5 days
Select A.acountid,sum( A.net) from test3 A where
convert(varchar ,col2,111) > dateadd(day,-5,convert(varch ar,getdate(),11 1))
and A.acountid in
(
Select top 2 acountid from test3
where convert(varchar ,getdate(),111) = convert(varchar ,col2,111) --convert(varchar ,'11/22/2007',111)
order by net asc
)
group by acountid order by 1
-- Last 5 days


Kindly let me know for any other queries . .

Regards,
Nedu. M

Hi Nedu,

Thanks for the script,

I need to create a Store Procedure for this script.

Please help me in doing this with some modification that i have elaborated.