UPDATE involving CASE expression

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UPDATE involving CASE expression

Author

Topic

rimitoste
Starting Member

2 Posts

Posted - 2007-10-25 : 13:08:05

I am one very frustrated beginner. If it were not for wonderful information in this forum I would have taken early retirement by now!

Table contains information about new and departing computer and phone users in several departments which we support. This is an existing table which I'm trying to clean up.
The essential part:

CREATE TABLE [dbo].[HelpDesk_NewUser](
[AutoNumber] [int] IDENTITY(1,1) NOT NULL,
[SubmittedDate] [datetime] NULL,
[FirstName] [varchar](100) NOT NULL,
[LastName] [varchar](100) NOT NULL,
[Department] [varchar](100) NOT NULL,
[StartDate] [datetime] NULL,
[DepartDate] [datetime] NULL
[RequestedBy] [varchar](100) NOT NULL,
[UpdatedBy] [varchar](16) NOT NULL,
[CurrentStatus] [varchar](10) NOT NULL ,
[DateCurrentStatus] [datetime] NULL,
[FormType] [varchar](10) NOT NULL,
) ON [PRIMARY]

There can be more than one record per FirstName, LastName. FormType can be N for new or D for departing.
I want to do this for each record:
Read the FormType and Department from the record with the most recent activity (SubmittedDate) for each user
Convert Department to a 4 character department number
Update CurrentStatus with (FormType concatenated with the 4 character dept number) in all records for that user.

I have created another table called UserMostRecent which contains the most recent record for each user.

I have written a query to do this by brute force (read a record, set local variables, update a record), but I would like learn a simpler way to do it.

I don't understand the syntax of CASE because it seems to change depending on where it is used.

Here is what I have tried that does not work. Error is "Incorrect syntax near word CASE"

UPDATE HelpDesk_NewUser
SET DateCurrentStatus = b.DateMostRecent,
CurrentStatus = (b.FormType + a.Department
CASE
WHEN 'Roads Department' THEN '3000'
WHEN 'Engineering and Survey Services' THEN '1900'
WHEN 'Waste Management' THEN '8999'
WHEN 'Kern Air Pollution Control District' THEN '9149'
WHEN 'Environmental Health' THEN '4113'
WHEN 'Building Inspection' THEN '2625'
WHEN 'Animal Control' THEN '2760'
WHEN 'Planning Department' THEN '2750'
WHEN 'Community and Economic Development' THEN '5940'
WHEN 'Resource Management Agency' THEN '2730'
WHEN 'Code Compliance' THEN '2620'
WHEN 'Roads Kern Regional Transit' THEN '8998'
END)
FROM HelpDesk_NewUser a JOIN UserMostRecent b
ON (a.LastName = b.LastName and a.FirstName = b.FirstName)

Thank you, forum participants. You are the best!

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-25 : 13:13:16

A case statement just returns a value.

I suspect you need a + after a.Department.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-25 : 13:15:10

Logic should probably say:

CASE WHEN a.Department = 'Roads Department' THEN '3000' WHEN a.Department = 'Engineering.....

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-25 : 13:57:30

Try:

UPDATE HelpDesk_NewUser
SET DateCurrentStatus = b.DateMostRecent,
CurrentStatus = (b.FormType +
CASE a.Department
WHEN 'Roads Department' THEN '3000'
WHEN 'Engineering and Survey Services' THEN '1900'
WHEN 'Waste Management' THEN '8999'
WHEN 'Kern Air Pollution Control District' THEN '9149'
WHEN 'Environmental Health' THEN '4113'
WHEN 'Building Inspection' THEN '2625'
WHEN 'Animal Control' THEN '2760'
WHEN 'Planning Department' THEN '2750'
WHEN 'Community and Economic Development' THEN '5940'
WHEN 'Resource Management Agency' THEN '2730'
WHEN 'Code Compliance' THEN '2620'
WHEN 'Roads Kern Regional Transit' THEN '8998'
END)
FROM HelpDesk_NewUser a JOIN UserMostRecent b
ON (a.LastName = b.LastName and a.FirstName = b.FirstName)

You should really have a table with these department names and numbers, hard-coding data like this into a sql statement is a bad design and a maintenance nightmare.

- Jeff
http://weblogs.sqlteam.com/JeffS

rimitoste
Starting Member

2 Posts

Posted - 2007-10-25 : 14:43:05

Wow, Jeff's code works perfectly and I'm even starting to understand

I'm scaring myself.

Right you are, Jeff. Lousy design. I will create a dept table. That will make things much cleaner. Right now the dept comes from a dropdown list in the asp.net program. This whole database has a bad design - columns are the wrong length and type, records from several tables needed to be combined, etc. I am trying to learn asp.net and SQL with no training and fix the design of the tables and the screens too.

Once I get an idea, I try to learn how to make it work, even if it not the easiest way to do something. Then I try to consider easier ways to do things. I find that I learn more that way even if I get more frustrated in the process.

Thanks to all! You have made my day a happy one!

quote:
Originally posted by jsmith8858

You should really have a table with these department names and numbers, hard-coding data like this into a sql statement is a bad design and a maintenance nightmare.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources