March 20, 2009 at 2:05 pm
Name Date Sal ID
A 02/21/2009 4:04:24 PM 90000 1
B 02/22/2009 4:04:35 PM 60000 2
C 02/23/2009 4:04:46 PM 70000 3
D 02/24/2009 4:05:01 PM 90000 4
E 02/25/2009 4:05:57 PM 80000 5
F 02/26/2009 4:05:57 PM 90000 6
I need to write a stored procedure which takes the ID value and based on that gives a cumulative sum of the sal .
Result:when ID is 6
Name Date Sal ID Cumulative Sal
A 02/21/2009 4:04:24 PM 90000 1 90000
B 02/22/2009 4:04:35 PM 60000 2 150000
C 02/23/2009 4:04:46 PM 70000 3 220000
D 02/24/2009 4:05:01 PM 90000 4 310000
E 02/25/2009 4:05:57 PM 80000 5 390000
F 02/26/2009 4:05:57 PM 90000 6 480000
Result:when ID is 4
Name Date Sal ID Cumulative Sal
A 02/21/2009 4:04:24 PM 90000 1 90000
B 02/22/2009 4:04:35 PM 60000 2 150000
C 02/23/2009 4:04:46 PM 70000 3 220000
D 02/24/2009 4:05:01 PM 90000 4 310000
tried this :
CREATE PROCEDURE usp_rpt_test
(@id int)
AS
select name, datecolumn, sal,id
,(select sum(sal) from mytest
where ID <= i.id)
'CummulativeSal'
from mytest i
But it returns all the rows irrespective of the id value I pass.
How do I write a stored proc where there are several input parameters involved and based on the input parameters only certain part of the stored procedure is executed . Like for say I have 5 input parameters and 5 select statements ,so based on a particular input parameter only 1 of the select should be executed.
Thanks
March 20, 2009 at 2:13 pm
I may be missing something, but your proc doesn't appear to actually use the @id parameter anywhere. It should probably be in the Where clause of the proc, and that would certainly help with your question. Is that the problem?
There are also better ways to solve running totals than the type of sub-query you used. The most efficient is a "quirky update". Search this site for "Running Totals", and you should find some data on how to use that. If you simply can't get that to work, a cursor or CLR will do it better than the sub-query. (By the way, that kind of sub-query is called a "triangular join", and it is really rough on performance once you have more than just a few rows.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 21, 2009 at 3:30 am
HI,
TRY THIS
declare @abc table
( name1 varchar(10),
date1 datetime,
sale int,
ID1 int
)
insert into @abc values ('A','2009-03-21',90000,1)
insert into @abc values ('B','2009-03-21',60000,2)
insert into @abc values ('C','2009-03-21',70000,3)
insert into @abc values ('D','2009-03-21',90000,4)
insert into @abc values ('E','2009-03-21',80000,5)
insert into @abc values ('F','2009-03-21',90000,6)
declare @RESULT table
( name1 varchar(10),
date1 datetime,
sale int,
ID1 int,
CUM_AMT int
)
insert into @RESULT
select *,0.00 from @abc
declare @CUM int,@run int
select @run = 1,@CUM = (select SALE from @RESULT where ID1 = 1)
update @RESULT
set @CUM = CUM_AMT = (case when @run <> ID1 then sale+@CUM else @CUM end ),
@run = ID1
from @RESULT
where ID1 <= 3 /*here you plot the desire count*/
select * from @RESULT for 3 rows
name1date1saleID1CUM_AMT
A2009-03-21 00:00:00.00090000190000
B2009-03-21 00:00:00.000600002150000
C2009-03-21 00:00:00.000700003220000
D2009-03-21 00:00:00.0009000040
E2009-03-21 00:00:00.0008000050
F2009-03-21 00:00:00.0009000060
ARUN SAS
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply