Stored procedure

  • 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

  • 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

  • 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