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.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..
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.
解决方案Hello Team,
I am closing this case, as I have got solution for it.
=======================================================
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