|
Hi Folks.
I have two columns, for simplicity: Input and Output.
Output reflects the incremental difference of two chronolgocially
consecutive inputs.
problem: If a given input in the series is zero, then
the result of the subtraction is not going to be incremental,
but will add the full amount of the input into the output field
as illustrated below:
input
0, 233, 233, 344, 344, 344, 349, 600, 600, 600, 0, 750
output
233, 0. 111, 0, 0, 5, 251, 0, 0, 750
The correct sequence should be
output
233, 0. 111, 0, 0, 5, 251, 0, 0, 150
I need to know how to go backwards in the series by date
until I hit a value that is greater than zero and then perform the
calculation:
If (there is no value greater than zero)
current input - 0 = correct result
Else
(current input) - (most recent value 0) = correct result
The SQL I have so far just takes the most recent input and adds it to
the current date in the input column and then does the subtraction
(but I need to do the above):
Code:
UPDATE NewCount
SET NewCount.DailyHours= @total_yesterday_hours
WHERE NewCount.CountDate = CONVERT(VARCHAR(50), DATEADD(day, -1,
@param_date), 101)
AND NewCount.ID = @customer_id;
UPDATE NewCount
SET NewCount.UsageToday =
@total_yesterday_hours - COALESCE((SELECT NewCount.DailyHours FROM
NewCount
WHERE NewCount.CountDate = CONVERT(VARCHAR(50), DATEADD(day, -2,
@param_date), 101)
AND NewCount.ID = @customer_id), 0)
WHERE NewCount.CountDate = CONVERT(VARCHAR(50), DATEADD(day, -1,
@param_date), 101)
AND NewCount.ID = @customer_id;
Thanks tons to anybody who cares to help out.
Regards.
| 
