SQL Server - 在不包括年份的日期范围之间查找人员

I have a table EMPLOYEE containing Employee info as mentioned below:

我有一个包含员工信息的表EMPLOYEE,如下所述:

ID     NAME     DOB
1      ABC      1974-01-01
2      BDS      1984-12-31
3      QWE      1959-05-27

and so on

I want to list all the employees whose DOB is in the given range.

我想列出DOB在给定范围内的所有员工。

select * from EMPLOYEE where DOB BETWEEN '1970-01-01' AND '1980-02-27'

I have a filter condition to 'include year in date comparison', which when selected 'NO', the employee DOB day and DOB month only should be considered for comparison. and not the year.

我有一个过滤条件'包括年度比较',当选择'否'时,员工DOB日和DOB月只应考虑进行比较。而不是一年。

For example: If I enter the date range as '1970-01-01' and '1980-02-27' and the filter is selected as 'NO' then it should search for only those employees whose DOB is greater than equal to JAN-01 and less than equal to FEB-27.

例如:如果我输入日期范围为'1970-01-01'和'1980-02-27'并且过滤器被选为'NO',那么它应该仅搜索那些DOB大于等于JAN的员工-01且小于等于FEB-27。

When selected 'Yes', it is simply date range as mentioned in above query.

选择“是”时,它只是上面查询中提到的日期范围。

Here is what I have tried so far:

这是我到目前为止所尝试的:

select * from EMPLOYEE where DOB BETWEEN '1970-01-01' AND '1980-02-27'
AND MONTH(DOB) >= CASE WHEN 'NO'='NO' THEN MONTH('1970-01-01')
ELSE MONTH(DOB) END
AND MONTH(DOB) <= CASE WHEN 'NO'='NO' THEN MONTH('1980-02-27')
ELSE MONTH(DOB) END
AND DAY(DOB) >= CASE WHEN 'NO'='NO' THEN DAY('1970-01-01')
ELSE DAY(DOB) END
AND DAY(DOB) <= CASE WHEN 'NO'='NO' THEN DAY('1980-02-27')
ELSE DAY(DOB) END

It works when I pass the date range where the FROM date has smaller number month than the TO date month.

当我通过日期范围时,它的工作日期范围,其中FROM日期的月份数小于TO日期月份。

For example: It doesnt work when I pass the date range as '1970-12-01' to '1980-01-31'. It should list the employees whose DOB is in DEC and JAN month.

例如:当我将日期范围作为'1970-12-01'传递给'1980-01-31'时,它不起作用。它应列出DOB在DEC和JAN月份的员工。

Need help please.

需要帮助。

7 个解决方案

#1

Sample Data;

DECLARE @Date_From date; SET @Date_From = '1970-12-01'
DECLARE @Date_To date; SET @Date_To = '1974-01-31'
DECLARE @IncludeYear bit; SET @IncludeYear = 0

CREATE TABLE #Employee (ID int, Name varchar(10), DOB date)
INSERT INTO #Employee (ID, Name, DOB)
VALUES
(1,'ABC','1974-01-01')
,(2,'BDS','1984-12-31')
,(3,'QWE','1959-05-27')

This is the query I've made. Tried to cover for every eventuality.

这是我的查询。试图弥补每一个可能性。

SELECT
e.ID
,e.Name
,e.DOB
FROM #Employee e
WHERE
    (
    @IncludeYear = 1
    AND
    DOB BETWEEN @Date_From AND @Date_To
    )
OR
(
@IncludeYear = 0
AND
(
    (
    DATEPART(DAYOFYEAR, @Date_From) = DATEPART(DAYOFYEAR, @Date_To)
    AND 
    DATEPART(DAYOFYEAR, DOB) = DATEPART(DAYOFYEAR, @Date_To)
    )
OR
    (
    DATEPART(DAYOFYEAR, @Date_From) < DATEPART(DAYOFYEAR, @Date_To)
    AND
    DATEPART(DAYOFYEAR, DOB) BETWEEN DATEPART(DAYOFYEAR, @Date_From) AND DATEPART(DAYOFYEAR, @Date_To)
    )
OR
    (
    DATEPART(DAYOFYEAR, @Date_From) > DATEPART(DAYOFYEAR, @Date_To)
    AND
        (
        DATEPART(DAYOFYEAR, DOB) > DATEPART(DAYOFYEAR, @Date_From)
        OR
        DATEPART(DAYOFYEAR, DOB) < DATEPART(DAYOFYEAR, @Date_To)
        )
    )
)
)

