Sql-server – Get employee names on the basis of Date

sql serversql-server-2005

I want a dynamic query which will fetch the employee name who has completed successfully 6 months and are ready for confirmation.

I have written a hardcode query does that. Here is the query

SELECT [emp_name] FROM [emp_mst] 
  WHERE Dt_Of_Join >= dateadd(m, -7, datediff(d, 8, getdate())) 
  and   Dt_Of_Join <  dateadd(m, -6, datediff(d, 9, getdate()))

For example: if employee has Joined on 1st dec,2014, he will complete 6 months on 31st May 2015.

So in june he can be intimated on any date. The above query gives me the result for Nov16 2014 to Dec 15 2014, based on today's date. I want it to be handled dynamically

Please suggest.

I am using sql server 2005.

Best Answer

Today, without time, is:

DECLARE @d SMALLDATETIME;
SET @d = DATEADD(DAY, GETDATE(), DATEDIFF(DAY, '19000101', GETDATE());

-- when you move past SQL Server 2005, which you should, you can use the much tidier:
-- DECLARE @d DATE = SYSDATETIME();

Then to move to the first day of this month:

SET @d = DATEADD(DAY, 1-DAY(@d), @d);

Now to get all employees who started within the month 6 months earlier, you can say:

DECLARE @d SMALLDATETIME;

SET @d = DATEADD(DAY, 1-DAY(GETDATE()), DATEDIFF(DAY, '19000101', GETDATE()));    

SELECT ... 
  WHERE Dt_Of_Join >= DATEADD(MONTH,-6,@d)
    AND Dt_Of_Join <  DATEADD(MONTH,-5,@d);
-- I assume you'll have other filters to make
-- sure employee is still employed, etc.

The smart way to do this would be through a parameter, which falls back to today if no date is supplied.

CREATE PROCEDURE dbo.GetNamesByDate -- not the name you should use
  @dt SMALLDATETIME = NULL
AS
BEGIN
  SET NOCOUNT ON;
  SET @dt = COALESCE(@dt, GETDATE());
  SET @dt = DATEADD(DAY, 1-DAY(@dt), DATEDIFF(DAY, '19000101', @dt));

  SELECT ... 
  WHERE Dt_Of_Join >= DATEADD(MONTH,-6,@dt)
    AND Dt_Of_Join <  DATEADD(MONTH,-5,@dt);

  -- for debugging only:
  PRINT '>= ' + CONVERT(CHAR(10), DATEADD(MONTH,-6,@dt), 120);
  PRINT '<  ' + CONVERT(CHAR(10), DATEADD(MONTH,-5,@dt), 120);
END
GO

(Now it's also very easy to test that this works for any date, including February 29th in leap years...)

Here is the illogical way to do it in one line (I have absolutely no clue why you think this is a valid or important requirement):

ALTER PROCEDURE dbo.GetNamesByDate -- not the name you should use
  @dt SMALLDATETIME = NULL
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ... WHERE Dt_Of_Join >= DATEADD(MONTH,-6,DATEADD(DAY, 1-DAY(COALESCE(@dt, GETDATE())), DATEDIFF(DAY, '19000101', COALESCE(@dt, GETDATE())))) AND Dt_Of_Join <  DATEADD(MONTH,-5,DATEADD(DAY, 1-DAY(COALESCE(@dt, GETDATE())), DATEDIFF(DAY, '19000101', COALESCE(@dt, GETDATE()))));
END
GO

Oh yeah, that's much easier to read and troubleshoot. Also, it didn't save you any characters, it's actually more... </facepalm>

Related Question