CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Mariyan
Mariyan

Reputation: 125

Retrieve different customer status

I want to write a query to retrieve the result from the table.

When the name all my statuses are completed to show me completed. When the status has completed but there is another status such as in progress or created then show only the other status

CustomName STATUS order
Ivan Ivanov completed 1
Stoqn Stoqnov completed 1
Dimityr Ivanov completed 1
Ivan Ivanov completed 2
Dimityr Ivanov completed 2
Ivan Ivanov inprocess 2
Stoqn Stoqnov completed 2
Dimityr Ivanov completed 3
Dimityr Ivanov created 4
Stoqn Stoqnov completed 3
Ivan Ivanov completed 4
Stoqn Stoqnov completed 4

Expected result
Ivan Ivanov inprocess
Dimityr Ivanov created
Stoqn Stoqnov completed

Query:

SELECT distinct  CustomName,
(CASE WHEN  [STATUS] ='COMPLETED'    THEN 'completed' 
WHEN  [STATUS] ='inprocess' THEN 'inprocess' 
WHEN  [STATUS] ='created' THEN 'created'  END  ) AS [STATUS]
from [dbo].[Customers]

Upvotes: 0

Views: 231

Answers (3)

Charlieface
Charlieface

Reputation: 72153

I don't think this needs window functions or cross joins, just a simple GROUP BY with conditional counts

SELECT
  CustomName,
  CASE WHEN COUNT(CASE WHEN [status] = 'created'   THEN 1 END) > 0 THEN 'Created'
       WHEN COUNT(CASE WHEN [status] = 'inprocess' THEN 1 END) > 0 THEN 'In Process'
       ELSE 'Completed' END
FROM YourTable t
GROUP BY t.CustomName;

Upvotes: 2

GGG
GGG

Reputation: 486

Try the following:

The idea is to use cte to find all Customer Name who contain different type of status

and then use UNION ALL to find all Customer Name who ONLY contains "Completed" status

;WITH cte AS (
    SELECT DISTINCT CustomName, [STATUS]
    FROM [dbo].[Customers]
    WHERE [STATUS] <> 'Completed'
)
SELECT *
FROM cte 
UNION ALL
SELECT DISTINCT CustomName, [STATUS]
FROM [dbo].[Customers]
WHERE CustomName NOT IN (SELECT CustomName FROM cte)

Upvotes: 0

Dale K
Dale K

Reputation: 27333

You can do something like the following where you count how many of each status there are per customer and then choose in a priority order which to display.

declare @Test table (CustomName varchar(32), [STATUS] varchar(32), [Order] int)

insert into @Test (CustomName, [STATUS], [Order])
values
('Ivan Ivanov',     'completed',   1),
('Stoqn Stoqnov',   'completed',   1),
('Dimityr Ivanov',  'completed',   1),
('Ivan Ivanov',     'completed',   2),
('Dimityr Ivanov',  'completed',   2),
('Ivan Ivanov',     'inprocess',   2),
('Stoqn Stoqnov',   'completed',   2),
('Dimityr Ivanov',  'completed',   3),
('Dimityr Ivanov',  'created',     4),
('Stoqn Stoqnov',   'completed',   3),
('Ivan Ivanov',     'completed',   4),
('Stoqn Stoqnov',   'completed',   4);

with cte as (
    select CustomName
      , sum(case when [status] = 'completed' then 1 else 0 end) over (partition by CustomName) Completed
      , sum(case when [status] = 'created' then 1 else 0 end) over (partition by CustomName) Created
      , sum(case when [status] = 'inprocess' then 1 else 0 end) over (partition by CustomName) InProcess
    from @Test
)
select CustomName
    -- This logic could be more complex if desired
    , case when InProcess > 0 then 'In Process' when Created > 0 then 'Created' else 'Completed' end
from cte
group by CustomName, Completed, Created, InProcess;

Returns:

CustomName Status
Dimityr Ivanov Created
Ivan Ivanov In Process
Stoqn Stoqnov Completed

Note: Providing the DDL+DML as I have shown, makes it much easier for people to assist.

Upvotes: 2

Related Questions