Click here to Skip to main content
15,945,864 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello All,

This is Regards of creating a View of Additional column with following requirement.
It would be great if I get some information on this

Example
I have a Table with the column as Specified and Sample value as below.

SQL
TABLENAME

RowNumber UtcTimeStamp           TotalRequest
1          2015-03-04 12:20:00    10441856
2          2015-03-04 12:30:00    10447205
3          2015-03-04 12:40:00    10454634
4          2015-03-04 12:50:00    10461790
5          2015-03-04 13:00:00    10469014
6          2015-03-04 13:10:00    10476494


I would need to create a View as following or PartitionLike below

Like always the First Row "ColumnResult" Column is 0

"ColumnResult" of Row 2 is
And from Row 2 Onwards, "ColumnResult"is =
TotalRequest(Value in Row 2 ) - TotalRequest(Value in Row 1 )

"ColumnResult" of Row 3 is
TotalRequest(Value in Row 3 ) - TotalRequest(Value in Row 2 )
and So on..




SQL
RowNumber UtcTimeStamp           TotalRequest    ColumnResult
1          2015-03-04 12:20:00    10441856       0
2          2015-03-04 12:30:00    10447205       5349
3          2015-03-04 12:40:00    10454634       7429
4          2015-03-04 12:50:00    10461790       7156
5          2015-03-04 13:00:00    10469014       ...
6          2015-03-04 13:10:00    10476494       so on...



Thanks and Regards
Durai Velan C.
Posted
Comments
Herman<T>.Instance 9-Mar-15 6:58am    
How does you query look right now?

1 solution

Hello Team,

I am closing this case, as I have got solution for it.

=======================================================
<pre lang="sql">

CREATE PROCEDURE GetDiffView()
BEGIN
SET @isFirstRow=1;
SET @ColumnThreeValuePrevious=0;

SELECT ColumnOne, ColumnTwo, ColumnThree
FROM ( 
SELECT ColumnOne, ColumnTwo.
@VColumnThree:=ColumnThree as CColumnThree,
(CASE
   WHEN @isFirstRow=1 THEN 0
   ELSE @VColumnThree-@ColumnThreeValuePrevious
END) as ColumnThree,
@ColumnThreeValuePrevious:=@VColumnThree,
@isFirstRow:=0
FROM DBTABLE ) Diff
END


Thanks and Regards
Durai Velan C
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900