Hi NG,
I'm trying to solve the following challange, but I have a black out.

I have a table like following:

No Event Money Org
1 1 35 1081
2 1 76 1081
3 1 99 1081
4 1 109 1082
5 1 100 1082

Now I would like to add an additional column MoneySum, which contains
the sum of the current Money value and the below the current record.
It should look like(pro Org):


No Event Money Org MoneySum
1 1 35 1081 35
2 1 76 1081 111 --(35+76)
3 1 99 1081 210 --(35+76+99)
4 1 109 1082 109
5 1 100 1082 209 --(109+100)


Do you have hints? I would like to do it without Cursor loop if
possible.
Is it possible by CTE?

Regards and thanks
Marcel

-- So with some example data as below

DECLARE @t TABLE (
No INT NOT NULL,
Event INT NOT NULL,
Money INT NOT NULL,
Org INT NOT NULL
);

INSERT INTO @t VALUES
(1, 1, 35, 1081),
(2, 1, 76, 1081),
(3, 1, 99, 1081),
(4, 1, 109, 1082),
(5, 1, 100, 1082);

-- Assume:
-- that we are grouping by Event and Org,
-- that we use No for the sequence/order,
-- and that we want to calculate (& display) the "running"/sub-totals
(cumulatively) within each group.

-- Note that if all we wanted were the sum per group, we could use SQL
Server's SUM() OVER (PARTITION BY ...):
SELECT Event, Org, No, Money, SUM(Money) OVER (PARTITION BY Event,
Org) AS MoneySum
FROM @t
--ORDER BY (Money % (No * 2))--tests that sums/grouping are calculated/
performed before/independent of sorting

-- Event Org No Money MoneySum
-- 1 1081 1 35 210
-- 1 1081 2 76 210
-- 1 1081 3 99 210
-- 1 1082 4 109 209
-- 1 1082 5 100 209

-- However, we want to show the partial subtotals within each group
-- (which, for display and human checking, is going to be a lot nicer
with a proper sequence defined).

-- We first want to create/define the grouping:

SELECT
DENSE_RANK() OVER(ORDER BY Event, Org ) Grp,
ROW_NUMBER() OVER(PARTITION BY Event, Org ORDER BY No) Seq,
Event, Org, No, Money
FROM @t;

-- Grp Seq Event Org No Money
-- 1 1 1 1081 1 35
-- 1 2 1 1081 2 76
-- 1 3 1 1081 3 99
-- 2 1 1 1082 4 109
-- 2 2 1 1082 5 100

-- Next, using this, we can create two Common Table Expressions to
solve the problem:

WITH
GroupingCTE (GrpNumber, SeqNumber, Event, Org, No, Money)
AS
(
SELECT
DENSE_RANK() OVER(ORDER BY Event, Org) GrpNumber,
ROW_NUMBER() OVER(PARTITION BY Event, Org ORDER BY No) SeqNumber,
-- caveat: if we use * here in stead of the explicit columns
below, we will be summing Org, not Money
Event, Org, No, Money
FROM @t
),
CalculusCTE (GrpNumber, SeqNumber, Event, Org, No, Money, CumSum)
AS
(
SELECT GrpNumber, SeqNumber, Event, Org, No, Money, Money AS
CumSum
FROM GroupingCTE
WHERE SeqNumber = 1

UNION ALL

SELECT a.GrpNumber, a.SeqNumber, a.Event, a.Org, a.No, a.Money,
a.Money + b.CumSum
FROM GroupingCTE a
INNER JOIN CalculusCTE b
ON a.GrpNumber = b.GrpNumber
AND a.SeqNumber = b.SeqNumber + 1
)
SELECT Event, Org, No, Money, CumSum
FROM CalculusCTE
ORDER BY GrpNumber, SeqNumber

-- Event Org No Money CumSum
-- 1 1081 1 35 35
-- 1 1081 2 76 111
-- 1 1081 3 99 210
-- 1 1082 4 109 109
-- 1 1082 5 100 209