Hi Everyone, I have developed many utilities to automate things up for us in my local machines and I have lost a lot of them. We as a developer usually code develop our own utilities to increase our productivity and use our artistic mind and practice a lot of code in home, office, workshops etc.
We generally keep our repository in our local machines. But sometimes due to system failure, we lose our best works which no1 is aware or project you were working on. Usually, big organization uses a Source Control system to avoid this vandalism like SVN, TFS, Bit Bucket, GitHub etc. With the advent of GitHub and Bit bucket, it’s is easy for developers to keep his magic work to be saved in the cloud that to private to him. Continue reading
Hello, folks today I am going to migrate one of my projects called Know Your Mentor which was built in ASP.NET Core Project.json. As now Microsoft has moved towards old csproj extension which is Visual Studio .NET C# Project file extension. Continue reading
Guid is a 16-byte binary data type that is globally unique. GUID stands for Global Unique Identifier. Guid are globally unique between table, databases, servers. Continue reading
EOMONTH Function is used to return last day of the month of the specified date. Continue reading
Choose function in SQL Server is used to get the item at the specified index from the list of available values. The index position starts with 1.
If the mentioned index is greater or less than the specified values, It results null.
If we pass the float value in the index it will be converted to an integer, based on that the value will be selected.
If function returns the one of the two values, depending on the condition evaluates true or false.
The IIF function is like Ternary operator in a programming language with single condition.
UnPivot is used to convert columns into rows.
We have below mentioned table who columns we want to convert into rows.
Tournament in ([UTU],[Zonal])
) as UnPivotex
The Lead and Lag functions returns subsequent row for the Lead() while previous row for the Lag function.
Lead(Column, OFFSET ?(OPTIONAL),DEFAULT VALUE) OVER (ORDER BY CLAUSE)
LAG (Column, OFFSET ?(OPTIONAL),DEFAULT VALUE) OVER (ORDER BY CLAUSE)
Column specifies the column value to be returned or expression to return.
The number of rows forward from the current row from the first row. Default value for offset is 1.
The default value is set if LEAD() returns NULL.
Here we have below mentioned table records which contains Project details and their respective Business Stakeholders.
So Now we want to see the next project that is needed to be done once the subsequent project gets completed.
So now i want to see my subsequent project with the use of LEAP() function as shown below:
Using LAG() to get the previous row from the column as shown below:
Setting default value to the LAG and LEAD() when functions returns NULL.
We have discussed OFFSET ?(OPTIONAL),DEFAULT VALUE) in syntax. The offset value defines the number of rows forward or back with LEAD() and LAG(). While Default defines the value when function returns NULL. So in order to handle the same we will set default value ‘NoProject’