CODEWITHSUNDEEP
sqlsql-servert-sql
YohanDe
YohanDe

Reputation: 39

Using SQL CASE Statement how to find multiple items

I need to get a value for “THEN” from “Mortgage_Type” column if bellow conditions are true. Mortgage_Type and Category are same table and Equipment from another table. Tables are joining using Item_No. I need to find the Mortgage_Type of each item. I have 20+ Mortgage_Types if Category is main and Equipment is null then should display relevant Mortgage_Type

when SUM (Mortgage_Type)is not null and SUM (Equipment)is null and sum(Category) =’M’ THEN “value from Mortgage_Type column”

Upvotes: 0

Views: 105

Answers (2)

Tyron78
Tyron78

Reputation: 4187

You could use a cte and a window function. Something like this:

DECLARE @t TABLE(
  [Contract] int
  ,[M_TYPE] nvarchar(100)
)

INSERT INTO @t VALUES
(1, 'V')
,(1, 'O')
,(1, 'M')
,(2, 'V')
,(3, 'V')
,(4, 'H')
,(4, 'V');

WITH cte AS(
  SELECT t.Contract,
         t.M_TYPE,
         COUNT(M_TYPE) OVER (PARTITION BY t.Contract) Multi
    FROM @t t
)
SELECT DISTINCT [Contract], CASE
                              WHEN Multi > 1 THEN 'Multiple'
                              WHEN M_TYPE = 'V' THEN 'VEHICLE'
                              WHEN M_TYPE = 'O' THEN 'OTHERS'
                              WHEN M_TYPE = 'M' THEN 'MORTGAGE'
                              WHEN M_TYPE = 'H' THEN 'HOUSE'
                              ELSE 'UNKNOWN'
                            END AS MortgageType
  FROM cte

Upvotes: 0

Hasan Alizada
Hasan Alizada

Reputation: 591

Maybe, this should help:

SELECT DISTINCT
       Contract,
       CASE
          WHEN CATOGORY NOT IN ('M', 'O') AND TYPE NOT IN ('V', 'E')
          THEN
             CASE
                WHEN M_TYPE = 'V' THEN 'VEHICLE'
                WHEN M_TYPE = 'O' THEN 'OTHERS'
                WHEN M_TYPE = 'M' THEN 'MORTGAGE'
                WHEN M_TYPE = 'H' THEN 'HOUSE'
                WHEN M_TYPE LIKE '%,%' THEN 'MULTIPLE'
             END
       END
          AS TYPE
  FROM Table1 LEFT JOIN Table2 ON Contract = ID

Upvotes: 1

Related Questions