• First part of the where clause checks if the @date_from and @date_to are the same date, then only returns these.

where子句的第一部分检查@date_from和@date_to是否是相同的日期,然后只返回这些。

• Second part checks if the day of year for @date_from comes before @date_to. If it does then return everything between these days of the year.

第二部分检查@date_from的日期是否在@date_to之前。如果确实如此,则在一年中的这几天之间返回所有内容。

• Final part checks if the day of year for @date_to comes before @date_from then it gets everything with the day of year after @date_from or before @date_to

最后一部分检查@date_to的日期是否在@date_from之前,然后在@date_from之后或@date_to之前获得一年中的所有日期

The results for this one come out as this;

这个的结果就是这样的;

ID  Name    DOB
1   ABC     1974-01-01
2   BDS     1984-12-31

#2

DECLARE @includeYear bit = 0, -- if 0 - we don't include year, 1 - include 
        @dateFrom date ='1970-12-01',
        @dateTo date ='1980-05-30'

IF @includeYear = 1 
BEGIN
    SELECT e.*
    FROM EMPLOYEE e
    INNER JOIN (SELECT @dateFrom as dF, @dateTo as dT) d
        ON e.DOB BETWEEN dF AND dT
END
ELSE 
BEGIN
    SELECT e.*
    FROM EMPLOYEE e
    INNER JOIN (SELECT @dateFrom as dF, @dateTo as dT) d
        ON e.DOB BETWEEN 
        (CASE WHEN MONTH(dF) > MONTH(dT) 
                THEN  DATEADD(year,YEAR(e.DOB)-YEAR(d.dF)-1,dF)
                ELSE  DATEADD(year,YEAR(e.DOB)-YEAR(d.dF),dF) END)
                AND DATEADD(year,YEAR(e.DOB)-YEAR(d.dT),dT)
        OR e.DOB BETWEEN DATEADD(year,YEAR(e.DOB)-YEAR(d.dF),dF) AND
        (CASE WHEN MONTH(dF) > MONTH(dT) 
                THEN  DATEADD(year,YEAR(e.DOB)-YEAR(d.dT)+1,dT)
                ELSE  DATEADD(year,YEAR(e.DOB)-YEAR(d.dT),dT) END)
END

For

dateFrom    dateTo
1970-12-01  1980-01-30

Output:

ID  NAME    DOB
1   ABC     1974-01-01
2   BDS     1984-12-31

For

dateFrom    dateTo
1970-05-01  1980-06-30

Output:

ID  NAME    DOB
3   QWE     1959-05-27

For

dateFrom    dateTo
1970-05-01  1980-05-30

Output:

ID  NAME    DOB
3   QWE     1959-05-27

etc

#3

Try the function DATEPART(dayofyear, date)
In case the day-of-year of the first date is smaller than the day-of-year of the second date, then the day-of-year of the DOB should be between the specified days-of-year.
Otherwise, the day-of-year of the DOB should be either smaller than the day-of-year of the second date or greater than the day-of-year of the first date.

尝试DATEPART函数(dayofyear,date)如果第一个日期的年份小于第二个日期的日期,那么DOB的年份应该在指定的年份之间天的一年。否则,DOB的年度应小于第二个日期的日期或大于第一个日期的日期。

I hope I expressed myself well.

我希望我表达得很好。

#4

Rather than working case-by-case and disassembling and reassembling parts of dates, I've tried to make life easier:

我试图让生活更轻松,而不是逐案工作,拆解和重新组装部分日期:

declare @t table (ID int not null, Name varchar(17) not null, DOB date not null)
insert into @t(ID,NAME,DOB) values
(1,'ABC','19740101'),
(2,'BDS','19841231'),
(3,'QWE','19590527')

declare @Start date
declare @End date
declare @IncludeYear bit

select @Start='19701201',@End='19800131',@IncludeYear=0

