CODEWITHSUNDEEP
sqldb2
Tick _Tack
Tick _Tack

Reputation: 33

USE COUNT WITH CONDITION IN THE SAME TABLE

SELECT DISTINCT 
    P.NAME,  L.ID, L.FROM, L.END, L.SALARY, L.NOTE, 
    CASE
        WHEN (MAX(A.END) IS NULL OR MAX(A.END) >= current date ) 
            THEN 'JES' 
            ELSE 'NO' 
    END AS "Have One "
   
FROM 
    SALARY L 
INNER JOIN 
    CONTACT A ON A.ID = L.ID
INNER JOIN 
    Pep P ON P.ID = L.ID 
WHERE
    L.SALARY = '8000' 
    AND L.END >= CURRENT DATE 
GROUP BY  
    P.NAME, L.ID, L.FROM, L.END, L.SALARY, L.NOTE

what i want to add new column that contain result : how many active record by person . i consider record is active when the following condition is ok

L.END >= FIRST_DAY(CURRENT DATE) AND L.FROM <= LAST_DAY(CURRENT DATE)

example på result:

|  name  |  ID | FROM      |   END    | SALARY | NOTE | HAVE ONE | ACTIVE
|  KLAR  | 678 |2021-01-01 |2021-09-31|   8000 | GOD  |  YES     |   1
|  KLAR  | 678 |2021-10-01 |2021-12-31|   8000 | GOD  |  YES     |   1
|  TINA  | 676 |2021-01-01 |2021-09-31|   8000 | GOD  |  YES     |   2
|  TINA  | 676 |2021-06-01 |2021-12-31|   8000 | GOD  |  YES     |   2

How can I do that? enter image description here

Upvotes: 0

Views: 134

Answers (1)

Stu
Stu

Reputation: 32579

It looks like you can just use conditional aggregation with your existing logic, implement your new column like so:

...,
SUM(
  SUM(
    CASE WHEN L.END >= FIRST_DAY(CURRENT DATE)
          AND L.FROM <= LAST_DAY(CURRENT DATE) 
      THEN 1 ELSE 0 END
  )
) OVER (PARTITION BY ID) AS ACTIVE

Upvotes: 4

Related Questions