If date is within a defined range show value as 1 else show value as 0

Transact-SQL
1

Greetings,

How do I ensure that the following code only queries records that are submitted between March and May 2024?

If a record has been submitted between the above date range, display 1. Otherwise, display 0.

 SELECT e.Name, e.email, e.emptitle, d.dateCreated,
     CASE WHEN YEAR(d.dateSubmitted) < YEAR(getdate()) THEN 1 ELSE 0 END as previousYear,
     --CASE WHEN YEAR(d.dateSubmitted) = YEAR(getdate()) THEN 1 ELSE 0 END as thisYear
 CASE WHEN d.dateSubmitted BETWEEN DATEFROMPARTS(2024, 3, 1) AND DATEFROMPARTS(2024, 5 + 1, 1) THEN 1 ELSE 0 END as thisYear
FROM Employees e
INNER JOIN dateDetails d on e.employeeID = d.employeeID
WHERE e.employeeID = someID

The line of code I commented out works perfectly However, since we are directed to perform this check just one time between March and May 2024, we replaced the code we commented out with the line of code below it.

We are having problems getting consistent results

Any ideas?

I even tried the line below"

I have also tried this:
CASE WHEN d.dateSubmitted >= DATEFROMPARTS(2024, 3, 1) AND d.dateSubmitted <  DATEFROMPARTS(2024,5 + 1, 1)
2

That should work. assuming that d.dateSubmitted is a date/datetime[2] data type.

Although it makes more sense to just put actual dates in there if the values are fixed:

CASE WHEN d.dateSubmitted >= '20240301' AND d.dateSubmitted < '20240601' THEN 1 ELSE 0 END AS thisYear

3

Thank you for the prompt response Scott.

dateSubmitted is of datetime data type.

4

Using BETWEEN is inclusive - which means you are including all d.dateSubmitted that are equal to '20240601 00:00:00.000'.

@ScottPletcher is using >= for the start (greater than or equal to '20240301 00:00:00.000' and < for the end (less than '20240601 00:00:00.000').

If you are getting inconsistent results with the above - then something else is going on and we would need to see examples of where it is failing.

5

@ScottPletcher's code worked great but I am wondering if I can still do this< replace this:
'
CASE WHEN YEAR(d.dateSubmitted) < YEAR(getdate()) THEN 1 ELSE 0 END as previousYear,

with this below and still get some result?

CASE WHEN d.dateSubmitted < '20240301' THEN 1 ELSE 0 END as previousYear,

In other words, since we are checking data entered from previous year up to March 1, 2024.

If dateSubmitted < from 03/01/2024 to prior year then previousYear = 1, otherwise, previousYear =1.

6

hi

hope this helps

create sample data script

declare @Employees table ( employeeID int , name varchar(100) , email varchar(100) , emptitle varchar(20) )
declare @dateDetails table ( employeeID int , dateCreated date ,dateSubmitted date )

insert into @Employees Values ( 1, 'Sam' , 'sam@gmail.com' , 'Manager' )
insert into @Employees Values ( 2, 'Roxy', 'Quintal@gmail.com' , 'Software Engineer' )
insert into @Employees Values ( 3, 'Tim' , 'dubai@gmail.com' , 'Data Engineer' )

insert into @dateDetails Values ( 1,'2020-02-07','2024-03-31' )
insert into @dateDetails Values ( 2,'2022-10-15','2024-04-12' )
insert into @dateDetails Values ( 3,'2021-05-02','2024-05-10' )

SELECT 
    e.Name
  , e.email
  , e.emptitle
  , d.dateCreated
  , CASE WHEN cast(CONVERT(NCHAR(8), d.dateSubmitted,112) as int )  < 20240301  THEN 1 ELSE 0 END as previousYear
  , CASE WHEN 
           cast(CONVERT(NCHAR(8), d.dateSubmitted,112) as int ) >= 20240301 
             AND 
           cast(CONVERT(NCHAR(8), d.dateSubmitted,112) as int ) <  20240501  
	     THEN 1 ELSE 0 END as thisYear
FROM 
   @Employees e
       INNER JOIN 
  @dateDetails d 
      on e.employeeID = d.employeeID

image
image1108×494 18.7 KB

7

Everything is working perfectly now

Thanks to all of you.

Do I have to choose only one solution?

8

hi

Your choice of solution

Would depend on a variety of factors

Example:

  Performance 
, Indexes
, Impact
, Maintenance 
, Scalability 
, Re Factoring 
, Future Scenarios 
, Deprecation 
, Disk Storage 
, Other Dependant Resources 
, Your Work Environment Situation 
Etc
Etc