Post reply

Conditional Sum

  • March 27, 2003 at 2:12 am

    #101418

    Hi,

    i have a value say 1000 and i have atble which contains values whose sum could be 10000. I want to sum the rows till my sum reaches 1000. Is it possible that i can do it in a single query. I can use cursor but i want to avaoid that.

    Second question, what are the pros and cons of using CASE statement.

    Third whare can i look for the further details for join alogorithms ( Inner LOOP, Merge, Hash) other than BOL.

    Kind Regards,

    Affan

  • March 27, 2003 at 2:58 am

    #452300

    I cannot think of any other way of summing the rows until you reach a set number other than processing each row at a time. Maybe someone else does know of a way.

    Lots of people say don't use cursors but I use them all over the place for record level processing without any problems. This is record level processing

    add row to total

    if more than value exit

    else fetch next row etc

    and a cursor would do the job fine.

    With the pros and cons of using CASE - the pros and cons against using what?

    Jeremy

  • March 27, 2003 at 3:02 am

    #452301

    General hind sides of using case statements.

  • March 27, 2003 at 8:04 am

    #452340

    Here are some useful links relating to join types:

    http://www.sql-server-performance.com/mb_sql_server_joins.asp

    http://www.sql-server-performance.com/hints_join.asp

    -Dan


    -Dan

  • March 27, 2003 at 8:29 am

    #452353

    Maffan76, for some info on joins read this article on sql.nu: http://www.sql.nu/articles/joins.shtml

    Regarding your sum question, is this what you mean?

    SELECT CASE WHEN SUM(somecol) > 1000 THEN 1000 ELSE SUM(somecol) END FROM sometable

    What exactly do you mean with pros and cons of using CASE? There are no general problems using CASE, but of course in some situations there might be better ways of accomplishing something.

    --

    Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

    http://www.sql.nu

    Edited by - chrhedga on 03/27/2003 08:30:56 AM

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • March 27, 2003 at 10:20 pm

    #452417

    Thanks all for the reply.

    Actually i want to distribute a number amongs the first n rows whose sum is equal to the number beign distributed.

    e.g. i have 1000 and i want ot distribute this number among the n rows whose sum accumulates to 1000.

  • March 28, 2003 at 1:58 am

    #452432

    What order do you want for the selection and does the table have a PK or IDENTITY column?

    e.g.

    create table #t (IDno int identity(1,1),val int)

    insert into #t values(34)

    insert into #t values(67)

    insert into #t values(17)

    insert into #t values(93)

    insert into #t values(49)

    insert into #t values(55)

    insert into #t values(92)

    insert into #t values(55)

    insert into #t values(22)

    insert into #t values(64)

    insert into #t values(28)

    insert into #t values(55)

    declare @limit int

    set @limit = 500

    select t.IDno,t.val,

    isnull((select sum(val) as 'sumval' from #t where IDno < t.IDno),0),

    (case when isnull((select sum(val) as 'sumval' from #t where IDno < t.IDno),0)+t.val > @limit then

    ((isnull((select sum(val) as 'sumval' from #t where IDno < t.IDno),0)+t.val)-@limit)

    else t.val end) as 'apportion'

    from #t t

    where isnull((select sum(val) as 'sumval' from #t where IDno < t.IDno),0) <= @limit

    Edited by - davidburrows on 03/28/2003 02:14:23 AM

    Far away is close at hand in the images of elsewhere.
    Anon.

  • March 28, 2003 at 3:01 am

    #452440

    Thanks

    I'll look into that

    Kind Regards,Affan

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply