the grouping of the rows on the basis of specific conditions in sql server

Based on your data (where the criteria are 1, 2 and NULL for n/a), a simple ‘group by’ the employee, and MAX of the columns, should work e.g.,

    MAX(yt.A_Shift_Status) AS A_Shift_Status, 
    MAX(yt.B_Shift_Status) AS B_Shift_Status, 
    MAX(yt.C_Shift_Status) AS C_Shift_Status, 
    MAX(yt.D_Shift_Status) AS D_Shift_Status
    yourtable yt

For the shift statuses

  • If any of them are 2, it returns 2
  • otherwise if any of them are 1, it returns 1
  • otherwise it returns NULL

Notes re 1/2/3 (which was specified as criteria) vs 1/2/NULL (which is in the data)

  • It gets a little tricker if the inputs are supposed to use 1/2/3 instead of 1/2/NULL. Let us know if you are changing the inputs to reflect that.
  • If the input is fine as NULLs, but you need the output to have ‘3’ for n/a (nulls), you can put an ISNULL or COALESCE around the MAX statements e.g., ISNULL(MAX(yt.A_Shift_Status), 3) AS A_Shift_Status

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top