February 5, 2008 at 6:59 am
Greetings to the group...
I'm working on an interface and can't figure out how to do this in SQL...
If an employee has a home phone, I want to return that... if there is no home phone listed, then return a cell number.
Currently we have to make sure everyone with a phone number has a "home" phone, even if it is a cell phone.
If they have both, the sql returns two rows and that messes up the target system.
It is kind of lengthy, but here is the select statement if it helps to clarify what I'm doing...
SELECT A.EMPLID AS PERSON_NBR,
(SELECT '005/'+'116'+ '/' + (LEFT(A.DEPTID,5))+ '/' + A.JOBCODE + '/' + 'NA'+ '/' + 'NA' + '/' + 'NA') AS LABORACCOUNT,
0 AS HOURLY_RT,
'' AS SUPERVISOR_NAME,
A.EMPL_STATUS AS EMPL_STATUS,
D.SERVICE_DT AS HIRE_DT,
'' AS AC_DEVICEGRP,
CASE A.GRADE
WHEN '01' THEN 'FABCO Salaried Non Exempt'
WHEN '02' THEN 'FABCO Salaried Non Exempt'
WHEN '03' THEN 'FABCO Salaried Non Exempt'
WHEN '04' THEN 'FABCO Salaried Non Exempt'
WHEN '05' THEN 'FABCO Salaried Non Exempt'
WHEN '06' THEN 'FABCO Salaried Non Exempt'
WHEN '07' THEN 'FABCO Salaried Non Exempt'
WHEN '08' THEN 'FABCO Salaried Non Exempt'
WHEN '09' THEN 'FABCO Salaried Non Exempt'
WHEN '10' THEN 'FABCO Salaried Non Exempt'
WHEN '11' THEN 'FABCO Salaried Exempt Eligible'
WHEN '12' THEN 'FABCO Salaried Exempt Eligible'
WHEN '13' THEN 'FABCO Salaried Exempt'
WHEN '14' THEN 'FABCO Salaried Exempt'
WHEN '15' THEN 'FABCO Salaried Exempt'
WHEN '16' THEN 'FABCO Salaried Exempt'
WHEN '17' THEN 'FABCO Salaried Exempt'
WHEN '18' THEN 'FABCO Salaried Exempt'
WHEN '19' THEN 'FABCO Salaried Exempt'
WHEN '20' THEN 'FABCO Salaried Exempt'
WHEN '21' THEN 'FABCO Salaried Exempt'
WHEN '22' THEN 'FABCO Salaried Exempt'
WHEN '23' THEN 'FABCO Salaried Exempt'
WHEN '24' THEN 'FABCO Salaried Exempt'
WHEN '25' THEN 'FABCO Salaried Exempt'
ELSE '**** BAD PAY RULE ****' END AS PAYRULE,
0 AS AC_BADGENUM,
F.BIRTHDATE AS BIRTHDATE,
G.FIRST_NAME AS FIRST_NAME,
G.LAST_NAME AS LAST_NAME,
H.ADDRESS_TYPE AS ADDRESS_TYPE,
H.ADDRESS1 AS ADDRESS1,
H.CITY AS CITY,
H.STATE AS STATE,
H.POSTAL AS POSTAL,
H.COUNTRY AS COUNTRY,
I.PHONE_TYPE AS PHONE_TYPE,
--'HOME' AS PHONE_TYPE,
I.PHONE AS PHONE,
A.JOBCODE AS JOBCODE,
A.EFFDT AS EFFDT,
A.SHIFT AS SHIFT,
D.SERVICE_DT AS SERVICE_DT,
A.ACC_CLOCK_ID AS ACC_CLOCK_ID,
A.EMPLID,
CASE A.GRADE
WHEN '01' THEN 'Fabco Salaried Grade 1-15'
WHEN '02' THEN 'Fabco Salaried Grade 1-15'
WHEN '03' THEN 'Fabco Salaried Grade 1-15'
WHEN '04' THEN 'Fabco Salaried Grade 1-15'
WHEN '05' THEN 'Fabco Salaried Grade 1-15'
WHEN '06' THEN 'Fabco Salaried Grade 1-15'
WHEN '07' THEN 'Fabco Salaried Grade 1-15'
WHEN '08' THEN 'Fabco Salaried Grade 1-15'
WHEN '09' THEN 'Fabco Salaried Grade 1-15'
WHEN '10' THEN 'Fabco Salaried Grade 1-15'
WHEN '11' THEN 'Fabco Salaried Grade 1-15'
WHEN '12' THEN 'Fabco Salaried Grade 1-15'
WHEN '13' THEN 'Fabco Salaried Grade 1-15'
WHEN '14' THEN 'Fabco Salaried Grade 1-15'
WHEN '15' THEN 'Fabco Salaried Grade 1-15'
WHEN '16' THEN 'Fabco Salaried Grade 16-19'
WHEN '17' THEN 'Fabco Salaried Grade 16-19'
WHEN '18' THEN 'Fabco Salaried Grade 16-19'
WHEN '19' THEN 'Fabco Salaried Grade 16-19'
WHEN '20' THEN 'Fabco Salaried Grade 20+'
WHEN '21' THEN 'Fabco Salaried Grade 20+'
WHEN '22' THEN 'Fabco Salaried Grade 20+'
WHEN '23' THEN 'Fabco Salaried Grade 20+'
WHEN '24' THEN 'Fabco Salaried Grade 20+'
WHEN '25' THEN 'Fabco Salaried Grade 20+'
ELSE '**** BAD ACCRUAL PROFILE ****' END AS ACCRUAL_PROFILE,
'13404' AS TIMEZONE,
A.PAYGROUP AS PAYGROUP,
J.ACNETLOGON
FROM dbo.PS_JOB A INNER JOIN
dbo.PS_PERSON_NAME B ON A.EMPLID = B.EMPLID INNER JOIN
dbo.PS_EMPLOYMENT D ON A.EMPLID = D.EMPLID LEFT OUTER JOIN
dbo.PS_AC_KRONOS_INT E ON A.EMPLID = E.EMPLID INNER JOIN
dbo.PS_PERSON F ON A.EMPLID = F.EMPLID INNER JOIN
dbo.PS_NAMES G ON A.EMPLID = G.EMPLID INNER JOIN
dbo.PS_ADDRESSES H ON A.EMPLID = H.EMPLID LEFT OUTER JOIN
dbo.PS_PERSONAL_PHONE I ON A.EMPLID = I.EMPLID INNER JOIN
dbo.PS_ACNETWORK_LOGON J ON A.EMPLID = J.EMPLID
WHERE (A.EFFDT =
(SELECT MAX(A_ED.EFFDT)
FROM PS_JOB A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR, GETDATE()), 1, 11)))
AND (A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ)
FROM PS_JOB A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.EMPL_RCD = A_ES.EMPL_RCD
AND A.EFFDT = A_ES.EFFDT))
AND (G.EFFDT =
(SELECT MAX(G_ED.EFFDT)
FROM PS_NAMES G_ED
WHERE G.EMPLID = G_ED.EMPLID
AND G_ED.EFFDT <= SUBSTRING(CONVERT(CHAR, GETDATE()), 1, 11)))
AND (G.NAME_TYPE = 'PRI')
AND (H.EFFDT =
(SELECT MAX(H_ED.EFFDT)
FROM PS_ADDRESSES H_ED
WHERE H.EMPLID = H_ED.EMPLID
AND H_ED.EFFDT <= SUBSTRING(CONVERT(CHAR, GETDATE()), 1, 11)))
AND (H.ADDRESS_TYPE = 'HOME')
AND (I.PHONE_TYPE = 'HOME' or I.PHONE_TYPE IS NULL or I.PHONE_TYPE = 'CELL')
AND (A.COMPANY = '007')
AND A.PAYGROUP = '701'
AND A.EMPLID NOT IN ('04171','04172','04173','04175','04272','04299','04309','04395','04423','04445','04510')
ORDER BY A.EMPLID
February 5, 2008 at 7:47 am
COALESCE would help you in this case...it's really just a CASE WHEN construct:
SELECT COALESCE(HOMEPHONE,CELLPHONE,EMERGENCYPHONE,'911')
FROM SOMETABLE.
it returns the first non-null item...so if homephone is null, it returns the cellphone, but if that is null, it returns the next, etc.
you could improve your case statement a bit if you did something like this:
CASE
WHEN CASE A.GRADE IN('01','02','03','04','05','06','07','08','09','10','11','12','13','14','15')
THEN 'Fabco Salaried Grade 1-15'
WHEN CASE A.GRADE IN('16','17','18','19')
THEN 'Fabco Salaried Grade 16-19'
WHEN CASE A.GRADE IN('17','18','19','20','21','22','23','24','25')
THEN 'Fabco Salaried Grade 20+'
ELSE '**** BAD ACCRUAL PROFILE ****' END AS ACCRUAL_PROFILE,
Lowell
February 5, 2008 at 8:08 am
I don't think COALESCE will work in this case.
Try
SELECTA.EMPLID AS PERSON_NBR,
'005/116/' + LEFT(A.DEPTID, 5) + '/' + A.JOBCODE + '/NA/NA/NA' AS LABORACCOUNT,
0 AS HOURLY_RT,
'' AS SUPERVISOR_NAME,
A.EMPL_STATUS AS EMPL_STATUS,
D.SERVICE_DT AS HIRE_DT,
'' AS AC_DEVICEGRP,
CASE
WHEN A.GRADE BETWEEN '01' AND '10' THEN 'FABCO Salaried Non Exempt'
WHEN A.GRADE BETWEEN '11' AND '12' THEN 'FABCO Salaried Exempt Eligible'
WHEN A.GRADE BETWEEN '13' AND '25' THEN 'FABCO Salaried Exempt'
ELSE '**** BAD PAY RULE ****'
END AS PAYRULE,
0 AS AC_BADGENUM,
F.BIRTHDATE AS BIRTHDATE,
G.FIRST_NAME AS FIRST_NAME,
G.LAST_NAME AS LAST_NAME,
H.ADDRESS_TYPE AS ADDRESS_TYPE,
H.ADDRESS1 AS ADDRESS1,
H.CITY AS CITY,
H.STATE AS STATE,
H.POSTAL AS POSTAL,
H.COUNTRY AS COUNTRY,
I.PHONE_TYPE AS PHONE_TYPE,
I.PHONE AS PHONE,
A.JOBCODE AS JOBCODE,
A.EFFDT AS EFFDT,
A.SHIFT AS SHIFT,
D.SERVICE_DT AS SERVICE_DT,
A.ACC_CLOCK_ID AS ACC_CLOCK_ID,
A.EMPLID,
CASE
WHEN A.GRADE BETWEEN '01' AND '15' THEN 'Fabco Salaried Grade 1-15'
WHEN A.GRADE BETWEEN '16' AND '19' THEN 'Fabco Salaried Grade 16-19'
WHEN A.GRADE BETWEEN '20' AND '25' THEN 'Fabco Salaried Grade 20+'
ELSE '**** BAD ACCRUAL PROFILE ****'
END AS ACCRUAL_PROFILE,
'13404' AS TIMEZONE,
A.PAYGROUP AS PAYGROUP,
J.ACNETLOGON
FROMdbo.PS_JOB AS A
INNER JOINdbo.PS_PERSON_NAME B ON B.EMPLID = A.EMPLID
INNER JOINdbo.PS_EMPLOYMENT AS D ON D.EMPLID = A.EMPLID
LEFT JOINdbo.PS_AC_KRONOS_INT AS E ON E.EMPLID = A.EMPLID
INNER JOINdbo.PS_PERSON AS F ON F.EMPLID = A.EMPLID
INNER JOINdbo.PS_NAMES AS G ON G.EMPLID = A.EMPLID
INNER JOINdbo.PS_ADDRESSES AS H ON H.EMPLID = A.EMPLID
LEFT JOINdbo.PS_PERSONAL_PHONE AS I ON I.EMPLID = A.EMPLID
AND I.PHONE_TYPE = (SELECT TOP 1 x.PHONE_TYPE FROM dbo.PS_PERSONAL_PHONE AS x WHERE x.EMPLID = A.EMPLID ORDER BY CASE x.PHONE_TYPE WHEN 'HOME' THEN 0 WHEN 'CELL' THEN 1 END)
INNER JOINdbo.PS_ACNETWORK_LOGON AS J ON J.EMPLID = A.EMPLID
WHEREA.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_JOB AS A_ED WHERE A_ED.EMPLID = A.EMPLID AND A_ED.EMPL_RCD = A.EMPL_RCD AND A_ED.EFFDT <= CONVERT(CHAR(10), GETDATE(), 112))
AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB AS A_ES WHERE A_ES.EMPLID = A.EMPLID AND A_ES.EMPL_RCD = A.EMPL_RCD AND A_ES.EFFDT = A.EFFDT)
AND G.EFFDT = (SELECT MAX(G_ED.EFFDT) FROM PS_NAMES AS G_ED WHERE G_ED.EMPLID = G.EMPLID AND G_ED.EFFDT <= CONVERT(CHAR(10), GETDATE(), 112))
AND G.NAME_TYPE = 'PRI'
AND H.EFFDT = (SELECT MAX(H_ED.EFFDT) FROM PS_ADDRESSES AS H_ED WHERE H_ED.EMPLID = H.EMPLID AND H_ED.EFFDT <= CONVERT(CHAR(10), GETDATE(), 112))
AND H.ADDRESS_TYPE = 'HOME'
AND A.COMPANY = '007'
AND A.PAYGROUP = '701'
AND A.EMPLID NOT IN ('04171', '04172', '04173', '04175', '04272', '04299', '04309', '04395', '04423', '04445', '04510')
ORDER BYA.EMPLID
N 56°04'39.16"
E 12°55'05.25"
February 6, 2008 at 3:10 am
Coalesce will only work if the phone number columns are nullable and have nulls when the value is not there - otherwise if a record did exist for e.g. home but there was no phone number for home it would still return the empty string ... one way around this is to use NullIf as well - so in the earlier hint on using Coalesce
SELECT COALESCE(HOMEPHONE,CELLPHONE,EMERGENCYPHONE,'911')
FROM SOMETABLE.
becomes
SELECT COALESCE(NULLIF(HOMEPHONE,''),NULLIF(CELLPHONE,''),NULLIF(EMERGENCYPHONE,''),'911')
FROM SOMETABLE.
That way if any of the fields are empty '' then they are turned to Null to fall through to next otpion in the Coalesce - I am not sure how this performs compared to a CASE statement though - probably simialrly as the work required by the engine to evaluate is pretty much the same
James Horsley
Workflow Consulting Limited
February 6, 2008 at 3:43 am
Yes, but the phone table has both TYPE and PHONE columns, which makes me think the table is normalized liek this
TYPE PHONE
HOME 555-5687
CELL 444-45524
And then coalesce will not work when the JOIN fetches two records.
N 56°04'39.16"
E 12°55'05.25"
February 6, 2008 at 4:03 am
Ah .. didn't notice that .. your subselect makes more sense then (though again it might be necessary to exclude rows in subselect where the actual phone field was not filled but that depends on how the app populates this table (i.e. does it ever add a row for HOME number with empty contents for the actual number)
James Horsley
Workflow Consulting Limited
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply