Important Date and Time functions for reporting

Lag - In Oracle/PLSQL, the lag function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from a previous row in the table. To return a value from the next row, try using the lead function.


The syntax for the lag function is:
lag ( expression [, offset [, default] ] )
over ( [ query_partition_clause ] order_by_clause )
expression is an expression that can contain other built-in functions, but can not contain any analytic functions.offset is optional. It is the physical offset from the current row in the table. If this parameter is omitted, the default is 1.default is optional. It is the value that is returned if the offset goes out of the bounds of the table. If this parameter is omitted, the default is null.

Simple example using LAG function can be as below.

lag(closingperiod([Year],MEMBER),1)
In the above function

closingperiod([Year],MEMBER) provides us with the latest year, for example 2009.

lag(closingperiod([Year],MEMBER),1) provides us with the year prior to current year, for example it will be 2008.

Closingperiod() - The ClosingPeriod() function, according to the Analysis Services Books Online, "returns the last sibling among the descendants of a member at a specified level." In other words, it returns the last member that shares the current member's parent at the specified level.

ClosingPeriod([<>[, <>]])