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 out.

Regards.

pbd22 (dushkin@gmail.com) writes:
> 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

Below is a query that achieves this. I have some problems with mapping
to what you say above to the UPDATE statements you include, so I leave
this for now. If my solution is not enough for you, please include:

o CREATE TABLE statements for you table(s) involved.
o INSERT statements with sample data.
o The desired result of the INSERT.
o The version of SQL Server you are using.

Below is a solution that requires SQL 2005 as it uses the row_number
function, which is extremely useful.

There is a CTE where I number only the rows with a non-zero value.
However, I add a single row with value = 0, which is used to handle
the start of the sequence, where we presumably start from 0.



CREATE TABLE #input(date datetime NOT NULL PRIMARY KEY,
value int NOT NULL)
go
INSERT #input (date, value)
SELECT '20080101', 0
UNION ALL
SELECT '20080102', 233
UNION ALL
SELECT '20080104', 233
UNION ALL
SELECT '20080105', 344
UNION ALL
SELECT '20080106', 344
UNION ALL
SELECT '20080108', 344
UNION ALL
SELECT '20080109', 349
UNION ALL
SELECT '20080110', 600
UNION ALL
SELECT '20080111', 600
UNION ALL
SELECT '20080112', 600
UNION ALL
SELECT '20080113', 0
UNION ALL
SELECT '20080114', 750

; WITH numbered AS (
SELECT date, value,
rowno = row_number() OVER(ORDER BY date)
FROM (SELECT date, value
FROM #input
WHERE value > 0
UNION ALL
SELECT '17530101', 0) AS u
)
SELECT i.date, i.value, coalesce(i.value - n2.value, 0)
FROM #input i
LEFT JOIN (numbered n1 JOIN numbered n2 ON n1.rowno = n2.rowno + 1)
ON i.date = n1.date
ORDER BY i.date
go
DROP TABLE #input



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks Erland!

This is pretty fantastic stuff. Your solution worked for me
(indeed, I am using SQL 2005) but I just need a little bit
of follow-up. Namely, I need to deal with the actual table and not
temp tables as I need to keep as a record the column that shows
the daily value differences.

TABLE DESCRIPTION:

Here is the description of the table I am working with. I have changed
some names around otherwise my company would boot me

