May 7, 2012 at 8:07 pm
Hi all-
I know this is a newbie question but...
I am trying to generate a dynamic case statement where each case is related to a look-up table row.
I have the following code(that I got help with on this forum) but it is using hard coded case elements.
;WITH EmpGroups AS (
SELECT
CASE
WHEN test.EmploymentStatus.EmploymentStatusDesc = 'Full-time' then 'Full-time'
WHEN test.EmploymentStatus.EmploymentStatusDesc = 'Part-time' then 'Part-time'
WHEN test.EmploymentStatus.EmploymentStatusDesc = 'Retired' then 'Retired'
WHEN test.EmploymentStatus.EmploymentStatusDesc = 'Student' then 'Student'
WHEN test.EmploymentStatus.EmploymentStatusDesc = 'Unemployeed' then 'Unemployeed'
ELSE 'Other'
END AS EmpGroup
FROM test.EmploymentStatus inner join test.employment on test.employment.employmentstatusID = test.employmentstatus.employmentstatusID
inner join test.volunteer on test.volunteer.partyid = test.employment.PartyID
)
In this example the EmploymentStatus.EmploymentStatusDesc field may be one of many employment descriptions. Is it possible to to have the Full-time, Part-time etc dynamically populated with results from a query. eg Select EmploymentStatus.EmploymentStatusDesc from EmploymentStatus?
As always thanks for your help.
Andy
May 8, 2012 at 3:28 am
Yes you can do it as follows:
Select (Case When field1 IN (Select field1 From Tbl Where field2 = 'Ann') Then field2 Else '' End ) From Tbl
I hope by Dynamically you mean Selecting From Another Table.
May 13, 2012 at 4:32 pm
Hi all -
Let me try and be more specific in my question as well as formatting the code a little nicer.
I am trying to automatically generate a case statement(see below) with the results of a query.
I hard coded the case statement with fields from the EmploymentStatusDesc column in the EmployementStatus table.
What I would like to do is use a query like:
SELECT EmploymentStatusDesc
FROM
EmploymentStatus
then use the results to populate the case statement below. The code below is the hard coded version.
;WITH EmpGroups AS (
SELECT
CASE
WHEN EmploymentStatus.EmploymentStatusDesc = 'Full-time' then 'Full-time'
WHEN EmploymentStatus.EmploymentStatusDesc = 'Part-time' then 'Part-time'
WHEN EmploymentStatus.EmploymentStatusDesc = 'Retired' then 'Retired'
WHEN EmploymentStatus.EmploymentStatusDesc = 'Student' then 'Student'
WHEN EmploymentStatus.EmploymentStatusDesc = 'Unemployeed' then 'Unemployeed'
ELSE 'Other'
END AS EmpGroup
FROM EmploymentStatus inner join
employment on employment.employmentstatusID = employmentstatus.employmentstatusID
inner join volunteer on volunteer.partyid = employment.PartyID
)
The results of the EmploymentStatus query will replace the "Full-time", "Part-time" etc.
I hope that I have made my problem much clearer.
Thanks
Andy
May 13, 2012 at 9:42 pm
Typically a lookup table like EmploymentStatus will allow your application to populate a related field table in your Employees table.
In one scenario the description from the lookup table ('Full-Time') would be populated into you Employees table. That doesn't see to be the case since you wouldn't be having an issue.
Alternatively a code from your lookup table would be inserted into your Employee table. If this is the case you would join the two tables on that field which would allow you to retrieve the description field from your lookup table.
What other fields does your lookup table have? Does your Employee table have a corresponding field?
Typically you would want something like this:
Select
E.EmployeeID,
ES.Status
FROM Employees E
INNER JOIN EmployeeStatus ES
ON E.StatusCode = ES.StatysCode
A CASE statement where the WHEN and THEN values are equivalent is inherently not useful.
May 14, 2012 at 7:02 am
Andy:
See if this is what you mean. I use this for the same thing. Change the select statement inside the cursor to the column and table you want to use. I use it for database conversions, mostly.
/* ========================================================================================================================= */
/* print out script of case WHEN statements for all distinct values of a column */
/* (possibly used in converting lookup values from one database to another) */
USE AccessConvDB2
GO
SET NOCOUNT ON
DECLARE @vl varchar(2000)
DECLARE @isstring bit
SET @isstring=1--set 1 for is a string 0 for numeric value (1=true, 0=false)
DECLARE backupFiles CURSOR FOR
SELECT DISTINCT BondType FROM altBonds ORDER BY BondType-- change this line
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @vl-- change this line if neeeded
WHILE @@FETCH_STATUS = 0
BEGIN
if @isstring = 1
PRINT 'WHEN ''' + @vl + ''' THEN '''''
else
PRINT 'WHEN ''' + @vl + ''' THEN 1'
FETCH NEXT FROM backupFiles INTO @vl-- change this line if needed
END
CLOSE backupFiles
DEALLOCATE backupFiles
SET NOCOUNT OFF
May 14, 2012 at 7:30 am
If I read your question correctly, you want to generate the query. I've setup the table :
CREATE TABLE EmploymentStatus (ID int PRIMARY KEY NOT NULL IDENTITY(1,1),
EmploymentStatusDesc varchar(50))
GO
INSERT INTO EmploymentStatus (EmploymentStatusDesc)
SELECT 'Full-time' UNION ALL
SELECT 'Part-time' UNION ALL
SELECT 'Retired' UNION ALL
SELECT 'Student' UNION ALL
SELECT 'Unemployeed'
This should generate the query :
SELECT ';WITH EmpGroups AS ('
UNION ALL
SELECT 'SELECT CASE'
UNION ALL
SELECT ' WHEN EmploymentStatus.EmploymentStatusDesc = ''' + EmploymentStatusDesc + ''' THEN ''' + EmploymentStatusDesc + ''''
FROM EmploymentStatus
UNION ALL
SELECT 'ELSE ''other'''
UNION ALL
SELECT 'END AS EmpGroup'
UNION ALL
SELECT 'FROM EmploymentStatus inner join'
UNION ALL
SELECT 'employment on employment.employmentstatusID = employmentstatus.employmentstatusID'
UNION ALL
SELECT 'inner join volunteer on volunteer.partyid = employment.PartyID'
UNION ALL
SELECT ')'
Copy the results to a new edit session and you have your query.
May 15, 2012 at 1:28 pm
I'm super confused. Please reference the article in my signature by Jeff Moden, otherwise you are going to get 20 responses that all do something different and none of them answer your question.
Jared
CE - Microsoft
May 15, 2012 at 3:04 pm
I am confused too!
I have read the article and I thought I was doing pretty good following it.
Can you be more specific about what I am doing wrong instead of just pointing me to the article.
Thanks
Andy
May 15, 2012 at 3:10 pm
What is confusing is what you are trying to accomplish. Why do you want to dynamically create the case statement? What are you trying to accomplish with this dynamically generated query?
May 15, 2012 at 3:16 pm
You're missing the most important parts 🙂 Table definitions for your tables and sample data. This way, we can simulate on our own machines. Sample data does not mean a small amount of actual data, it should be made up and fake to not compromise company data. Then show us expected results. We can apply your code to those tables and understand it much clearer.
Jared
CE - Microsoft
May 15, 2012 at 3:21 pm
Just because we can generate a case statement, doesn't mean we should.
May 15, 2012 at 3:26 pm
andy 56206 (5/13/2012)
Hi all -Let me try and be more specific in my question as well as formatting the code a little nicer.
I am trying to automatically generate a case statement(see below) with the results of a query.
I hard coded the case statement with fields from the EmploymentStatusDesc column in the EmployementStatus table.
What I would like to do is use a query like:
Ah. Alright, there's two pieces to this if I'm understanding what's going on here. First is how to dynamically generate ANY statement. This requires not including the table in the query definition but using the table that has the status (in this case EmploymentStatus) from outside the query to build out the inner query. If you take a look at what Steve Cullen nicely provided you it'll provide you with a baseline of the understanding.
However! What it looks like you're trying to do is have 'Other' listed as any value that's not currently available in the EmploymentStatus table. That's actually MUCH easier to do:
SELECT
ISNULL( es.EmploymentStatusDesc, 'OTHER') AS EmpGroup
FROM
employment AS e
inner join
volunteer AS v
onvolunteer.partyid = employment.PartyID
LEFT JOIN
EmploymentStatus AS es
ONes.employmentstatusID = e.employmentstatusID
I believe that will give you the same result as what you're looking for with the massive dynamic case statement.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 15, 2012 at 3:51 pm
Third time is a charm!
Let me start again.
I am trying to determine the total number of applicants/users/employees who fall into specific categories.
For example: In the following case statement I am getting the number of records for each of the following employment status, full-time part-time etc
Each status is is configurable by the customer and is stored in a look-up table called employmentStatus. The field in question employmentStatusDesc stores the text description entered by the customer.
I initially hard coded the case statement with a few choices.
I know how to do this.
My issues is that I do not want to hard code all the possible combinations of employmentsStatusDesc, I want to pull all of the possible choices that are available in the employmentStatus table and populate(if that is the correct way to do this) the case statement with the results from the query of the employmentStatus table.
Here is my current CASE STATEMENT that has hard coded values. The words in bold should come from the employmentStatusDesc in the employmentStatus table.
;WITH EmpGroups AS (
SELECT
CASE
WHEN EmploymentStatus.EmploymentStatusDesc = 'Full-time' then 'Full-time'
WHEN EmploymentStatus.EmploymentStatusDesc = 'Part-time' then 'Part-time'
WHEN EmploymentStatus.EmploymentStatusDesc = 'Retired' then 'Retired'
WHEN EmploymentStatus.EmploymentStatusDesc = 'Student' then 'Student'
WHEN EmploymentStatus.EmploymentStatusDesc = 'Unemployeed' then 'Unemployeed'
ELSE 'Other'
END AS EmpGroup
FROM EmploymentStatus
)
The results of the EmploymentStatus query will replace the "Full-time", "Part-time" etc.
I hope that I have made my problem much clearer.
Thanks
Andy
May 15, 2012 at 3:55 pm
andy 56206 (5/15/2012)
Third time is a charm!Let me start again.
I am trying to determine the total number of applicants/users/employees who fall into specific categories.
For example: In the following case statement I am getting the number of records for each of the following employment status, full-time part-time etc
Each status is is configurable by the customer and is stored in a look-up table called employmentStatus. The field in question employmentStatusDesc stores the text description entered by the customer.
I initially hard coded the case statement with a few choices.
I know how to do this.
My issues is that I do not want to hard code all the possible combinations of employmentsStatusDesc, I want to pull all of the possible choices that are available in the employmentStatus table and populate(if that is the correct way to do this) the case statement with the results from the query of the employmentStatus table.
Here is my current CASE STATEMENT that has hard coded values. The words in bold should come from the employmentStatusDesc in the employmentStatus table.
;WITH EmpGroups AS (
SELECT
CASE
WHEN EmploymentStatus.EmploymentStatusDesc = 'Full-time' then 'Full-time'
WHEN EmploymentStatus.EmploymentStatusDesc = 'Part-time' then 'Part-time'
WHEN EmploymentStatus.EmploymentStatusDesc = 'Retired' then 'Retired'
WHEN EmploymentStatus.EmploymentStatusDesc = 'Student' then 'Student'
WHEN EmploymentStatus.EmploymentStatusDesc = 'Unemployeed' then 'Unemployeed'
ELSE 'Other'
END AS EmpGroup
FROM EmploymentStatus
)
The results of the EmploymentStatus query will replace the "Full-time", "Part-time" etc.
I hope that I have made my problem much clearer.
Thanks
Andy
Nope, sorry. Still doesn't make sense. Trying to figure out how you are using this and there isn't enough information to do that.
May 15, 2012 at 4:45 pm
Why don't you simply join to the lookup table?
Why do you need a case expression at all?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply