Need HELP ASAP - SQL Server Forums

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

Need HELP ASAP

Author

Topic

NelsonAK
Starting Member

1 Post

Posted - 2012-07-09 : 03:10:16

I am a Full Time College Student and have literally spend the last 3 days straight working on this individual assignment. I am running out of time and still have a Team Assignment to work on and both are due in 24 hours.
I'm hoping that maybe someone that understands SQL much better than me can help me do this last part. Here are the details and any legit SQL example will help me so I can code it for my database.
I'm new to this forum and decided to give it a chance to see where it goes. If anyone can help it's very much appreciated because i'm really tired and stressed :(.

Here is the requirement left of this assignment:

Human Resources has requested a listing of employees. They
want the listing to show the first initial of the employee’s last
name. In addition, they want the report in the following sort
order: exempt/non-exempt status, the first letter of their last
name, last name, first name and salary. For example: Exempt, S,
Smith, Bob, $50,000

Now the way my tables are set up is I have an Employee Table and a JobTitle Table. They are in master.dbo database. I don't think I will need to use the JobTitle Table however because all the columns needed are in the Employee Table only. For The columns to complete this my columns are exactly as follows:

ExemptOrNonExemptStatus, InLastName, LastName, FirstName, & Salary.

If someone can give me either an example or the code so I can do this ASAP I would be so thankful.

---*You never know when it will be your time, so live life to the fullest & emjoy every minute of it*---

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2012-07-09 : 09:08:28

The following should get you the first letter of last name:


SUBSTRING('My name', 1, 1)

If you plug the select statement into your query and replace 'My Name' with the "name" column from your Database. When you order just use the statement above in your order by statement like:

order by column1, column2, SUBSTRING(LastName, 1, 1), column3 etc

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-09 : 09:18:30

Grifter's example isn't accurate.

Post a sample data set and then the required output from that dataset.

We try not to spoon feed for assignments but you'll get hints on how to do what you want.

What DBMS are you using? Is it SQL Server or Postgres or Oracle or MySQL or.........

Each flavour is different.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 09:35:35

hints are LEFT() function to get initial, ORDER BY to order your results. Have a look at MSDN documentation and try to do it yourself. In case of doubts post back

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2012-07-09 : 10:18:16

quote:
Originally posted by Transact Charlie

Grifter's example isn't accurate.

Can you explain what is not accurate and why just in case I miss anything?

Thanks

G

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-09 : 12:03:06

quote:
Originally posted by Grifter

quote:
Originally posted by Transact Charlie

Grifter's example isn't accurate.

Can you explain what is not accurate and why just in case I miss anything?

Thanks

G

Sure.

Running:


SELECT SUBSTRING('My name', 1, 1)

Produced the value 'M'

whereas what's required is the value 'n' (first character of last word)

Finding that isn't actually completely trivial. Here's some exmaples


DECLARE @Names TABLE (
	[FullName] VARCHAR(MAX)
	)
	
INSERT @Names VALUES
	('Transact Charlie')
	, ('Grifter')
	, ('Fred Flintstone')
	, ('Barney Rubble')
	, ('')
	, (' ')
	, (' FOO')
	
SELECT
	[Fullname]
	, SUBSTRING([Fullname], 1, 1) AS [First Character]
	, SUBSTRING([Fullname], LEN([Fullname]) + 2 - CHARINDEX(' ', REVERSE([Fullname])), 1) AS [First Character of Last Word]
FROM
	@Names

Results :


(7 row(s) affected)
Fullname                       First Character First Character of Last Word
------------------------------ --------------- ----------------------------
Transact Charlie               T               C
Grifter                        G               
Fred Flintstone                F               F
Barney Rubble                  B               R
                                               
                                                
 FOO                                           F

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-09 : 12:04:31

and you'd probably need to take care of the special case of a string with no spaces it in ('Grifter' should produce 'G' Probably)

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2012-07-09 : 12:11:42

quote:
Originally posted by Transact Charlie

quote:
Originally posted by Grifter

[quote]Originally posted by Transact Charlie

Grifter's example isn't accurate.

Can you explain what is not accurate and why just in case I miss anything?

Ah right I mean't to have no space in there like:

'MyName' as in we were only looking at a last name field

So it would have picked up M, I also had code for extracting last name character in a full name column:


begin 
declare @Name Varchar(50)
set @Name = 'john h smith'

select substring(RIGHT(@Name, charindex(' ', Reverse(@Name), 1)- 1), 1, 1)
	
end

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-09 : 12:21:54

that'll die on strings with no spaces in em:


begin 
declare @Name Varchar(50)

SET @Name = 'foo'
select
	substring(RIGHT(@Name, charindex(' ', Reverse(@Name), 1)- 1), 1, 1)
	
end

Results:


Msg 536, Level 16, State 2, Line 5
Invalid length parameter passed to the RIGHT function.

So this is safe (and works for all cases)


DECLARE @Names TABLE (
	[FullName] VARCHAR(MAX)
	)
	
INSERT @Names VALUES
	('Transact Charlie')
	, ('Grifter')
	, ('Fred Flintstone')
	, ('Barney Rubble')
	, ('')
	, (' ')
	, (' FOO bar')
	
SELECT
	[Fullname]
	, CASE
		WHEN CHARINDEX(' ', [fullname], 0) > 0 THEN SUBSTRING(RIGHT([Fullname], CHARINDEX(' ', REVERSE([Fullname]), 1)- 1), 1, 1)
		ELSE SUBSTRING([Fullname], 1, 1)
	END
FROM
	@Names

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2012-07-09 : 12:36:43

Ok thanks for the modifications

G

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources