none
QRY COMPOSING RRS feed

  • Domanda

  • ope  cf  sign
    1    10  1
    1    20  1
    2   40  1
    2   -10 -1
    2   -15 -1
    3   -15 -1
    3   -5  -1
    
    
    


    I need

     

    ope cf = sum(cf) if there is NO sign                     
           change 
           else 
           sum(cf with the same sign)  
    1           30                       
    2           -25 (40 has sign(+))         
    3           -20 

    How Can I compose my query,

    Thanks in advance,

    h.

    martedì 14 giugno 2011 11:00

Risposte

  • hi,

    to me you are missing something.. "how" can you say {2, 40, 1} must be discarded for ope=2 if it's the "first" row for ope=2.. if you consider the "order" of the inserted rows you should discard {2, -10, -1} and {2, -15, -1}... but you still miss, in the design, the ordering responsible colum, the tie breaker you base your "condition" to hold true... so, if we consider this tie breaker to be the insertion order, you can rephrase your scenario like

    SET NOCOUNT ON;
    declare @TAB table (ope int,cf int,[sign] int, idx int)
    insert @TAB
    select 1,10,1,1 UNION ALL
    select 1,20,1,2 UNION ALL
    select 2,40,1,3 UNION ALL
    select 2,-10,-1,4 UNION ALL
    select 2,-15,-1,5 UNION ALL
    select 3,-15,-1,6 UNION ALL
    select 3,-5,-1,7 ;


    WITH cte AS (
     SELECT t.ope, t.cf, t.[sign], t.idx, ISNULL(t2.idx, t.idx) [idx2], ISNULL(t2.[sign], t.[sign]) [sign2]
      FROM @TAB t
      LEFT JOIN @TAB t2
       ON t2.ope = t.ope
        AND t2.idx = (SELECT MIN(t3.idx)
            FROM @TAB t3
            WHERE t3.ope = t.ope
             AND t3.idx < t.idx)
     )
     SELECT c.ope,
      SUM(cf *
       CASE WHEN [sign] = [sign2] THEN 1 ELSE 0 END)
       [cf]
      FROM cte c
      GROUP BY c.ope;
    --<--------
    ope         cf
    ----------- -----------
    1           30
    2           40
    3           -20

    where the idx column serves the ordering proposal... the common table expression, in this trivial sample, project for each row the first available sing, defining the actual "rule" for each partition or group..

    having that order in place, you should argue that, being ope{2, 40, 1} the first row, all successive signs must be + in order to be aggregated.. [or explicitely define and indentify what your tie breaker is :)].. whit these assumptions, you can then discard the values you like to, aggregating the relevant rows only...

    if this is not the case, please appropriately define the rules..

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/ - http://www.hotelsolericcione.de
    • Contrassegnato come risposta HGon giovedì 23 giugno 2011 14:49
    mercoledì 15 giugno 2011 01:15
    Moderatore

Tutte le risposte

  • try :

    declare @TAB table (ope int,cf int,[sign] int)
    insert @TAB
    select 1,10,1 UNION ALL
    select 1,20,1 UNION ALL
    select 2,40,1 UNION ALL
    select 2,-10,-1 UNION ALL
    select 2,-15,-1 UNION ALL
    select 3,-15,-1 UNION ALL
    select 3,-5,-1 
    select ope,sum(cf) as cf,[sign] from @TAB group by ope,[sign]
    order by ope
    --1 30 1
    --2 -25 -1
    --2 40 1
    --3 -20 -1

     

     

    Best regards


    martedì 14 giugno 2011 11:08
  • I don't want this row:

    2 40 1


    because there is a sign changenment..
    martedì 14 giugno 2011 12:37
  • hi,

    to me you are missing something.. "how" can you say {2, 40, 1} must be discarded for ope=2 if it's the "first" row for ope=2.. if you consider the "order" of the inserted rows you should discard {2, -10, -1} and {2, -15, -1}... but you still miss, in the design, the ordering responsible colum, the tie breaker you base your "condition" to hold true... so, if we consider this tie breaker to be the insertion order, you can rephrase your scenario like

    SET NOCOUNT ON;
    declare @TAB table (ope int,cf int,[sign] int, idx int)
    insert @TAB
    select 1,10,1,1 UNION ALL
    select 1,20,1,2 UNION ALL
    select 2,40,1,3 UNION ALL
    select 2,-10,-1,4 UNION ALL
    select 2,-15,-1,5 UNION ALL
    select 3,-15,-1,6 UNION ALL
    select 3,-5,-1,7 ;


    WITH cte AS (
     SELECT t.ope, t.cf, t.[sign], t.idx, ISNULL(t2.idx, t.idx) [idx2], ISNULL(t2.[sign], t.[sign]) [sign2]
      FROM @TAB t
      LEFT JOIN @TAB t2
       ON t2.ope = t.ope
        AND t2.idx = (SELECT MIN(t3.idx)
            FROM @TAB t3
            WHERE t3.ope = t.ope
             AND t3.idx < t.idx)
     )
     SELECT c.ope,
      SUM(cf *
       CASE WHEN [sign] = [sign2] THEN 1 ELSE 0 END)
       [cf]
      FROM cte c
      GROUP BY c.ope;
    --<--------
    ope         cf
    ----------- -----------
    1           30
    2           40
    3           -20

    where the idx column serves the ordering proposal... the common table expression, in this trivial sample, project for each row the first available sing, defining the actual "rule" for each partition or group..

    having that order in place, you should argue that, being ope{2, 40, 1} the first row, all successive signs must be + in order to be aggregated.. [or explicitely define and indentify what your tie breaker is :)].. whit these assumptions, you can then discard the values you like to, aggregating the relevant rows only...

    if this is not the case, please appropriately define the rules..

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/ - http://www.hotelsolericcione.de
    • Contrassegnato come risposta HGon giovedì 23 giugno 2011 14:49
    mercoledì 15 giugno 2011 01:15
    Moderatore
  • Ciao,

    Vorrei ricordare a tutti i partecipanti a questo thread che siamo in una gerarchia italiana.

    Perciò siete pregati di postare in italiano o spostarvi sulla gerarchia americana.

     

    Grazie in anticipo della vostra collaborazione,

    Irina


    Questo contenuto è distribuito “as is” e non implica alcuna responsabilità da parte di Microsoft. L'azienda offre questo servizio gratuitamente, allo scopo di aiutare gli utenti e farli aumentare la conoscenza sui prodotti e le tecnologie Microsoft.

    LinkedIn

    venerdì 17 giugno 2011 16:30