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.
| Author |
Topic |
|
decem
Starting Member
21 Posts |
 Posted - 2005-06-09 : 12:59:25
|
| please help.. i have no idea where to begin w/ this.. i have the following view combining the data from two tables, adusers (data in this table imported from Active Directory) and vwUsers2_EHD (data here imported from Human Resources database):CREATE view view_vANDa ASSELECT TOP 1000000000a.logonid as a_BFUSAlogonid,v.LastName as v_lastname,v.FirstName as v_firstname,v.MiddleInitial as v_middleinitial,a.vip as a_vip,a.ssn as a_ssn, v.employeecategorydescription as EmployeeCategory,v.businessgroupcode as [Group],v.companydescription as Company,v.divisiondescription as Div,v.departmentdescription as Dept,v.physicallocationcode as LocationCode,v.physicallocationdescription as LocationDescription,v.physicallocationfloor as [Floor],v.physicallocationquadrantdescription as Quad,v.physicallocationaddress1 as v_workaddress1,v.physicallocationaddress2 as v_workaddress2,v.physicallocationcity as v_city,v.physicallocationstate as v_state,v.physicallocationzipcode as v_zip,v.networkphone as NetPhone, v.networkphoneextension as NetPhoneExt,v.offnetworkphone as OffNetPhone,v.offnetworkphoneextension as OffNetPhoneExt,v.fax as v_fax,v.email as v_email,right (a.exchangeserver, 11) as exchangesvr,a.homedir, a.billingdept as a_billingdept,from vwUsers2_EHD as vLeft Outer Join adusers as a ON v.userid = a.ssn and v.lastname = a.lastname and v.firstname = a.firstname and v.email = a.emaileither from this view or within this view, i need to create a USERNAME column which should do the following:1. when a_BFUSAlogonid is not null, then username should = a_BFUSAlogonid2. when a_BFUSAlogonid is null, then i need to create the username with the following characteristics:---a. i have to combine v_lastname + v_firstname.---b. it can't longer than 12 characters, but it can be shorter.---c. it must be unique------i. i have to make sure the one i create is not the same as any a_bfusalogonid entry. ------ii. if there are duplicates (like the 14 entries for smithcharles that "LOWER(LEFT(v.Lastname + v.firstname, 12)) AS TESTusername" results in, i want to (a) keep the first one, and then (b) take off a letter and add a number, as such:smithcharlessmithcharle1smithcharle2smithcharle3smithcharle4..smithcharl14 is this possible? please.. any input is greatly appreciated..thank you. mike |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-06-09 : 14:21:15
|
with just a select hardly.you can only add columns to tables not to views.you can of course do:select ..., (case when a_BFUSAlogonid is not null then a_BFUSAlogonid else left(v_lastname + v_firstname, 12) end) as USERNAME from ...however that solves only part of your problem.Go with the flow & have fun! Else fight the flow  |
 |
|
|
decem
Starting Member
21 Posts |
Posted - 2005-06-10 : 09:33:21
|
| thanks spirit1..here's more info about my situation: i have one table that has all data from active directory of our domain (8,500 users) and then a table given to me by HR. if a person is in the AD table, then he's in the HR table, but there are another 32,000 in the HR table that aren't in AD. moreover, the AD table has slightly different/more info than the HR table. on top of this, the HR table is filled w/ dirty, inconsistent data.. (last names have spaces in them, includes jr. and sr. suffixes and st. prefixes, etc), as does the AD tabled (duplicate ssn entries where one acct was created, then trashed, then another created in its place for the same person w/ same ssn).. furthermore, while each entry in the HR table has an entry with a unique userid/ssn, only half of the 8,500 entries in the AD table have such an entry. purpose of doing all this: our help desk is expanding so that we'll soon be supporting more than just those in the domain but all of those in the HR database as well, which means that we have to merge the data in the HR database into ours so that the Remedy system we use to track cases can access that data as well.. given this info.. can you guys point in me in the right direction to go about handling this? am i doing the right thing by creating a view that accesses both tables?should i just have some sort of stored procedure/dts package that creates a new table? any suggestions for getting rid of spaces and periods in the data? what about how to join/combine the two tables when only half of those in AD have an unique userid/ssn entry? thanks again.. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-06-10 : 09:55:59
|
| well since the data in your tables is crap... first clean the data.views are useless if you have bad data behind it...createing a new table is a good idea in my opinion.better to have all the data in one place.Go with the flow & have fun! Else fight the flow |
|
|
|
decem
Starting Member
21 Posts |
Posted - 2005-06-10 : 10:58:50
|
| thanks again spirit.. i'm sorry i'm asking such basic questions.. what's the best way to go about cleaning the data? make HR do it? should i do it as i import it into the new table? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-06-10 : 11:04:14
|
well do what's easier for you  if you can make HR do it, let them.but i think you might do it faster. it's all a matter of a few updates.put the data into a temporary table. clean it there and then insert it into the real table and drop the temporary table.Go with the flow & have fun! Else fight the flow |
|
|
|
|
|
|
|
|
please help.. is this even possible?