-1

I have a table with a lot of employees, let's say 'Employees' table. This table have a date column among others. Let's say this column is called, 'InjuryDate'.

I would like to select all the employees that satisfy below conditions:

  1. InjuryDate is within current month, for example, if current month is July, I want all employees that: InjuryDate >= 01/07/2017 and InjuryDate <= 31/07/2017.
  2. InjuryDate is between day 20 of the past month and the first day of the current month. For example, if current month is July, I want all employees that: InjuryDate >= 20/06/2017 and InjuryDate < 01/07/2017.

So taken into account what is said here (Taken into account that SQL Server can use an index on the column, if appropriate), I have done below query:

DECLARE @today datetime = getdate()

DECLARE @Day int  = 20
DECLARE @Month int  = MONTH(dateadd(month, -1, @today))
DECLARE @Year int = YEAR(dateadd(month, -1, @today))
DECLARE @EarlyDate datetime = cast(cast(@Year*10000 + @Month*100 + @Day as varchar(255)) as date)

SELECT *
FROM   Employees
WHERE  (
         -- Condition 1
         InjuryDate >= cast(@today - day(@today) + 1 as date) 
            AND
         InjuryDate < dateadd(month, 1, cast(@today - day(@today) + 1 as date) )
       )
         OR
       (    
         -- Condition 2
         InjuryDate >= @EarlyDate
            AND 
         InjuryDate < cast(@today - day(@today) + 1 as date) 
       )

Is it correct, or is there any other better way to do it?

I am using SQL Server 2008.

Astro
  • 367
  • 1
  • 3
  • 17

3 Answers3

2

Try this:

DECLARE @today date = getdate();

DECLARE @todayDay int  = datepart(day, @today);
DECLARE @earlyDate date = dateadd(day, 19, DATEADD(month, DATEDIFF(month, 0, @today) - 1, 0));
DECLARE @nextMonthFirstDayDate date = DATEADD(month, DATEDIFF(month, 0, @today) + 1, 0);

SELECT *
FROM (
        VALUES  (1, '2017-06-19'),
                (2, '2017-06-20'),
                (3, '2017-07-19'),
                (4, '2017-07-31'),
                (5, '2017-08-01')
     ) AS Employees(Id,InjuryDate)
WHERE  InjuryDate >= @earlyDate AND InjuryDate < @nextMonthFirstDayDate;

Output:

Id          InjuryDate
----------- ----------
2           2017-06-20
3           2017-07-19
4           2017-07-31
Ruslan K.
  • 1,912
  • 1
  • 15
  • 18
1

Basically what you need is to get all the records from the last month 20th until last day of the current month. You do need OR here.

DECLARE @end_range DATE= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),
    @start_rage DATE= DATEADD(m, DATEDIFF(m, 0, DATEADD(m, DATEDIFF(m, 0, GETDATE()), -1)), 19)  

SELECT *
FROM   Employees
WHERE InjuryDate BETWEEN @start_range AND @end_rage
Dmitrij Kultasev
  • 5,447
  • 5
  • 44
  • 88
  • why you use seconds in @start_range? Is it necessary? or only taken into account the date and discarding the time is enough? Also in you answer you have switch start_range and end_range, correct it please. – Astro Jul 28 '17 at 10:25
  • Also my InjuryDate field is only a date column, I mean, it has no time, time is set to 00:00:00.0. In the same table I have another column 'InjuryTime' which contains only the time (no date). – Astro Jul 28 '17 at 10:29
  • despite my column InjuryDate field is containing only the date, I think your solution is ok. Could you confirm me, please? – Astro Jul 28 '17 at 10:36
  • @Rodri you must choose best answer and accept it: https://stackoverflow.com/help/someone-answers – Ruslan K. Jul 28 '17 at 10:41
  • fixed the end and start ranges and using DATE instead of DATETIME – Dmitrij Kultasev Jul 28 '17 at 10:46
0

. Remove my answer as someone feels it's steal

PRAVIN NALIYAPARA
  • 16
  • 3