One of the readers of the blog has sent me a question regarding how to use the DECODE function in SQL Server.
SELECT DECODE(Letters, 'First',1, 'Second',2, 'Third',3, 0) AS LN FROM LettersTable
In SQL Server the equivalent code is CASE statement. Here are the examples regarding how DECODE can be written in SQL Server.
SELECT CASE WHEN Letters = 'First' THEN 1 WHEN Letters = 'Second' THEN 2 WHEN Letters = 'Third' THEN 3 ELSE 0 END AS LN FROM LettersTable
Here are few alternative blog posts.
Sometimes back I posted about interesting function AGENT_DATETIME functions which will convert integers into datetime values. Someone commented that there might be some permission issues using this function which is only available in msdb database. So I write this post to give you alternate method without using any such functions.
One of the best kept secrets of T-SQL window functions, also called windowing or windowed functions, is framing. Framing was introduced with SQL Server 2012, and it allows the set of rows, or window, to be defined very granularly. For example, when calculating a running total, each row needs to see an increasing number of values to perform the calculation. Row 1 needs to see just row 1. Row 2 needs to see rows 1 and 2. Row 3 needs to see rows 1, 2, and 3. Framing allows each row to have a distinct set of rows for the window function calculation.
Reference: Pinal Dave (https://blog.sqlauthority.com)