SQL Server : select specific row from groups based on multiple conditions-CodePudding

I have a table with columns ID, Status, Date.

I have to group by ID and year month of the Date to get a single row for each month and for each ID (it can have more statuses in a single month, each status having its own row) based on a few conditions:

if it has row with status 'I' and/or row with status 'R' and/or row with status 'S', it must return the row with status 'I' (only these 3 statuses should be included, there are more statuses, but they should NOT be affected - these should appear no matter what)
if it has row with status 'R' and row with status 'S', then it must return row with status 'S'.

I tried using ROW_NUMBER and a self join, but to no avail.

For example (for September 2021, remember to group by year month):

Can you, please, help?

Thank you!

CodePudding user response：

This is mostly just a matter of picking the right grouping criteria

SELECT
  t.ID,
  Month = EOMONTH(t.Date),
  Status = CASE WHEN COUNT(CASE WHEN t.Status = 'I' THEN 1 END) > 0 THEN 'I'
                WHEN COUNT(CASE WHEN t.Status = 'S' THEN 1 END) > 0 THEN 'S'
                WHEN COUNT(CASE WHEN t.Status = 'R' THEN 1 END) > 0 THEN 'R'
                ELSE MIN(Status) END
FROM YourTable t
GROUP BY
  t.ID,
  EOMONTH(t.Date),
  CASE WHEN Status NOT IN ('I','R','S') THEN Status END;

CodePudding user response：

Try this:

create table #test_group(id int, stat varchar(16), dt datetime default getdate())
insert into #test_group(id, stat) values (1, 'I')
insert into #test_group(id, stat) values (1, 'R')
insert into #test_group(id, stat) values (1, 'S')
insert into #test_group(id, stat) values (2, 'I')
insert into #test_group(id, stat) values (2, 'R')
insert into #test_group(id, stat) values (2, 'S')
insert into #test_group(id, stat) values (2, 'O')
insert into #test_group(id, stat) values (3, 'I')
insert into #test_group(id, stat) values (3, 'R')
insert into #test_group(id, stat) values (4, 'I')
insert into #test_group(id, stat) values (4, 'R')
insert into #test_group(id, stat) values (4, 'O')
insert into #test_group(id, stat) values (5, 'R')
insert into #test_group(id, stat) values (5, 'S')
insert into #test_group(id, stat) values (6, 'R')
insert into #test_group(id, stat) values (6, 'S')
insert into #test_group(id, stat) values (6, 'O')
insert into #test_group(id, stat) values (7, 'O')
insert into #test_group(id, stat) values (7, 'Z')
insert into #test_group(id, stat) values (7, 'F')
insert into #test_group(id, stat) values (8, 'I')
insert into #test_group(id, stat) values (8, 'R')
insert into #test_group(id, stat) values (8, 'S')
insert into #test_group(id, stat) values (8, 'I')
insert into #test_group(id, stat) values (8, 'R')
insert into #test_group(id, stat) values (8, 'S')
insert into #test_group(id, stat) values (8, 'R')
insert into #test_group(id, stat) values (8, 'S')
insert into #test_group(id, stat) values (8, 'S')
insert into #test_group(id, stat) values (8, 'O')
insert into #test_group(id, stat) values (8, 'Z')
insert into #test_group(id, stat) values (8, 'F')
insert into #test_group(id, stat) values (9, 'R')
insert into #test_group(id, stat) values (9, 'O')
insert into #test_group(id, stat) values (9, 'Z')
insert into #test_group(id, stat) values (9, 'F')
insert into #test_group(id, stat) values (9, 'S')
insert into #test_group(id, stat) values (10, 'R')
insert into #test_group(id, stat) values (10, 'O')
select * from #test_group
select id, DATEPART(year,dt)dtY,datepart(month, dt)dtM, 
case when min(stat) = 'I' then 'I' else case when max(stat) = 'S' then 'S' else 'R' end end stat
from #test_group where stat in ('I','R','S')group by id, DATEPART(year,dt),datepart(month, dt)
union all 
select id, DATEPART(year,dt)dtY,datepart(month, dt)dtM, stat
from #test_group where stat not in ('I','R','S')group by id, DATEPART(year,dt),datepart(month, dt), stat
order by dty,dtm,id,stat
drop table #test_group