0

I have a table called Ordhdr, i want to create a query based on the [status] column of that table. I have many statuses that i want to group into Won, Lost, Pending for easier viewing. How do i set my declare, set statement to first 'rename' the current status to WON, LOST or PENDING so i can then display my query results using the new name.

Sample Data
Order 1, STatus is QteCall1 (Pending Status)
Order 2, Status is Converted (Won Status)
Order 3, STatus is Declined (Dead Status)
Order 4, Status is Waiting (Pending Status)
Order 5, Status is NOResponse (Dead Status)'

how would I 'rename' the current status to get results that showed a count of

  • 1 Won
  • 2 Pending
  • 2 Dead
Enea Dume
  • 3,014
  • 3
  • 21
  • 36
Mary Mahoney
  • 53
  • 2
  • 10

1 Answers1

1

Is this what you need?

SELECT CASE WHEN STATUS = 'Converted' THEN 'WON' 
            WHEN STATUS IN ('QteCall1','Waiting') THEN 'PENDING'
            WHEN STATUS IN ('Declined', 'NOResponse ') THEN 'LOST' END AS NEW_NAME
            --The above case expression is being used to compare and rename the values according to the desired match
        ,COUNT(*) AS COUNT
    FROM Ordhdr
    GROUP BY 
        CASE WHEN STATUS = 'Converted' THEN 'WON' 
            WHEN STATUS IN ('QteCall1','Waiting') THEN 'PENDING'
            WHEN STATUS IN ('Declined', 'NOResponse ') THEN 'LOST' END
            -- This second case expression is the same as the previous one, but it is required by the SQL standards to be in the GROUP BY section

Result from query.

Fernando dos Santos
  • 141
  • 4