So this isn't a great answer, this is kind of a starting answer for somebody else to take on and refine this better. But I'll make a stab at it.
First I have a question: Are you trying to retain this in a view? I don't think you can for what you're wanting to do, it's kinda complicated, so let's examine the operations that you need to do to actually do what you want.
You stated that you want the first 12 rows to be static every time, and they should always have their last column set as NULL, and the others should retain their value. So that's a business rule that we need to encode in SQL. But before we encode this as a rule, let's ask if there's a way to ENSURE that those 12 rows are the RIGHT rows every time. If we can make that assumption, then we can do this as part of the next step.
You're next requirement is to do a calculation on each row with the previous row. Since the first 12 rows are static (and I presume not calculated) then we don't have to ask "what about the first row". So the easiest way to do calculations on the previous row is to assign a rownum to each row, then use the rownum ID in a comparison. This meshes with the previous requirement.
So we should start by doing our select and assigning a rownum as well, like this:
SELECT
ROW_NUMBER() OVER (ORDER BY in.I_Date) AS rownum,
in.I_Date ,--Date
in.I_O_P ,--Money
in.I_O_H ,--Money
in.I_O_L ,--Money
in.I_C_O ,--Money
c.AMPS12_C --Money
CAST(0.0 AS Money) AS C12WR
FROM
dbo.IC_Raw_In in
INNER JOIN
dbo.AMPS12_C c ON in.I_Serial = c.i_serial
But for the way I would do this, I would funnel these values into a temp table, and then use that to work out what I need. That way you can just refer to the columns in subsequent calls, like this:
UPDATE t
SET C12WR = NULL
FROM temptable t
WHERE t.rownum < 12 -- see how we set the values = null here?
UPDATE t
SET C12WR = 510.3958
FROM temptable t
WHERE t.rownum = 12 -- see how we set the value to something static?
-- If this were a stored procedure we could use a value passed in here
and then we continue with:
UPDATE t
SET C12WR = ( ( t2.C12WR * 11.0 ) + t.I_C_O ) / 12.0
FROM temptable t
INNER JOIN temptable t2 ON t.rownum = (t2.rownum - 1) -- this let's us get the previous row
WHERE t.rownum > 12
Using this logic: After the 13th row, the C12WR column = (prevrow.C12WR * 11 + currow.I_C_O Column) / 12
And then you would just return the values that you wanted from the temptable.
Notice: the things I left off. I did not define the temp table, I did not get rid of the temptable. I did not use appropriate syntax for the temptable addressing. I did not validate anything. I presumed that this was going to be used in a stored procedure. I did not illustrate how to use the static value as a stored procedure passed parameter.
Hope this helps. Hope someone else helps make this a better answer ;)
As I read the question, the basic recursive algorithm required is:
- Return the row with the earliest date in the set
- Set that date as "current"
- Find the row with the earliest date more than 90 days after the current date
- Repeat from step 2 until no more rows are found
This is relatively easy to implement with a recursive common table expression.
For example, using the following sample data (based on the question):
DECLARE @T AS table (TheDate datetime PRIMARY KEY);
INSERT @T (TheDate)
VALUES
('2014-01-01 11:00'),
('2014-01-03 10:00'),
('2014-01-04 09:30'),
('2014-04-01 10:00'),
('2014-05-01 11:00'),
('2014-07-01 09:00'),
('2014-07-31 08:00');
The recursive code is:
WITH CTE AS
(
-- Anchor:
-- Start with the earliest date in the table
SELECT TOP (1)
T.TheDate
FROM @T AS T
ORDER BY
T.TheDate
UNION ALL
-- Recursive part
SELECT
SQ1.TheDate
FROM
(
-- Recursively find the earliest date that is
-- more than 90 days after the "current" date
-- and set the new date as "current".
-- ROW_NUMBER + rn = 1 is a trick to get
-- TOP in the recursive part of the CTE
SELECT
T.TheDate,
rn = ROW_NUMBER() OVER (
ORDER BY T.TheDate)
FROM CTE
JOIN @T AS T
ON T.TheDate > DATEADD(DAY, 90, CTE.TheDate)
) AS SQ1
WHERE
SQ1.rn = 1
)
SELECT
CTE.TheDate
FROM CTE
OPTION (MAXRECURSION 0);
The results are:
╔═════════════════════════╗
║ TheDate ║
╠═════════════════════════╣
║ 2014-01-01 11:00:00.000 ║
║ 2014-05-01 11:00:00.000 ║
║ 2014-07-31 08:00:00.000 ║
╚═════════════════════════╝
With an index having TheDate
as a leading key, the execution plan is very efficient:
You could choose to wrap this in a function and execute it directly against the view mentioned in the question, but my instincts are against it. Usually, performance is better when you select rows from a view into a temporary table, provide the appropriate index on the temporary table, then apply the logic above. The details depend on the details of the view, but this is my general experience.
For completeness (and prompted by ypercube's answer) I should mention that my other go-to solution for this type of problem (until T-SQL gets proper ordered set functions) is a SQLCLR cursor (see my answer here for an example of the technique). This performs much better than a T-SQL cursor, and is convenient for those with skills in .NET languages and the ability to run SQLCLR in their production environment. It may not offer much in this scenario over the recursive solution because the majority of the cost is the sort, but it is worth mentioning.
Best Answer
It seems you need a running total.
If
tot_qty
is the same in all rows then you can useROWS UNBOUNDED PRECEDING
is the abbreviated version ofROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
.This window frame-ing option means that for each row the
SUM
will include itself and all predecessors as ordered byid
.The
ROWS
option is potentially important for efficiency as described in Best approaches for running totals – updated for SQL Server 2012