Possible Duplicate:
How do I calculate a running total in SQL without using a cursor?
It's a little difficult to explain, so I'll show what I want with an example:
Lets say we have the following table named MonthProfit
:
[MonthId][Profit]
1, 10 -- January
2, 20 -- February
3, 30
4, 40
5, 50
6, 60
7, 70
8, 80
9, 90
10, 100
11, 110
12, 120 -- December
Column profit
represents the profit for that month.
However, if we have 10 profit in January, and 20 in February, in February we have a total profit of 30.
so I'd like to create a view that shows the following:
[MonthId][Profit][ProfitTotal]
1, 10, 10 -- January
2, 20, 30 -- February
3, 30, 60
4, 40, 100
5, 50, 150
6, 60, 210
7, 70, 280
8, 80, 360
9, 90, 450
10, 100, 550
11, 110, 660
12, 120, 780 -- December
What I did now to solve it, is a view like this:
SELECT [MonthId]
,[Profit]
, (SELECT SUM([Profit])
FROM MonthProfit
WHERE [MonthId] <= outer.[MonthId]) as ProfitTotal
FROM MonthProfit as outer
However, I assume this is pretty slow because it has to recount everything all the time, and it does not seem very elegant to me. Is there a "good" way to do this?
I have tried a small example here for your reference this generates the results as per the requirements
CREATE TABLE [dbo].[tbl_TotalPrevious](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NOT NULL,
[values] [bigint] NOT NULL)
INSERT DATA INTO THE TABLE
insert into tbl_TotalPrevious values ('A', 10)
insert into tbl_TotalPrevious values ('B', 20)
insert into tbl_TotalPrevious values ('C', 10)
insert into tbl_TotalPrevious values ('D', 10)
insert into tbl_TotalPrevious values ('E', 10)
insert into tbl_TotalPrevious values ('F', 10)
insert into tbl_TotalPrevious values ('G', 10)
insert into tbl_TotalPrevious values ('H', 10)
insert into tbl_TotalPrevious values ('I', 10)
insert into tbl_TotalPrevious values ('J', 10)
insert into tbl_TotalPrevious values ('K', 10)
insert into tbl_TotalPrevious values ('L', 10)
insert into tbl_TotalPrevious values ('M', 10)
insert into tbl_TotalPrevious values ('N', 10)
insert into tbl_TotalPrevious values ('O', 10)
insert into tbl_TotalPrevious values ('P', 10)
insert into tbl_TotalPrevious values ('Q', 10)
insert into tbl_TotalPrevious values ('R', 10)
insert into tbl_TotalPrevious values ('S', 10)
insert into tbl_TotalPrevious values ('T', 10)
insert into tbl_TotalPrevious values ('U', 10)
insert into tbl_TotalPrevious values ('V', 10)
insert into tbl_TotalPrevious values ('W', 10)
insert into tbl_TotalPrevious values ('X', 10)
insert into tbl_TotalPrevious values ('Y', 10)
Create a Function eg.
ALTER FUNCTION testtotal
(
@id int
)
RETURNS int
AS
BEGIN
DECLARE @Result int
SELECT @Result = (SELECT SUM([values])
FROM tbl_TotalPrevious
WHERE [id] <= @id)
RETURN @Result
END
GO
RESULTS GENERATED FROM A SINGLE QUERY
SELECT [id],[values], (dbo.testtotal(id)) as TotalVals FROM tbl_TotalPrevious
HOPE THE ABOVE SOLVES YOUR PURPOSE WITH THE TIMING ISSUE AND GENERATES THE DATA FASTER AS REQUIRED.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With