;With Normalized as (
    select
        ID,
        Name,
        CASE WHEN @IncludeYear=1 THEN DOB
            ELSE DATEADD(year,DATEDIFF(year,DOB,'20000101'),DOB)
        END as DOB,
        CASE WHEN @IncludeYear=1 THEN @Start
            ELSE DATEADD(year,DATEDIFF(year,@Start,'20000101'),@Start)
        END as StartRange,
        CASE WHEN @IncludeYear=1 THEN @End
            ELSE DATEADD(year,DATEDIFF(year,@End,'20000101'),@End)
        END as EndRange
    from
        @t
)
select * from Normalized
where
    DOB between StartRange and EndRange or
    (
        @IncludeYear=0 and StartRange>EndRange and
        (
            DOB < EndRange or DOB > StartRange
        )
    )

We create the Normalized CTE that, does nothing if @IncludeYear is 1 or, if it is zero, it resets all dates so that they occur in 2000 (arbitrarily selected).

我们创建标准化CTE,如果@IncludeYear为1则不执行任何操作,或者如果它为零,则重置所有日期,以便它们出现在2000(任意选择)。

We then do the straightforward query based on the CTE. The one circumstance where it won't correctly match is when you have your range defined over a year-end transition and we don't care about years - which we can check specifically for and cater for within the end of the WHERE clause.

然后,我们根据CTE进行简单的查询。它不能正确匹配的一种情况是,您在年终转换中定义了您的范围,而我们不关心年份 - 我们可以在WHERE子句的末尾专门检查和满足。

Results:

ID          Name              DOB        StartRange EndRange
----------- ----------------- ---------- ---------- ----------
1           ABC               2000-01-01 2000-12-01 2000-01-31
2           BDS               2000-12-31 2000-12-01 2000-01-31

Results with @Start='19700101',@End='19800227',@IncludeYear=1:

结果@ Start ='19700101',@ End ='19800227',@ IncludeYear = 1:

ID          Name              DOB        StartRange EndRange
----------- ----------------- ---------- ---------- ----------
1           ABC               1974-01-01 1970-01-01 1980-02-27

#5

Here is another solution

这是另一种解决方案

DECLARE @employee table(EmployeeID varchar(10), DOB date);

INSERT INTO @employee(EmployeeID, DOB)
    VALUES('0001', '01-Dec-1990'),
        ('0002', '06-Jan-1993'),
        ('0003', '04-Mar-1987'),
        ('0004', '12-Feb-1996');


DECLARE @dateStart date = '01-Jan-1990';
DECLARE @dateEnd date = '27-Feb-1997';
DECLARE @includeYear bit = 0;

If @includeYear = 0
Begin
    SET @dateStart = CAST(('2000-' + CAST(MONTH(@dateStart) AS varchar(10)) + '-' + CAST(DAY(@dateStart) as varchar(10))) AS date);
    SET @dateEnd = CAST(('2000-' + CAST(MONTH(@dateEnd) AS varchar(10)) + '-' + CAST(DAY(@dateEnd) as varchar(10))) AS date);
End

If @includeYear = 1
Begin
    SELECT  *
    FROM    @employee
    WHERE   DOB BETWEEN @dateStart AND @dateEnd
End
Else
Begin
    SELECT  *
    FROM    @employee
    WHERE   CAST(('2000-' + CAST(MONTH(DOB) AS varchar(10)) + '-' + CAST(DAY(DOB) as varchar(10))) AS date) BETWEEN @dateStart AND @dateEnd
End

As you can see we are just making the year part of the query a constant if you don't want to include year. This query seems to be a bit slower but if you add another computed column in your table, where you save the date with a constant year then you just need to put where criteria on that particular column.

正如您所看到的,如果您不想包含年份,我们只是将查询的年份作为常量。这个查询似乎有点慢但是如果你在表中添加另一个计算列,你用一个常数年保存日期,那么你只需要在那个特定列上放置标准。

#6

Try this.

declare @flag varchar(3) ='NO';
declare @sd date ='1980-02-27';
declare @ed date ='1970-01-01';

select tt.*
from (select sd = month(@sd)*100 + day(@sd),
      ed = month(@ed)*100 + day(@ed)
     ) prm      
