How do I query these tables. Maybe use CASE expression?

In-efficient 61 Reputation points
2021-04-24T19:50:41.19+00:00

Hi,
I have two tables: dbo.GeneralInfo(ID, FYQtr) and dbo.EmpData(ID, Status, FYQtr). Both these tables are joined on two fields: ID & FYQtr.
My question is if dbo.GeneralInfo.ID has more than one entry in the dbo.EmpData.Status column, I want it to say "Multiple Entry", which ID 1001 & 1002 have multiple entry in Status field. How do I achieve this?

90903-screenshot-query-result.png

Here is the query:SELECT DISTINCT A.*, B.Status FROM TestEmpID..GeneralInfo A JOIN TestEmpID..EmpData B ON A.ID=B.ID AND A.FYQtr=B.FYQtr

Here are the table definitions and their data:
CREATE TABLE [TestEmpID]..EmpData;
INSERT INTO TestEmpID..EmpData(ID,[Status], FYQtr)
VALUES('1001','RegEmp','FY2019Q1'),('1002','AssMgr','FY2019Q2'),('1003','Mgr','FY2019Q3'),('1004','Dir','FY2019Q4')
,('1005','RegEmp','FY2020Q1'),('1001','AssMgr','FY2020Q2'),('1002','Mgr','FY2020Q3'),('1006','Dir','FY2020Q4')
,('1007','RegEmp','FY2021Q1'),('1008','AssMgr','FY2021Q2'),('1001','Mgr','FY2021Q3'),('1002','Dir','FY2021Q4')

CREATE TABLE TestEmpId.dbo.GeneralInfo([ID] INT,[FYQtr] VARCHAR(10));
INSERT INTO CREATE TABLE [TestEmpID].[dbo].GeneralInfo
VALUES('1001','FY2019Q1'),('1001','FY2020Q2'),('1001','FY2021Q3'),('1002','FY2019Q2'),
('1002','FY2020Q3'),('1002','FY2021Q4'),('1003','FY2019Q3'),('1004','FY2019Q4'),
('1005','FY2020Q1'),('1006','FY2020Q4'),('1007','FY2021Q1'),('1008','FY2021Q2'),

No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 68,621 Reputation points MVP
    2021-04-24T20:56:25.713+00:00

    Could this be something?

    SELECT A.ID, A.FYQtr,B.Status,
           CASE WHEN MIN(B.Status) OVER (PARTITION BY A.ID) <>
                     MAX(B.Status) OVER (PARTITION BY A.ID)
                THEN 'Multiple values'
           END
    FROM   GeneralInfo A
    JOIN   EmpData B ON A.ID = B.ID
                   AND A.FYQtr = B.FYQtr
    

0 additional answers

Sort by: Most helpful