Display the designation based on their achievement in SQL Server

Question

CREATE TABLE [Changu143Aa].[UserSalesVolume]
(
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [UserID] [bigint] NULL,
    [PSV] [decimal](18, 2) NULL DEFAULT ((0)),
    [GSV] [decimal](18, 2) NULL DEFAULT ((0)),
    [DateAdded] [datetime] NULL,
    [Pin] [uniqueidentifier] NULL
)

This is the structure of my table. I need to display the designation of user depending on the condition like

• FIELD EXECUTIVE if(sum(gsv) < 100 and Sum(PSV) < 2500)

• FIELD MANAGER if(Sum(PSV) >= 100 and Sum(PSV) < 500 and Sum(GSV) >= 2500 and Sum(GSV) < 10000)

• AREA MANAGER if(Sum(PSV) >= 500 and Sum(PSV) < 2500 and sum(Gsv) >= 10000 and Sum(Gsv) <= 50000

• REGIONAL MANAGER if(sum(PSV) >= 2500 and Sum(PSV) < 5000 and Sum(GSV) >= 50000 and Sum(GSV) < 2,500,000

How can I display UserID with total PSV and total GSV and designation?

Answer 1

Use CASE statement and Group by

SELECT UserID,
       CASE
         WHEN Sum(gsv) < 100
              AND Sum(PSV) < 2500 THEN 'FIELD EXCICUTIVE'
         WHEN Sum(PSV) >= 100
              AND Sum(PSV) < 500
              AND Sum(GSV) >= 2500
              AND Sum(GSV) < 10000 THEN 'FIELD MANAGER'
         WHEN Sum(PSV) >= 500
              AND Sum(PSV) < 2500
              AND Sum(Gsv) >= 10000
              AND Sum(Gsv) <= 50000 THEN 'AREA MANAGER'
         WHEN Sum(PSV) >= 2500
              AND Sum(PSV) < 5000
              AND Sum(GSV) >= 50000
              AND Sum(GSV) < 2500000 THEN 'REGIONAL MANAGER'
         ELSE 'others'
       END      AS designation,
       Sum(PSV) AS [Total PSV],
       Sum(GSV) AS [Total GSV]
FROM   yourtable 
group by UserID 

Answer 2

This is a case with aggregation, if I understand correctly:

select UserId,
       (case when sum(gsv) < 100 and Sum(PSV) < 2500 then 'Field Executive'
             when sum(gsv) >= 100 sum(PSV) < 500 and sum(GSV) >= 2500 and sum(GSV) < 10000
             then 'Field Manager'
             when sum(PSV) >= 500 and sum(PSV) < 2500 and sum(Gsv) >= 10000 and sum(Gsv) <= 50000 
             then 'Area Manager
             when sum(PSV) >= 2500 and sum(PSV) < 5000 and sum(GSV) >= 50000 and sum(GSV) < 2500000
             then 'Regional Manager
        end) as title
from [Changu143Aa].[UserSalesVolume] usv
group by UserId;