SQL

How to use the LAG function in SQL?

LAG function in sql is used when we have data based on time and we want to replace the current data from the previous month’s data.

In Oracle, you could use the LAG function within a case statement like so:

If the data looks like this:

MONTH    |    Value
___________________
JAN2016  |   100.00
FEB2016  |
MAR2016  |   342.60
APR2016  |   450.20

Code:

SELECT
    MONTH,
    CASE WHEN Value is null then LAG(Value OVER MONTH,1) else Value END as Value
FROM
    myTable;

The result would be:

    MONTH    |    Value 
________________________
    JAN2016  |   100.00
    FEB2016  |   100.00
    MAR2016  |   342.60
    APR2016  |   450.20

This solution will work as long as you don’t have two or more consecutive months with a null value… if that is happening then your solution will be a bit more complicated.

Important Notice for college students

If you’re a college student and have skills in programming languages, Want to earn through blogging? Mail us at geekycomail@gmail.com

For more Programming related blogs Visit Us Geekycodes. Follow us on Instagram.

Leave a Reply

%d bloggers like this: