Find end date for a month

How we can find the first and last day of any month? This is a very easy code, this function accepts a offset parameter that would help you to find last of any previous months, or coming months. CREATE FUNCTION getLastDayOfMonth (@dt DATE,@offset INT=0) RETURNS DATE AS BEGIN RETURN Dateadd(d,-1,Dateadd(mm,Datediff(m,0,Dateadd(month,1,@dt))+@offset,0)); END GO See this function in action… Continue Reading Find end date for a month

LEAD and LAG functions in SQL 2012

SQL 2012 introduced two new analytical functions LEAD and LAG that would help accessing a preceding and subsequent rows in a query. Consider the subsequent example, we have to find the employees who were absent for two consecutive days   EmployeeID        SigninDate   1 2014-01-03 1 2014-01-04 1… Continue Reading LEAD and LAG functions in SQL 2012

CHOOSE() – SQL 2012

I was looking at the the SQL 2012 features and one T-SQL feature that attracted me is CHOOSE. DECLARE @RECORDS TABLE(ID INT IDENTITY(1,1),NAME VARCHAR(10),STATUS INT); INSERT INTO @RECORDS(NAME,STATUS) VALUES (‘A’,1), (‘B’,2), (‘C’,2), (‘D’,3); SELECT *, CASE STATUS WHEN 1 THEN ‘Working’ WHEN 2 THEN ‘Resigned’ WHEN 3 THEN ‘InActive’ END… Continue Reading CHOOSE() – SQL 2012