Hi,
I have three fields that make up employees' names in the database i.e.
FirstName, MiddleInitial and LastName fields. FirstName and LastName fields
are varchar(30) and varchar(50) respectively. MiddleInitial field is char(1)
.
When I use the FirstName + ' ' + MiddleInitial + ' ' + LastName AS
EmployeeName construct, I get no data because most records have a null value
for MiddleInitial field.
If I ask for FirstName + ' ' + LastName AS EmployeeName, I get results as
desired.
How can I take data from all three fields and return EmployeeName as single
field in a stored procedure? Even if I make it work, I'll have two spaces
between FirstName and LastName if the employee has no MiddleInitial due to
null value in that field. I'm sure there's a civilized way to do this. I'd
appreciate some pointers here.
Thanks,
SamYou need to use COALESCE, ISNULL or CASE in your SELECT query:
CREATE TABLE Names
(
FirstName nvarchar(20) NOT NULL
,MiddleInitial nchar(1) NULL
,LastName nvarchar(30) NOT NULL
)
INSERT INTO Names (FirstName, MiddleInitial, LastName)
VALUES ('John', NULL , 'Doe')
INSERT INTO Names (FirstName, MiddleInitial, LastName)
VALUES ('Alfred', 'E', 'Neuman')
SELECT
FirstName + ' ' + COALESCE(MiddleInitial + ' ', '') + LastName
AS EmployeeName
FROM Names
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:2F5162E6-A0C5-47F9-8252-A5EA8243C76F@.microsoft.com...
> Hi,
> I have three fields that make up employees' names in the database i.e.
> FirstName, MiddleInitial and LastName fields. FirstName and LastName field
s
> are varchar(30) and varchar(50) respectively. MiddleInitial field is char(
1).
> When I use the FirstName + ' ' + MiddleInitial + ' ' + LastName AS
> EmployeeName construct, I get no data because most records have a null val
ue
> for MiddleInitial field.
> If I ask for FirstName + ' ' + LastName AS EmployeeName, I get results as
> desired.
> How can I take data from all three fields and return EmployeeName as singl
e
> field in a stored procedure? Even if I make it work, I'll have two spaces
> between FirstName and LastName if the employee has no MiddleInitial due to
> null value in that field. I'm sure there's a civilized way to do this. I'd
> appreciate some pointers here.
> --
> Thanks,
> Sam|||Great... Thank you very much Tibor.
--
Thanks,
Sam
"Tibor Karaszi" wrote:
> You need to use COALESCE, ISNULL or CASE in your SELECT query:
> CREATE TABLE Names
> (
> FirstName nvarchar(20) NOT NULL
> ,MiddleInitial nchar(1) NULL
> ,LastName nvarchar(30) NOT NULL
> )
> INSERT INTO Names (FirstName, MiddleInitial, LastName)
> VALUES ('John', NULL , 'Doe')
> INSERT INTO Names (FirstName, MiddleInitial, LastName)
> VALUES ('Alfred', 'E', 'Neuman')
> SELECT
> FirstName + ' ' + COALESCE(MiddleInitial + ' ', '') + LastName
> AS EmployeeName
> FROM Names
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:2F5162E6-A0C5-47F9-8252-A5EA8243C76F@.microsoft.com...
>|||Also you might want to have a look at concat_null_yields_null parameter
in BOLsql
Showing posts with label employees. Show all posts
Showing posts with label employees. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
need design advice
we are desgning a db that will keep name and address information for all
kinds of entities: customers, employees, vendors, contacts, etc. Instead of
having N/A fields in each of the tables it seems like a good idea to have
one nameaddess table keyed by an identity int and have all of the other
tables reference this table via foreign key (either implicit or explicit).
What are the pros and cons of doing this kind of design?
Thanks,
T
Not sure what your referencing by an "N/A" field. However, if I'm
following you correctly, setting this up will depend if address can
have more than one entity (ie: one address can be a vendor and a
customer or something like that).
If not, then 2 tables should do it:
tbl_addresses
tbl_entities
the entities table will have "customers", "employees" etc. marked with
a PK (ie: "entity_id"). Then, of course, you'd have an "entity_id"
column in the addresses table that's a FK.
If an address can have more than one entity, then you can add a third
table that has three columns: the PK for the third table and 2 FK's
(one for addresses and one for entities). Here you wouldn't need the
FK in the "adresses" table.
So, let's say address #8 in the table has 1 entity and address #9 has
3, your 3rd table's data would something like:
ID ADDRESS_ID ENTITY_ID
1 8 2
2 9 2
3 9 4
4 9 5
and so on...
Of course, the "entity_id" shown above would refer to customers or
employees or whatever.
Hope that makes sense or helps in some way!
Jeremy
kinds of entities: customers, employees, vendors, contacts, etc. Instead of
having N/A fields in each of the tables it seems like a good idea to have
one nameaddess table keyed by an identity int and have all of the other
tables reference this table via foreign key (either implicit or explicit).
What are the pros and cons of doing this kind of design?
Thanks,
T
Not sure what your referencing by an "N/A" field. However, if I'm
following you correctly, setting this up will depend if address can
have more than one entity (ie: one address can be a vendor and a
customer or something like that).
If not, then 2 tables should do it:
tbl_addresses
tbl_entities
the entities table will have "customers", "employees" etc. marked with
a PK (ie: "entity_id"). Then, of course, you'd have an "entity_id"
column in the addresses table that's a FK.
If an address can have more than one entity, then you can add a third
table that has three columns: the PK for the third table and 2 FK's
(one for addresses and one for entities). Here you wouldn't need the
FK in the "adresses" table.
So, let's say address #8 in the table has 1 entity and address #9 has
3, your 3rd table's data would something like:
ID ADDRESS_ID ENTITY_ID
1 8 2
2 9 2
3 9 4
4 9 5
and so on...
Of course, the "entity_id" shown above would refer to customers or
employees or whatever.
Hope that makes sense or helps in some way!
Jeremy
need design advice
we are desgning a db that will keep name and address information for all
kinds of entities: customers, employees, vendors, contacts, etc. Instead of
having N/A fields in each of the tables it seems like a good idea to have
one nameaddess table keyed by an identity int and have all of the other
tables reference this table via foreign key (either implicit or explicit).
What are the pros and cons of doing this kind of design?
Thanks,
TNot sure what your referencing by an "N/A" field. However, if I'm
following you correctly, setting this up will depend if address can
have more than one entity (ie: one address can be a vendor and a
customer or something like that).
If not, then 2 tables should do it:
tbl_addresses
tbl_entities
the entities table will have "customers", "employees" etc. marked with
a PK (ie: "entity_id"). Then, of course, you'd have an "entity_id"
column in the addresses table that's a FK.
If an address can have more than one entity, then you can add a third
table that has three columns: the PK for the third table and 2 FK's
(one for addresses and one for entities). Here you wouldn't need the
FK in the "adresses" table.
So, let's say address #8 in the table has 1 entity and address #9 has
3, your 3rd table's data would something like:
ID ADDRESS_ID ENTITY_ID
----
1 8 2
2 9 2
3 9 4
4 9 5
and so on...
Of course, the "entity_id" shown above would refer to customers or
employees or whatever.
Hope that makes sense or helps in some way!
Jeremysql
kinds of entities: customers, employees, vendors, contacts, etc. Instead of
having N/A fields in each of the tables it seems like a good idea to have
one nameaddess table keyed by an identity int and have all of the other
tables reference this table via foreign key (either implicit or explicit).
What are the pros and cons of doing this kind of design?
Thanks,
TNot sure what your referencing by an "N/A" field. However, if I'm
following you correctly, setting this up will depend if address can
have more than one entity (ie: one address can be a vendor and a
customer or something like that).
If not, then 2 tables should do it:
tbl_addresses
tbl_entities
the entities table will have "customers", "employees" etc. marked with
a PK (ie: "entity_id"). Then, of course, you'd have an "entity_id"
column in the addresses table that's a FK.
If an address can have more than one entity, then you can add a third
table that has three columns: the PK for the third table and 2 FK's
(one for addresses and one for entities). Here you wouldn't need the
FK in the "adresses" table.
So, let's say address #8 in the table has 1 entity and address #9 has
3, your 3rd table's data would something like:
ID ADDRESS_ID ENTITY_ID
----
1 8 2
2 9 2
3 9 4
4 9 5
and so on...
Of course, the "entity_id" shown above would refer to customers or
employees or whatever.
Hope that makes sense or helps in some way!
Jeremysql
need design advice
we are desgning a db that will keep name and address information for all
kinds of entities: customers, employees, vendors, contacts, etc. Instead of
having N/A fields in each of the tables it seems like a good idea to have
one nameaddess table keyed by an identity int and have all of the other
tables reference this table via foreign key (either implicit or explicit).
What are the pros and cons of doing this kind of design?
Thanks,
TNot sure what your referencing by an "N/A" field. However, if I'm
following you correctly, setting this up will depend if address can
have more than one entity (ie: one address can be a vendor and a
customer or something like that).
If not, then 2 tables should do it:
tbl_addresses
tbl_entities
the entities table will have "customers", "employees" etc. marked with
a PK (ie: "entity_id"). Then, of course, you'd have an "entity_id"
column in the addresses table that's a FK.
If an address can have more than one entity, then you can add a third
table that has three columns: the PK for the third table and 2 FK's
(one for addresses and one for entities). Here you wouldn't need the
FK in the "adresses" table.
So, let's say address #8 in the table has 1 entity and address #9 has
3, your 3rd table's data would something like:
ID ADDRESS_ID ENTITY_ID
----
1 8 2
2 9 2
3 9 4
4 9 5
and so on...
Of course, the "entity_id" shown above would refer to customers or
employees or whatever.
Hope that makes sense or helps in some way!
Jeremy
kinds of entities: customers, employees, vendors, contacts, etc. Instead of
having N/A fields in each of the tables it seems like a good idea to have
one nameaddess table keyed by an identity int and have all of the other
tables reference this table via foreign key (either implicit or explicit).
What are the pros and cons of doing this kind of design?
Thanks,
TNot sure what your referencing by an "N/A" field. However, if I'm
following you correctly, setting this up will depend if address can
have more than one entity (ie: one address can be a vendor and a
customer or something like that).
If not, then 2 tables should do it:
tbl_addresses
tbl_entities
the entities table will have "customers", "employees" etc. marked with
a PK (ie: "entity_id"). Then, of course, you'd have an "entity_id"
column in the addresses table that's a FK.
If an address can have more than one entity, then you can add a third
table that has three columns: the PK for the third table and 2 FK's
(one for addresses and one for entities). Here you wouldn't need the
FK in the "adresses" table.
So, let's say address #8 in the table has 1 entity and address #9 has
3, your 3rd table's data would something like:
ID ADDRESS_ID ENTITY_ID
----
1 8 2
2 9 2
3 9 4
4 9 5
and so on...
Of course, the "entity_id" shown above would refer to customers or
employees or whatever.
Hope that makes sense or helps in some way!
Jeremy
Friday, March 9, 2012
Need Advice (pulling my hair out)
I am the only DBA for a company of about 200 employees which makes about 100 million a year; I have 8 SQL servers some with over 70 databases on them that feed our web sites and educational web sites. I also have a few databases that are between 75 and 120 Gig that is for our circulation system. I am the Systems admin for all of the systems that run of my SQL server, so that makes our circ system, tradeshow system, accounting system, web sites there are about 90 of them or so. I also do programming for our IT department and some web site stuff as well as for our tradeshow dept. I am the answer man for our sales people and programmers, I also have the knowledge to build by own servers and run a network. So that’s over 200 databases plus all the other *** that I do. Oh and by the way I'm supposed to help out and cover for our Tec support guys, witch there are 2 of them by the way. I can never take any more than 3 days off at a time; I have a month’s vacation. I do all this for under $55,000 a year. I am never included in any decisions on software that runs on SQL nor am I consulted on any thing having to do with SQL. I work for a director who knows nothing about SQL server. There are 2 JR data guys and they do just data queries and deal with the day to day circ requests. These guys both have an office not to mention the tech guys have an office and guess what I am in a cube. I asked my boss for a laptop with a Verizon air card so when I’m not at work if something happens that needs my attention I do have to drive to work or go home and remote in, here is the best part he looked at me and asked if I was high. I am on call 24/7 365. I have over 10 years experience working with SQL. I’m I crazy for staying? I have been with this company for almost 8 years; I am the one who started them out on SQL server. I have converted every thing over to SQL 98.5% of all the company data I am in charge of. What would you do?
In a nutshell...
You're in a toxic environment that will not improve. You've heard of the saying "A prophet is never welcomed in his own home country", right? This applies to you in this situation because, in your own words, you have 10 years of experience but have been with the same company for 8 of those 10 years. You are at a stage in your current employment situation where you are being taken for granted. And the salary you mentioned...I'd have to know what area of the country you're working in to know what is reasonable, but $55k with 10 years of experience is damned-near insulting. To put it in perspective, I too have 10 years of experience. I have changed companies every 2-3 years and now earn more than twice the figure you quoted (in Austin, Texas).
Do yourself a favor: Go to http://www.careerbuilder.com and be prepared for an eye-opening experience. Upload your resume and be prepared to field a ton of phone calls. A person with 10 years of experience is always a hot catch for a company, and you can rest assured you'll find one that will make you happy.
To answer your final question: I would leave. Pure and simple.
|||In the US, $50K is marginally entry level for DBA.
It is really time to do some 'soul-searching', and plan your future.
Just like employers conduct 'annual reviews', employees should also do their own 'annual review'. It may be the 'right' position for you, only you know all of the details.
|||Thanks for your response. I live in Phoenix Az and I found out that DBA's with my experience level range from 75 to 92 k a year. I am so outta here. Careerbuilder and monster have lots of possibilities.|||My employers annual review was poor. According to salary.com entry level DBA's range from 60k to 68,500. Needles to say I'm getting used and insulted. Just one other thing with my company for myself and my family to get med insurance with the company is 685.00 a month.
Subscribe to:
Posts (Atom)