CODEWITHSUNDEEP
sql-serverfunctiont-sql
Kingsley Schmid
Kingsley Schmid

Reputation: 61

Return difference of comparing percentage value of a column

I have a colum with 10,000 Finincial records. The values are: ID PK, OwingValue Example data

ID   OwingValue
1    123.2
2    123.4
3    123.5
4    123.6
5    123.7
6    140.2
7    140.3

For a giving column with more than 0.7% difference from the previous record, return the records with high difference.

In this case the result will be column 7 because (column 6 OwingValue - column 5 owingValue) multiply by 100 will give 1.650 percent which is higher than our threshold of 0.7% I need an sql while loop or curso that will help me solve this problem.

Thanks in Advance

Upvotes: 0

Views: 110

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Use lag(), but this is the simplest way:

select t.*
from (select t.*,
             lag(owingvalue) over (order by id) as prev_owingvalue
      from table t
     ) t
where owingvale >= 1.07 * prev_owingvalue;

This puts the "current" and previous value in the same row of output.

Upvotes: 0

PP006
PP006

Reputation: 709

Try using LAG function,

SELECT ID,
       OWING_VALUE,
       LAG,
       ( OWING_VALUE - LAG ) / OWING_VALUE * 100 AS INCREASE_PERCENT
FROM   (SELECT *,
               Row_number()
                 OVER (
                   ORDER BY ID) AS RN,
               CASE
                 WHEN Row_number()
                        OVER (
                          ORDER BY ID) = 1 THEN OWING_VALUE
                 ELSE Lag(OWING_VALUE, 1, 0)
                        OVER(
                          ORDER BY ID )
               END              AS LAG
        FROM   #Your_Table)A
WHERE  ( OWING_VALUE - LAG ) / OWING_VALUE * 100 > 0.7

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Try this:

DECLARE @t TABLE ( ID INT, V MONEY )

INSERT  INTO @t
VALUES  ( 1, 123.2 ),
        ( 2, 123.4 ),
        ( 3, 123.5 ),
        ( 4, 123.6 ),
        ( 5, 123.7 ),
        ( 6, 140.2 ),
        ( 7, 140.3 )

SELECT  t1.ID
FROM    @t t1
        JOIN @t t2 ON t1.ID = t2.ID + 1
        JOIN @t t3 ON t2.ID = t3.ID + 1
WHERE   ( t2.v - t3.V ) / t3.V * 100 > 0.7

Output:

ID
7

If your IDs have gaps then:

DECLARE @t TABLE ( ID INT, V MONEY )

INSERT  INTO @t
VALUES  ( 1, 123.2 ),
        ( 2, 123.4 ),
        ( 3, 123.5 ),
        ( 5, 123.6 ),
        ( 9, 123.7 ),
        ( 16, 140.2 ),
        ( 27, 140.3 );


WITH    cte
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY ID ) AS ID ,
                        V
               FROM     @t
             )
    SELECT  t1.ID
    FROM    cte t1
            JOIN cte t2 ON t1.ID = t2.ID + 1
            JOIN cte t3 ON t2.ID = t3.ID + 1
    WHERE   ( t2.v - t3.V ) / t3.V * 100 > 0.7

Upvotes: 1

Related Questions