Solved

TSql coding for conditional results

Posted on 2013-05-15
2
244 Views
Last Modified: 2013-05-15
I need to pass the records of a table, record by record, to another set of logic that will pull certain results based on various conditions.

In Table1 I have two cols.  1)  StoreCode  2)JobCode.  This table gets updated via another process.  Typically no more than 10 records.  

I need to take each record (each StoreCode and JobCode combination) and pull from say an Employees table to get a list of each employee that matches that StoreCode and JobCode.

The catch is this:  In theory there should only be one record in the Employees table for each StoreCode and Jobe Code.  If only one record does exist I want to pull 3 fields from the employess table (Fname, Lname, Rank).

If no records are found then I just want to return one record with NULL for each of those 3 fields.

If multiple records are found I want to return one record with the values 'Duplicates', NULL, NULL

In the end result I need a table that has the same number of records that were in Table1 with the results found in the conditional logic above.

Results table would look similar to this:  

Result Set based on conditional results
0
Comment
Question by:d1cjm1ex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 500 total points
ID: 39167727
Hi,
only limited time therefore it might not look good (and there are perhaps better options) but the result is as expected:
SELECT
	EPT.FName,
	EPT.LName,
	EPT.[Rank],
	CBT.StoreCode,
	CBT.JobCode
FROM tblCombo CBT
LEFT JOIN tblEmployee AS EPT
ON EPT.FKJobCode = CBT.JobCode AND EPT.FKStoreCode = CBT.StoreCode
LEFT JOIN (SELECT FKStoreCode,FKJobCode, 1 AS SQR FROM tblEmployee GROUP BY FKStoreCode,FKJobCode HAVING COUNT(1) < 2) AS EPT3
ON EPT3.FKJobCode = CBT.JobCode AND EPT3.FKStoreCode = CBT.StoreCode
WHERE EPT3.SQR = 1 OR EPT.FKStoreCode IS NULL
UNION
SELECT
	'Duplicate' AS FName,
	NULL AS LName,
	NULL AS [Rank],
	CBT.StoreCode,
	CBT.JobCode
FROM tblCombo CBT
JOIN (SELECT FKStoreCode,FKJobCode FROM tblEmployee GROUP BY FKStoreCode,FKJobCode HAVING COUNT(1) > 1) AS EPT2
ON EPT2.FKJobCode = CBT.JobCode AND EPT2.FKStoreCode = CBT.StoreCode

Open in new window

HTH
Rainer
0
 

Author Closing Comment

by:d1cjm1ex
ID: 39168549
works as advertised.  Thanks.  I have a logic twist I need to add but will post that as another question as you did answer this specific question.  Hopefully you can look at it as I want to keep the code you sent.
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can we attach PDF to table 2 46
SQL Lag Function DateDiff 2 26
Creating a View from a CTE 15 45
Need split for SQL data 6 39
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question