I have this table (resedtl) below...

It contains information about a customer and the resType and the status of it. I am trying to determine if a customer has completed (status = done) both types of "RES" or only one or only two.

CREATE TABLE [dbo].[resedtl](
[CustId] [int] NOT NULL,
[RESType] [nchar](10) NULL,
[note] [varchar](50) NULL,
[status] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[resedtl] ([CustId], [RESType], [note], [status]) VALUES (123, N'1         ', N'test', N'done')
GO
INSERT [dbo].[resedtl] ([CustId], [RESType], [note], [status]) VALUES (123, N'2         ', N'test2', N'done')
GO
INSERT [dbo].[resedtl] ([CustId], [RESType], [note], [status]) VALUES (124, N'1         ', N'test', N'done')
GO
INSERT [dbo].[resedtl] ([CustId], [RESType], [note], [status]) VALUES (124, N'2         ', N'tests', N'no')
GO
INSERT [dbo].[resedtl] ([CustId], [RESType], [note], [status]) VALUES (125, N'1         ', N'test', N'done')
GO
INSERT [dbo].[resedtl] ([CustId], [RESType], [note], [status]) VALUES (126, N'2         ', N'test', N'done')
GO

I want to return something like this, where if custId is done for both resType 1 and 2 then I want to return both. else just one or just two.

So far, I've the query written as follows...

  select distinct  t.CustId, case when (
 select top 1 rd.CustId from resedtl rd
 where rd.CustId = t.CustId
 and rd.CustId in (
   select rd.CustId from resedtl rd
  where rd.status ='done'
  and rd.RESType = 1
  and rd.CustId = t.CustId
 )
 and rd.CustId in (
    select rd.CustId from resedtl rd
  where rd.status ='done'
  and rd.RESType = 2
  and rd.CustId = t.CustId
 )
 ) = t.CustId then 'both'
 when

 (
  select top 1 rd.CustId from resedtl rd
 where rd.CustId = t.CustId
 and rd.CustId in (
   select rd.CustId from resedtl rd
  where rd.status ='done'
  and rd.RESType = 1
  and rd.CustId = t.CustId
 )
 and rd.CustId not in (
    select rd.CustId from resedtl rd
  where rd.status ='done'
  and rd.RESType = 2
  and rd.CustId = t.CustId
 )
 ) = t.CustId then 'just one'

 when

 (
  select top 1 rd.CustId from resedtl rd
 where rd.CustId = t.CustId
 and rd.CustId not in (
   select rd.CustId from resedtl rd
  where rd.status ='done'
  and rd.RESType = 1
  and rd.CustId = t.CustId
 )
 and rd.CustId  in (
    select rd.CustId from resedtl rd
  where rd.status ='done'
  and rd.RESType = 2
  and rd.CustId = t.CustId
 )
 ) = t.CustId then 'just two'
 else 'None'
 end as result from resedtl t
 where t.CustId in (123,124,125,126)

I'm wondering if this is the best way to do this or if there is a better solution to this..

The query I have seem to be over complicated.

CodePudding user response：

select   Custld
        ,case when min(chk) <> max(chk) then 'both' 
              when min(chk) =  1        then 'just one' 
              when min(chk) =  2        then 'just two'
                                        else 'none' end as "result"
        
from     (
          select  *
                  ,case status when 'done' then RESType end as chk
          from    t
         ) t
group by Custld