If Column from Group of Rows Contains 'x' Return 'x' for All Related Rows in New-CodePudding

I have the table below and I want to create a new column which looks at the [Registration Date] and the [Person ID] and checks all the values in [Registration Status], if the [Person ID] has 1 or more ‘Present’ or 1 or more ‘Not Registered’ within a day then they are present for the whole day, otherwise they are absent.

As an example (Highlighted red in image below), look at the 7th Feb 2022 where Person ID = 103. He has 4 rows of ‘Not Registered’ resulting in 'Present' for the day:

The granularity of the rows needs to stay the same so Group By wouldn’t work in this instance.

Thank you in advance.

Register ID Person ID Registration Date Registration Time Registration Status Desired Output 1 100 07-Feb-22 07:30:00 Present Present 2 100 07-Feb-22 08:00:00 Not Registered Present 2 100 07-Feb-22 08:55:00 Absent Present 2 100 07-Feb-22 10:10:00 Present Present 3 101 07-Feb-22 07:30:00 Not Registered Present 3 101 07-Feb-22 07:40:00 Absent Present 3 101 07-Feb-22 08:25:00 Absent Present 3 101 07-Feb-22 09:10:00 Present Present 5 102 07-Feb-22 07:30:00 Absent Absent 5 102 07-Feb-22 12:00:00 Absent Absent 2 103 07-Feb-22 07:30:00 Not Registered Present 2 103 07-Feb-22 07:40:00 Not Registered Present 2 103 07-Feb-22 08:25:00 Not Registered Present 2 103 07-Feb-22 09:10:00 Not Registered Present 6 100 08-Feb-22 07:30:00 Absent Absent 7 100 08-Feb-22 08:00:00 Absent Absent 8 101 09-Feb-22 07:30:00 Absent Absent 9 101 09-Feb-22 07:40:00 Absent Absent 10 101 09-Feb-22 08:25:00 Absent Absent 11 101 09-Feb-22 09:10:00 Absent Absent

CodePudding user response：

select *, case when count(case when Registration_Status= 'Present' then 1 end) OVER (PARTITION BY person_id, cast(Registration_Date as date)) > 1
                 or count(case when Registration_Status= 'Not Registered' then 1 end) OVER (PARTITION BY person_id, cast(Registration_Date as date)) > 1
               then 'Present'
               else 'Absent'
          end
from table

CodePudding user response：

You can use CASE expression for that.

For your example:

SELECT [Register ID],[Person ID],[Registration Date],[Registration Time],[Registration Status]
CASE
    WHEN COUNT(CASE WHEN [Registration Status]= 'Present' THEN 1 END) OVER (PARTITION BY [Person ID], [Registration Date]) > 0 THEN 'Present'
    WHEN COUNT(CASE WHEN [Registration Status]= 'Not Registered' THEN 1 END) OVER (PARTITION BY [Person ID], [Registration Date]) > 0 THEN 'Present'
    ELSE 'Absent'
END AS [Desired Output]
FROM YourTable;

should work.

CodePudding user response：

Use 2 queries instead of one.

    ;WITH NEWTABLE AS
    (
      SELECT DISTINCT PersonId, RegistrationDate FROM TABLE WHERE [Registration 
      Status] IN ('Present', 'Not Registered')
    )
    UPDATE a SET [Desired Output] = 'Present' FROM TABLE tbl 
INNER JOIN NEWTABLE ntbl ON tbl.PersonId = ntbl.PersonId 
AND tbl.RegistrationDate = ntbl.RegistrationDate; 
    
    WITH ABSENTTABLE AS
    (
      SELECT DISTINCT PersonId, RegistrationDate FROM TABLE WHERE [Registration 
      Status] IN ('Absent')
    )
    UPDATE a SET [Desired Output] = 'Absent' FROM TABLE tbl 
INNER JOIN ABSENTTABLE atbl ON tbl.PersonId = atbl.PersonId 
AND atbl.RegistrationDate = atbl.RegistrationDate;

CodePudding user response：

The final answer was a combination of Erin and Domni0's solutions.

SELECT [registration date],
   [person id],
   CASE
     WHEN Count(CASE
                  WHEN [registrationstatus] = 'Present'
                        OR [registrationstatus] = 'Not Registered' THEN 1
                END)
            OVER (
              partition BY [person id], [registration date]) > 0 THEN
     'Present'
     ELSE 'Absent'
   END AS [Desired Outcome]   FROM Table

Thank you both for your help.