2

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.

  • The above query gives you dates in 2014 not 2015. – Mark Sinkinson Jun 24 '15 at 12:48
  • @MarkSinkinson: Yes, sorry my mistake. Updated that – B N Jun 24 '15 at 12:50
  • 1
    I don't understand how the above is "hard-coded" - it seems to be dynamic based on today's date. Do you mean you want it to take parameters based on some date the user specifies? – Aaron Bertrand Jun 24 '15 at 13:00
  • @AaronBertrand: Yes, on the basis of DateofJoining. because each and every month new emplyee will join, so the confimation of the employees will be different – B N Jun 24 '15 at 13:02
  • DateOfJoining and Dt_Of_Join are the same column, right? You're going to need to provide some sample data and desired results. This is a word problem right now, and it's hard to decipher what you mean by"dynamic" - the query you've written already seems to be dynamic based on today's date. – Aaron Bertrand Jun 24 '15 at 13:06
4

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>

  • Thank you sir +1 for considering the Feb part which I didn't mentioned. I m out of office now. Will surely test and let you know tomorrow – B N Jun 24 '15 at 15:23
  • getting error as Incorrect syntax near the keyword 'WHERE'. – B N Jun 25 '15 at 5:10
  • got it, sir, cant it be reduced to 2-3 lines of code, as I am already using these in procedure – B N Jun 25 '15 at 5:13
  • @nad sure, you can remove the PRINT statements; those were just for your benefit. So then it's only an additional 2 lines of code on top of your query. That said, it's rather silly to judge things based on "lines of code" - sure we could put those two lines into a single line, but at the cost of readability (and actually that one line would increase by some number of characters, so what is really gained?). – Aaron Bertrand Jun 25 '15 at 10:57
  • Actually I want to use this line of code in a procedure. So here already is one procedure. I want only that line of code that I could add in the procedure which gives me the desired results.sorry to be illogical. – B N Jun 25 '15 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.