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
QRY COMPOSING
