hELP - 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

hELP

Author

Topic

maggie21620
Starting Member

27 Posts

Posted - 2013-07-01 : 13:34:42

so i have a qry that runs thanks to help from the forum but when i put in another region i either get multiple results or none at all. My qry is:
SELECT DISTINCT
dv.LAST_NAME ,
dv.FIRST_NAME ,
--id.FIRSTNAME ,
--id.LASTNAME ,
fb.MSOOPRID ,
dv.LOGIN_ID,
fb.DEPT ,
up.PHONENBR ,
fb.OPR ,
dv.PERNR ,
dv.WORK_EMAIL ,
dv.SUPERVISOR_NAME ,
dv.JOB_DESCR ,
dv.WORK_LOCATION ,
dv.WORK_LOCATION_CITY,
dv.EMPL_STATUS ,
dv.EMPL_STATUS_DESCR ,
-- fb.SOURCE_UPDATE_DATE ,
dv.EMPLID ,
dv.ORIGINAL_HIRE_DT ,
dv.WEEKLY_HOURS ,
dv.AD_LAST_LOGON
FROM [freedomBilling].[dbo].[IDST_56_OPERATOR_ID] fb -- JOIN [INFODDP].[dbo].[IDST_SYS_OPERATOR_INFO] id ON id.MSOOPRID = fb.MSOOPRID
JOIN [HR_STORE].[dbo].[V_DIVISIONS] dv ON fb.MSOOPRID COLLATE Latin1_General_BIN = LEFT(dv.LOGIN_ID,
9)
LEFT JOIN spitfire.comtrac.userDataPernr up ON up.CLASSIFY = CAST(dv.PERNR AS VARCHAR(100))
JOIN freedom.dbo.corpList c ON c.corpNum = fb.ACCTCORP
WHERE fb.MSOOPRID <> '3RD%'
AND c.corpName = 'FREEDOM REGION'
--AND ID.DATASET = 'SYS1A'
--AND ORG_HIERARCHY_LEVEL1_DESCR LIKE 'NORTHEAST DIVISION%'
AND ORG_HIERARCHY_LEVEL2_DESCR LIKE 'FREEDOM REGION%'
--AND ORG_HIERARCHY_LEVEL2_DESCR = 'BELTWAY' --THIS IS THE OTHER REGION--
AND FB.STAT = '1' --ACTIVE MSOs
AND DV.EMPL_STATUS IN ( '1', '3' )
AND DV.SUPERVISOR_NAME LIKE ('%' + @NAME +'%')
--AND DV.SUPERVISOR_NAME IN (@NAME)
ORDER BY DV.SUPERVISOR_NAME

If I don't put in another region then i am fine but when i put in the region the team leader is in i get multiple results please help

none

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-01 : 14:06:10

You have a "DISTINCT" clause near the beginning of your query. What that does is remove any duplicates. However, the way SQL Server decides what is duplicate is based on all the columns in your select list. You have about 19 or 20 columns in your result set. If even one of those columns is different between two rows, then both rows will be returned.

So what you need to do to figure out how to eliminate the duplicates is to see what columns are different. Once you know that, you can either remove those columns in the select list (if you don't really need them) or use an aggregate function (such as MAX or MIN) to pick one out of those duplicates.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources