How To Look At Chronological Series To Find the First Values GreaterThan Zero?

[pbd22]

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.

[Erland Sommarskog]

pbd22 (du*****@gmail.com) writes:

The error goes away. Since I am not entirely clear on what
SELECT '17530101' is doing, my utility sort of ends there.

It is intended to produce the date 1753-01-01 which is the first date for
the datetime data type. Since you use smalldatetime, '19000101' is a better
choice. But you could use 1990-01-01 or whatever, as long as it's before any
date that appears in your data.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pbd22]

Thanks Erland, changing the SELECT from '17530101' to '19000101' did
the trick and the code works now. Serious appreciated.

All best,
Peter