cross join
    -- test data, place real table here
    ( 
        values 
        (1,'ABC', cast('1974-01-05' as date)),
        (2,'BDS','1984-12-31'),
        (3,'QWE','1959-05-27')
    ) tt(ID,NAME, DOB)
cross apply (select md = month(DOB)*100 + day(DOB)) tx  
where @flag ='YES' and DOB between @sd and @ed
    or @flag ='NO' and (prm.sd<=prm.ed and tx.md  between prm.sd and prm.ed
                        or prm.sd>prm.ed and (tx.md <= prm.ed or tx.md >= prm.sd));

#7

• Always use a SARG in your predicate. Any answer that fails to do this only results in lost performance and your DBA getting upset.

始终在谓词中使用SARG。任何未能做到这一点的答案只会导致性能下降和DBA感到不安。

What you want is two different queries to run depending on the answer to the Procedure. Since this is a proc that likely runs a lot, store the answers into a variable in your PROC and run any adjusting code from there. Not only will this make your code more robust by flushing out errors beforehand, but SQL Server has a better chance of guessing the variables to use with your indexes.

您想要的是根据过程的答案运行两个不同的查询。由于这是一个可能会运行很多的过程,因此将答案存储到PROC中的变量中并从那里运行任何调整代码。通过预先清除错误,这不仅可以使您的代码更加健壮,而且SQL Server更有可能猜测要与索引一起使用的变量。

The following PROC will work. Feel free to use part or all of it:

以下PROC将起作用。随意使用部分或全部:

CREATE TABLE #table_E (ID INT, Name VARCHAR(3), DOB DATE)
INSERT INTO #table_E (ID , Name, DOB)
VALUES (1, 'ABC', '1997-01-02' )
     , (2, 'BDS', '1984-12-31' )
     , (3, 'QWE', '1993-03-22' )

GO

CREATE PROC USP_EmpCompare (@Date_1 DATE, @Date_2 DATE, @Compare_Year VARCHAR(3))
AS
BEGIN
    DECLARE @MONTH_1 INT
          , @Month_2 INT
          , @Day_1   INT
          , @Day_2   INT
          , @Date1  DATE
          , @Date2  DATE
    SET @Date1  = CASE WHEN @Date_1 > @Date_2 THEN @Date_2 ELSE @Date_1 END
    SET @Date2  = CASE WHEN @Date_1 > @Date_2 THEN @Date_1 ELSE @Date_2 END
    SET @Month_1 = CASE WHEN DATEPART(MM, @Date2) > DATEPART(MM, @Date1) THEN DATEPART(MM, @Date1) ELSE DATEPART(MM, @Date2) END
    SET @Month_2 = CASE WHEN DATEPART(MM, @Date1) > DATEPART(MM, @Date2) THEN DATEPART(MM, @Date1) ELSE DATEPART(MM, @Date2) END
    SET @Day_1   = CASE WHEN DATEPART(DD, @Date2) > DATEPART(DD, @Date1) THEN DATEPART(DD, @Date1) ELSE DATEPART(DD, @Date2) END
    SET @Day_2   = CASE WHEN DATEPART(DD, @Date1) > DATEPART(DD, @Date2) THEN DATEPART(DD, @Date1) ELSE DATEPART(DD, @Date2) END
    -- SELECT @Date1, @Date2
    IF @Compare_Year = 'no'
    BEGIN
    ;WITH C AS (SELECT ID
                    , Name
                    , DATEPART(DD, DOB) AS Day
                    , DATEPART(MM, DOB) AS Month
    FROM #table_E)
    SELECT  ID, Name, @Date1, @Date2
    FROM    C
    WHERE   C.Month >= @MONTH_1
        AND C.Month <= @Month_2
        AND C.Day   >= @Day_1
        AND C.DAy   <= @Day_2       
    END
    IF @Compare_Year = 'yes'
    BEGIN

        SELECT ID, Name, DOB
        FROM #table_E
        WHERE DOB <= @Date2
          AND DOB >= @Date1
    END
    ELSE 
        PRINT WHAT! FOLLOW THE RULES YOU FOOL!!!

END 

jk. that last part about fools is probably not included in your final draft. ;)

JK。关于傻瓜的最后一部分可能不包括在你的最终草案中。 ;)