Display designation based on their achievements 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 user designation depending on type state

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

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

• REGION MANAGER if (amount (PSV)> = 500 and amount (PSV) <2500 and amount (Gsv)> = 10000 and amount (Gsv) <= 50000

• REGIONAL MANAGER if (amount (PSV)> = 2500 and amount (PSV) <5000 and amount (GSV)> = 50,000 and amount (GSV) <2,500,000

How can I display UserIDwith full PSV value and general GSV and designation?

Answer 1

CASE 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

case , :

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;

Answer 3

Try the following:

SELECT  USERID,
        TotalPSV,
        TotalGSV,
        CASE 
           WHEN TotalGSV < 100 AND TotalPSV < 2500 THEN 'FIELD EXCICUTIVE'
           WHEN TotalPSV >= 100 AND TotalPSV < 500 AND TotalGSV >=2500 AND TotalGSV <10000 THEN 'FIELD MANAGER' 
           WHEN TotalPSV >=500 and TotalPSV  <2500 AND TotalGSV >=10000 AND TotalGSV  <=50000 THEN 'AREA MANAGER' 
           WHEN TotalPSV  >=2500 AND TotalPSV <5000 AND TotalGSV >=50000 AND TotalGSV  <2,500,000 THEN 'REGIONAL MANAGER' 
        END AS Designation
FROM(
      SELECT USERID,SUM(PSV) TotalPSV,SUM(GSV) TotalGSV
      FROM TableName
      GROUP BY USERID
    ) 

Answer 4

Use the sql-case function.

In your case, it should look something like this (just a couple of lines):

SELECT UserID, sum(PSV) as sPSV, sum(GSV) as sGSV,(
    CASE 
        WHEN sGSV < '100' && sPSV < '2500' THEN FIELD EXCICUTIVE
        WHEN sPSV >= '100' && sPSV < '500' && sGSV >= '2500' && sGSV < '1000' THEN AREA MANAGER
        ELSE yourelsedefintion
    END)
FROM UserSalesVolume
GROUP BY UserID;