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

Question

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

In-efficient 61

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?

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'),

Answer 1

Erland Sommarskog 68,621 MVP

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

In-efficient 61 Reputation points

2021-04-24T22:28:08.533+00:00

Than you, Erland.
For the example provided, yes, it works however, it does not work when I apply to my dev data. If I narrow it down to the following image, how can I query it?
Erland Sommarskog 68,621 Reputation points MVP

2021-04-25T09:29:32.137+00:00
I can only work with what you provide me. You gave us CREATE TABLE + INSERT for an example, and apparently my query gave you the result you wanted. Now you say that it "does not work" (without specifying that means), with your dev data, and you now show a simpler data set with only one table, and no fiscal year?

Am I right to believe that you spiced up your dev data with an extra table and an extra column for the fun ot if, and I am now looking at is the real thing? In such case:

SELECT ID, CASE WHEN MIN(Status) = MAX(Status) THEN MIN(Status) ELSE 'Multiple values' END AS Status FROM tbl GROUP BY ID

Or could it be that this one more a simplification of your real case? Which I can't help you with anyway, since I don't have a crystal ball where I sit.
In-efficient 61 Reputation points

2021-04-25T14:57:08.37+00:00

Thanks again.
It looks like you're looking over my shoulder. You're absolutely correct that I provided you a much more simplified data but I have more than 5 tables involved in my actual script. The first answer, I mistyped and just found that it correctly returns the desired data but I have to add the [Status] field to the GROUP BY... I wanted to get another option and put the results into a temp table then query the temp table, which is why I had removed the FY field.
And they both work.
I am sorry for not giving you the right data and thank you for the help.

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

0 additional answers

Activity