Problem to get data with select

Transact-SQL
1

hi there ,
i have a tabel from which i have data foe employys form 2015 up today. each employy may have more than one row based on a date and all have active_card fild true or false.

so i want to collect all the rows once that the have active_card false and on the curent period are not active.

any ideas?
kindly

2

Sure! To collect all the rows for employees with active_card set to false and who are currently not active, you can use a SQL query with the appropriate filtering conditions. Assuming your table is named "EmployeeData" and the relevant columns are named "employee_id", "active_card", and "date", here's a sample SQL query to achieve this:

SELECT *
FROM EmployeeData
WHERE active_card = 0 -- active_card is false
  AND date <= GETDATE() -- on or before the current date
  AND employee_id NOT IN (
    SELECT employee_id
    FROM EmployeeData
    WHERE active_card = 1 -- active_card is true
      AND date <= GETDATE() -- on or before the current date
  );

Let me explain the query step by step:

  1. SELECT *: This selects all columns from the "EmployeeData" table for the rows that meet the specified conditions.
  2. WHERE active_card = 0: This filters rows where the "active_card" column is false (0).
  3. AND date <= GETDATE(): This ensures that the rows selected have a date on or before the current date. This is to consider only the data up to the current period.
  4. AND employee_id NOT IN (...): This subquery filters out employees who have an active card (active_card = 1) on or before the current date. By using NOT IN, we exclude these employees from the final result.

This way, you will get all the rows for employees with active_card set to false and who are currently not active based on the specified conditions.