Prime Study Hub

LAG Function in SQL Server The LAG function is used to access previous row data along with current row data. This function was introduced in SQL Server 2012. Using this function is easy to compare values in the current row with values in the previous row. It is just the opposite of LEAD function. The syntax for LAG function is, LAG([scalar_expression], offset, default_value) OVER([partition_by_clause] [order_by_clause]) In LAG function partition_by_clause is optional, order_by_clause is required. You can specify any number of columns in the order_by_clause. The  offset  denotes the number of rows to lag. The  default_value  to return if the number of rows to lag goes beyond last row in a table or partition. If the  default_value  is not specified NULL is returned. In order to achieve this we are going to use "tbl_EmployeeDetails" table for our demo. CREATE TABLE [dbo].[tbl_EmployeeDetails]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [Employee] [varchar](

LEAD Function in SQL Server The LEAD function is used to access subsequent row data along with current row data. This function was introduced in SQL Server 2012. Using this function is easy to compare values in the current row with values in the subsequent row. The syntax for LEAD function is, LEAD([scalar_expression], offset, default_value) OVER([partition_by_clause] [order_by_clause]) In LEAD function partition_by_clause is optional, order_by_clause is required.You can specify any number of columns in the order_by_clause. The offset denotes the number of rows to lead. The default_value to return if the number of rows to lead goes beyond first row in a table or partition. If the default_value is not specified NULL is returned. In order to achieve this we are going to use "tbl_EmployeeDetails" table for our demo. CREATE TABLE [dbo].[tbl_EmployeeDetails]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [Employee] [varchar](450) NULL, [Salary] [decimal](18,

FIRST VALUE Function in SQL Server The FIRST_VALUE()  SQL Server analytical function is used to return first value in an ordered set of list. The syntax for  FIRST_VALUE ()  function is as follows, FIRST_VALUE([scalar_expression]) OVER([partition_by_clause] [order_by_clause] [rows_range_clause]) The scalar_expression is an expression that returns single value. It does not permit other analytic functions. The partition_by_clause divides the returned list into partitions, Otherwise the function treats all the result list as a single group. The order_by_clause specifies which order the result set should be displayed?. The rows_range_clause specifies the range of rows to be returned in a result set. Lets see this in detail using an example, We are going to use "tbl_BankDetails" table for our demo. CREATE TABLE [dbo].[tbl_BankDetails]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [AccNo] [varchar](450) NULL, [Month] [bigint] NULL, [Balance] [decimal](18,

LAST VALUE Function in SQL Server The LAST_VALUE() SQL Server analytical function is used to return last value in an ordered set of list. The syntax for LAST_VALUE() function is as follows, LAST_VALUE([scalar_expression]) OVER([partition_by_clause] order_by_clause rows_range_clause) The scalar_expression is an expression that returns single value. It does not permit other analytic functions. The partition_by_clause splits the returned list into partitions, Otherwise the function treats all the result list as a single group. The order_by_clause specifies which order the result set should be displayed?. The rows_range_clause specifies the range of rows to be returned in a result set. Lets see this in detail using an example, We are going to use "tbl_BankDetails" table for our demo. CREATE TABLE [dbo].[tbl_BankDetails]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [AccNo] [varchar](450) NULL, [Month] [bigint] NULL, [Balance] [decimal](18, 0) NULL,  CON

Manipulate JSON data in SQL Server JavaScript Object Notation (JSON) is a lightweight popular data exchangeable format used across modern IoT platforms, web and mobile applications etc. It is a language independent textual data format. JavaScript Object Notation (JSON) is also used for storing unstructured data in log files or NoSQL Databases such as Microsoft Azure Cosmos DB. Many RESTful web services that allow us to store and retrieve JSON formatted texts among different protocols using different Endpoints. The example of JSON text is as follows, [{         "customer":"Michael",         "products":["Watch","Mobile","Books"] }, {         "customer":"Stuart",         "products":["Laptop","Keyboard","Mouse"] } ] In this article you will understand the following, Read JSON data from table Modify JSON data in a table Validate JSON objects