Count of Employee only when first_stamp is between a window of start

I want a count of emp_id only when firststamp date is in a 21 day window depending on startdt.

However, I want to count that empid, only when the firststamp either 7 days before the startdt, during the week of startdt, or the next week of startdt. Startdt is always going to be a sunday, so essentially I am looking for a three week period, 1 week before the startdt, during the week of startdt or one week after.

For example, in this table, employee 123 will be given a count since his firststamp is 10/21(during the week of startdt) for this emp any date between and including 10/09 and 10/29 will be acceptable.

But emp 345 does not get a credit because his firststamp is way after the 21 day window. It would be acceptable if it was between 10/16 to 11/04

Can someone help me figure out the code logic in SQL? enter image description here

please let me know if you have any questions

CodePudding user response：

I have a MySQL answer for you.

If you are using MySQL ( I am saying this because I dont know what 'DATE' data requirements and the clause that we can use to query them in other RDBMS.),first of all, the data that you stored in the 'DATE' column should not be arbitrary, it should be something like YYYY-MM-DD. Once you insert your date data as 'YYYY-MM-DD', MySQL provides some clause that we can use.

The following is the code that you need:

SELECT COUNT(emp_id) AS emp_id_count
FROM t1
WHERE first_stamp BETWEEN DATE_SUB(start_dt, INTERVAL 7 DAY) AND DATE_ADD(start_dt, INTERVAL 13 DAY);

I have tested it, it works. The only prerequest is that you have to change your date data to YYYY-MM-DD. And also, you can change t1 to your actual table name.