467,915 Members | 1,382 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,915 developers. It's quick & easy.

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

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:

0, 233, 233, 344, 344, 344, 349, 600, 600, 600, 0, 750

233, 0. 111, 0, 0, 5, 251, 0, 0, 750

The correct sequence should be

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

If (there is no value greater than zero)
current input - 0 = correct result
(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):
SET NewCount.DailyHours= @total_yesterday_hours
WHERE NewCount.CountDate = CONVERT(VARCHAR(50), DATEADD(day, -1,
@param_date), 101)
AND NewCount.ID = @customer_id;

SET NewCount.UsageToday =
@total_yesterday_hours - COALESCE((SELECT NewCount.DailyHours FROM
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.

Sep 26 '08 #1
  • viewed: 1665
2 Replies
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
Books Online for SQL Server 2000 at
Sep 29 '08 #2
Thanks Erland, changing the SELECT from '17530101' to '19000101' did
the trick and the code works now. Serious appreciated.

All best,
Sep 29 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by sdhyok | last post: by
reply views Thread by Ed prochak | last post: by
3 posts views Thread by sedaw | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.