USE [DB]
GO
/****** Object: Table [dbo].[My_Table] Script Date: 09/27/2008
22:38:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[My_Table](
[CPID] [int] NOT NULL,
[CountDate] [smalldatetime] NOT NULL,
[ViewCount] [int] NULL,
[EmbedCount] [int] NULL CONSTRAINT [DF_My_Table_EmbedCount] DEFAULT
((0)),
[DirectCount] [int] NULL CONSTRAINT [DF_My_Table_DirectCount]
DEFAULT ((0)),
[CID] [int] IDENTITY(1,1) NOT NULL,
[DailyIncrements] [int] NOT NULL CONSTRAINT
[DF__My_Table_Usage__19DFD96B] DEFAULT ((0)),
[EmbedSLCount] [int] NULL CONSTRAINT [DF_My_Table_EmbedSLCount]
DEFAULT ((0)),
[DailyValues] [int] NULL CONSTRAINT [DF_My_Table_DailyValues]
DEFAULT ((0)),
CONSTRAINT [PK_My_Table] PRIMARY KEY CLUSTERED
(
[CPID] ASC,
[CountDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

** The column DailyValues is the column that gets the growing series
of numbers (0, 34, 60, 0, 99, etc...) as daily values.

** The column DailyIncrements is the column that must show the
difference between any two increments greater than zero (excluding the
first zero).

INSERT STATEMENT

Here is the relevant data for an insert:

INSERT INTO My_Table
([CPID]
,[CountDate]
,[DailyValues])
VALUES
(84,
,'09/26/2008'
,1000)

DESIRED RESULT OF THE INSERT:

****The insert statement runs a few minutes past midnight. ****

STEP ONE - It takes the total value for the previous day and inserts
it into the DailyValue column.

STOP TWO: It then subtracts the number in the DailyValue column from
two days ago from the number just added to yesterday and adds the
result in yesterday's DailyIncrement column.

So, the desired result of the above insert would be:

CPID CountDate DailyIncrement DailyValues
84 09/24/2008 0 975
84 09/25/2008 0 0
84 09/26/2008 25 1000

The code executed on 09/27/2008 at a few minutes after
midnight.

I applied the code you gave me to this table and it worked great but,
as I said above, I need the results in the actual My_Table, not in a
temp table. I am somewhat unclear on how to change the code you
provided to do this. I am assuming "INSERT #input... " becomes "INSERT
My_Table..." but I start to get lost at the WITH statement.

CREATE TABLE #input(date datetime NOT NULL PRIMARY KEY,
value int NOT NULL)
go
INSERT #input (date, value)
SELECT CountDate, DailyValues
FROM My_Table
WHERE CPID = @param_cpid
; WITH numbered AS (
SELECT date, value,
rowno = row_number() OVER(ORDER BY date)
FROM (SELECT date, value
FROM #input
WHERE value > 0
UNION ALL
SELECT '17530101', 0) AS u
)
SELECT i.date, i.value, coalesce(i.value - n2.value, 0)
FROM #input i
LEFT JOIN (numbered n1 JOIN numbered n2 ON n1.rowno = n2.rowno +
1)
ON i.date = n1.date
ORDER BY i.date
go
DROP TABLE #input

Thanks a ton again for your . Much appreciated.

Thanks Erland!

This is pretty fantastic stuff. Your solution worked for me
(indeed, I am using SQL 2005) but I just need a little bit
of follow-up. Namely, I need to deal with the actual table and not
temp tables as I need to keep as a record the column that shows
the daily value differences.

TABLE DESCRIPTION:

Here is the description of the table I am working with. I have changed
some names around otherwise my company would boot me

USE [DB]
GO
/****** Object: Table [dbo].[My_Table] Script Date: 09/27/2008
22:38:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[My_Table](
[CPID] [int] NOT NULL,
[CountDate] [smalldatetime] NOT NULL,
[ViewCount] [int] NULL,
[EmbedCount] [int] NULL CONSTRAINT [DF_My_Table_EmbedCount] DEFAULT
((0)),
[DirectCount] [int] NULL CONSTRAINT [DF_My_Table_DirectCount]
DEFAULT ((0)),
[CID] [int] IDENTITY(1,1) NOT NULL,
[DailyIncrements] [int] NOT NULL CONSTRAINT
[DF__My_Table_Usage__19DFD96B] DEFAULT ((0)),
[EmbedSLCount] [int] NULL CONSTRAINT [DF_My_Table_EmbedSLCount]
DEFAULT ((0)),
[DailyValues] [int] NULL CONSTRAINT [DF_My_Table_DailyValues]
DEFAULT ((0)),
CONSTRAINT [PK_My_Table] PRIMARY KEY CLUSTERED
(
[CPID] ASC,
[CountDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

** The column DailyValues is the column that gets the growing series
of numbers (0, 34, 60, 0, 99, etc...) as daily values.

** The column DailyIncrements is the column that must show the
difference between any two increments greater than zero (excluding the
first zero).

INSERT STATEMENT

Here is the relevant data for an insert:

INSERT INTO My_Table
([CPID]
,[CountDate]
,[DailyValues])
VALUES
(84,
,'09/26/2008'
,1000)

DESIRED RESULT OF THE INSERT:

****The insert statement runs a few minutes past midnight. ****

STEP ONE - It takes the total value for the previous day and inserts
it into the DailyValue column.

STOP TWO: It then subtracts the number in the DailyValue column from
two days ago from the number just added to yesterday and adds the
result in yesterday's DailyIncrement column.

So, the desired result of the above insert would be:

CPID CountDate DailyIncrement DailyValues
84 09/24/2008 0 975
84 09/25/2008 0 0
84 09/26/2008 25 1000

The code executed on 09/27/2008 at a few minutes after
midnight.

I applied the code you gave me to this table and it worked great but,
as I said above, I need the results in the actual My_Table, not in a
temp table. I am somewhat unclear on how to change the code you
provided to do this. I am assuming "INSERT #input... " becomes "INSERT
My_Table..." but I start to get lost at the WITH statement.

CREATE TABLE #input(date datetime NOT NULL PRIMARY KEY,
value int NOT NULL)
go
INSERT #input (date, value)
SELECT CountDate, DailyValues
FROM My_Table
WHERE CPID = @param_cpid
; WITH numbered AS (
SELECT date, value,
rowno = row_number() OVER(ORDER BY date)
FROM (SELECT date, value
FROM #input
WHERE value > 0
UNION ALL
SELECT '17530101', 0) AS u
)
SELECT i.date, i.value, coalesce(i.value - n2.value, 0)
FROM #input i
LEFT JOIN (numbered n1 JOIN numbered n2 ON n1.rowno = n2.rowno +
1)
ON i.date = n1.date
ORDER BY i.date
go
DROP TABLE #input

Thanks a ton again for your . Much appreciated.

pbd22 (dushkin@gmail.com) writes:
> I applied the code you gave me to this table and it worked great but,
> as I said above, I need the results in the actual My_Table, not in a
> temp table. I am somewhat unclear on how to change the code you
> provided to do this. I am assuming "INSERT #input... " becomes "INSERT
> My_Table..." but I start to get lost at the WITH statement.
>
> CREATE TABLE #input(date datetime NOT NULL PRIMARY KEY,
> value int NOT NULL)
> go
> INSERT #input (date, value)
> SELECT CountDate, DailyValues
> FROM My_Table
> WHERE CPID = @param_cpid
> ; WITH numbered AS (
> SELECT date, value,
> rowno = row_number() OVER(ORDER BY date)
> FROM (SELECT date, value
> FROM #input
> WHERE value > 0
> UNION ALL
> SELECT '17530101', 0) AS u
> )
> SELECT i.date, i.value, coalesce(i.value - n2.value, 0)
> FROM #input i
> LEFT JOIN (numbered n1 JOIN numbered n2 ON n1.rowno = n2.rowno +
> 1)
> ON i.date = n1.date
> ORDER BY i.date

The syntax when you have a Common Table Expression (CTE), which is that
the WITH thing is know as and INSERT is really poor:

WITH (....)
INSERT (....)
SELECT ....

But if you are only inserting a single row, it may be easier to write:

INSERT MyTable(...)
SELECT @CPID, @date, @todaysvalue,
(SELECT @todaysvalue - DailyValues
FROM (SELECT DailyValues,
rowno = row_number()
OVER(ORDER BY CountDate DESC)
FROM (SELECT CountDate, DailyValues
FROM MyTable
WHERE DailyValues > 0
AND CPID = @CPID
UNION ALL
SELECT '17530101', 0) AS u) AS n
WHERE rowno = 1)



A CTE is very similar to a derived table, but in addition to that it
appears in different places in the query, there are two more differences:
1) WITH introduces a name that can be used in several places in the query.
2) A CTE can refer to itself, which is good for hierarchical queries.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks again Erland.

I have been trying to run your provided code (thanks again).

I am running into this error:

"The conversion of char data type to smalldatetime data type resulted
in an out-of-range smalldatetime value."

This error seems like it should be easily solved with a CAST somewhere
but I have tried the obvious things (eg. casting @date to
SMALLDATETIME)
and I haven't been able to get it to work.

so, I broke the code that I have been trying below into individual
select
statements:

INSERT My_Table(CPID, CountDate, DailyValues, DailyIncrement)
SELECT @CPID, @date, @dailyvalue,
(SELECT @dailyvalue - DailyValues
FROM (SELECT DailyValues,
rowno = row_number()
OVER(ORDER BY CountDate DESC)
FROM (SELECT CountDate, DailyValues
FROM My_Table
WHERE DailyValues > 0
AND CPID = @CPID
UNION ALL
SELECT '17530101', 0) AS u) AS n
WHERE rowno = 1)


It looks like when I run this statement:

SELECT CountDate, DailyValues
FROM My_Table
WHERE DailyValues > 0
AND CPID = @CPID
UNION ALL
SELECT '17530101', 0;

I still get the error.

But, when I run the same statement like this:

SELECT CountDate, DailyValues
FROM My_Table
WHERE DailyValues > 0
AND CPID = @CPID;

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

Would you mind taking a look at the code block I provided you
and telling me what I did wrong?

Thanks again for all your !

pbd22 (dushkin@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, esquel@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

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

All best,
Peter