By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,648 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.
Sep 26 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Sep 29 '08 #2

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

All best,
Peter
Sep 29 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.