469,128 Members | 1,655 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,128 developers. It's quick & easy.

Need SQL Script

8
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(postivie 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
Dec 7 '07 #1
6 1257
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
Dec 8 '07 #2
debasisdas
8,127 Expert 4TB
Kindly post your code you are working on for further discussion on the topic.
Dec 10 '07 #3
sqldba
8
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
Dec 10 '07 #4
nedu
65
-- 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,getdate()), 0) ,111)
and convert(varchar,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+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(varchar,getdate(),111))
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
Dec 11 '07 #5
sqldba
8
-- 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,getdate()), 0) ,111)
and convert(varchar,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+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(varchar,getdate(),111))
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.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


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.
Dec 11 '07 #6
sqldba
8
-- 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,getdate()), 0) ,111)
and convert(varchar,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+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(varchar,getdate(),111))
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.
Dec 11 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Sofia | last post: by
5 posts views Thread by deko | last post: by
6 posts views Thread by mike | last post: by
28 posts views Thread by Randy Starkey | last post: by
5 posts views Thread by news | last post: by
4 posts views Thread by Brie_Manakul | last post: by
14 posts views Thread by mistral | last post: by
4 posts views Thread by Jonathan Wood | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.