# QRY COMPOSING

• ### 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,

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 giovedì 23 giugno 2011 14:49
mercoledì 15 giugno 2011 01:15

### 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 giovedì 23 giugno 2011 14:49
mercoledì 15 giugno 2011 01:15
• 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.

venerdì 17 giugno 2011 16:30