Assuming I have a table containing the following information:sq db2

      ID        NAME     Date1           Date2          code
      01        Tommy     2022-09-10     2022-09-30     45
      01        Tommy     2022-10-01     2022-12-31     44
      02        Elis      2022-07-13     2022-08-03     45
      02        Elis      2022-08-04     2022-08-31     45
      03        John      2022-08-10     2022-09-27     44
      04        John      2022-09-28     null           45

is there a way I can perform a select on the table to get the following(sql :DB2) I Mean get all people that work whole month and have 45 (code) för whole month(same month). Elisa she work Aug and has 45 for whole aug as code

      ID         NAME     Date1           Date2          code        
             
      02        Elis      2022-07-13     2022-08-03     45           
      02        Elis      2022-08-04     2022-08-31     45            
   

It is difficult for me

Thanks

CodePudding user response：

1. Create a subquery that GROUPS your data by ID.
2. HAVING MAX Date2 and compare it with the last day of the month for Date2.
3. HAVING MIN Date1 and compare it with the first day of the month for Date1.
4. WHERE code = '45'

SELECT a.ID, a.NAME, a.Date1, a.Date2, a.Code
FROM Emloyee a
INNER JOIN (SELECT ID
            FROM Emloyee 
            WHERE code = '45'
            GROUP BY ID, NAME 
            HAVING MAX(Date2) BETWEEN MAX(Date2) AND MAX(LAST_DAY(Date2))
            AND MIN(Date1) BETWEEN MIN(Date1) AND MAX(Date1 - (day(Date1) -1) days)
) b ON a.ID = b.ID
ORDER BY a.ID ASC

Result:

| ID | NAME | DATE1      | DATE2      | CODE  |
|----|------|------------|------------|-------|
| 2  | Elis | 2022-07-13 | 2022-08-03 | 45    |
| 2  | Elis | 2022-08-04 | 2022-08-31 | 45    |

Fiddle here.

CodePudding user response：

Similar tasks are resolved usually with the following approach. It's not a complete solution, but the way to start from.

WITH 
  MYTAB (ID, NAME, Date1, Date2, code) AS 
(
VALUES
  ('01', 'Tommy', DATE ('2022-09-10'), DATE ('2022-09-30'), 45)
, ('01', 'Tommy', DATE ('2022-10-01'), DATE ('2022-12-31'), 44)

--, ('02', 'Elis',  DATE ('2022-07-13'), DATE ('2022-08-03'), 45)
, ('02', 'Elis',  DATE ('2022-07-13'), DATE ('2022-07-14'), 45)
, ('02', 'Elis',  DATE ('2022-07-16'), DATE ('2022-08-03'), 45)
, ('02', 'Elis',  DATE ('2022-08-04'), DATE ('2022-08-31'), 45)

, ('03', 'John',  DATE ('2022-08-10'), DATE ('2022-09-27'), 44)
, ('04', 'John',  DATE ('2022-09-28'), CAST (null AS DATE), 45)
)
, G AS
(
-- Group number calculation with GRP
-- All records with the same (ID, NAME, GRP) have consecutive (DATE1, DATE2)
SELECT 
  T.*
, SUM
  (
    CASE 
      WHEN DATE1 = LAG (DATE2) OVER (PARTITION BY ID, NAME ORDER BY DATE2)   1 
      THEN 0
      ELSE 1
    END
  ) OVER (PARTITION BY ID, NAME ORDER BY DATE1)
  AS GRP 
FROM MYTAB T
WHERE CODE = 45
)
SELECT 
  G.*
, MIN (G.DATE1) OVER (PARTITION BY ID, NAME, GRP) AS DATE1_MIN
, MAX (G.DATE2) OVER (PARTITION BY ID, NAME, GRP) AS DATE2_MAX
FROM G

You calculate an additional "Group" column (GRP in the example) first.
All records with the same (ID, NAME, GRP) have consecutive (DATE1, DATE2) date intervals.
Finally (min, max) dates are calculated for every such a group.

The most difficult part is to construct an expression for every (min, max) interval to understand, if it covers some whole unknown month in common case (it's easy for some fixed known month, of course).