Principale utente con più risposte
QRY COMPOSING

Domanda
-
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 -20where 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
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
-
-
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 -20where 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
-
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.