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-06-26 : 13:25:53

I am working on a program that when you put in a team leaders name only the agents that are on his or her team show up in the results window. It works in the sql when you put a like command with variable but when you put in an at to be able to enter a name you get all the results.

please help I know it is something very simple:

i have a parameter set up:
for supervisor name with the qry:
SELECT DISTINCT LAST_NAME, FIRST_NAME
FROM [HR_STORE].[dbo].V_DIVISIONS
WHERE JOB_LEVEL = 'SUPERVISOR' AND ORG_HIERARCHY_LEVEL2_DESCR = 'Freedom Region'
ORDER BY LAST_NAME

the main qry to grab information is:

SELECT DISTINCT
dv.LAST_NAME ,
dv.FIRST_NAME ,
--id.FIRSTNAME ,
--id.LASTNAME ,
fb.MSOOPRID ,
fb.DEPT ,
up.PHONENBR ,
fb.OPR ,
dv.PERNR ,
dv.WORK_EMAIL ,
dv.SUPERVISOR_NAME ,
dv.JOB_DESCR ,
dv.WORK_LOCATION ,
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_LEVEL2_DESCR = 'Freedom Region'
AND FB.STAT = '1' --ACTIVE MSOs
AND DV.EMPL_STATUS IN ( '1', '3' )
--AND DV.SUPERVISOR_NAME IN(@NAME)
--ORDER BY DV.SUPERVISOR_NAME

help help help

none

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 13:29:46

AND DV.SUPERVISOR_NAME IN(@NAME)

should be written as

AND (',' + @NAME + ',' LIKE '%,' + DV.SUPERVISOR_NAME + ',%')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

maggie21620
Starting Member

27 Posts

Posted - 2013-06-26 : 14:07:54

Thank you for the help but i don't get any results at all when i ran it

none

maggie21620
Starting Member

27 Posts

Posted - 2013-06-26 : 14:12:03

I commented out that and got back all the results. what i want to do enter in the drop down box a team leaders name and get the agents that report to them

none

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-26 : 14:47:43

Do you get any result when you explicitly provide a static supervisor value to the query? e.g.

AND DV.SUPERVISOR_NAME IN('MIK_20008') -- pass a valid value that exist in V_DIVISIONS.SUPERVISOR_NAME column

Cheers
MIK

maggie21620
Starting Member

27 Posts

Posted - 2013-06-26 : 14:56:03

Yes i would get just the 10 or 11 agents that they have

none

maggie21620
Starting Member

27 Posts

Posted - 2013-06-26 : 14:58:19

If i put a like command in I got results

none

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-26 : 15:01:43

now use Visakh method and pass in the same value via parameter, check if it works or not?

Cheers
MIK

maggie21620
Starting Member

27 Posts

Posted - 2013-06-26 : 15:14:28

now use Visakh method and pass in the same value via parameter, check if it works or not?

Not sure what you are talking about here, sorry

none

maggie21620
Starting Member

27 Posts

Posted - 2013-06-26 : 15:49:41

now use Visakh method and pass in the same value via parameter, check if it works or not?

Not sure what you are talking about here, sorry

none

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-26 : 20:17:11

Try this:
[CODE]
SELECT DISTINCT
dv.LAST_NAME ,
dv.FIRST_NAME ,
--id.FIRSTNAME ,
--id.LASTNAME ,
fb.MSOOPRID ,
fb.DEPT ,
up.PHONENBR ,
fb.OPR ,
dv.PERNR ,
dv.WORK_EMAIL ,
dv.SUPERVISOR_NAME ,
dv.JOB_DESCR ,
dv.WORK_LOCATION ,
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_LEVEL2_DESCR = 'Freedom Region'
AND FB.STAT = '1' --ACTIVE MSOs
AND DV.EMPL_STATUS IN ( '1', '3' )
AND DV.SUPERVISOR_NAME LIKE '%'+@NAME+'%'
--ORDER BY DV.SUPERVISOR_NAME

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 00:47:36

One thing is not yet clear.
Will you be passing single value of group of values? Your first post indicated you pass multiple values but later posts sound as if it will be single value as per statement below
what i want to do enter in the drop down box a team leaders name

if its single value you pass the condition should be like

DV.SUPERVISOR_NAME = @NAME

if its multiple values it will obviously be delimited by , . In that case condition should be

',' + @NAME + ',' LIKE '%,' + DV.SUPERVISOR_NAME + ',%'

If parameter is optional you need to give it a default value (say NULL) and use condition like

AND (',' + @NAME + ',' LIKE '%,' + DV.SUPERVISOR_NAME + ',%' OR @NAME IS NULL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

maggie21620
Starting Member

27 Posts

Posted - 2013-06-27 : 10:32:19

MuMu88
Thank you that so worked. Yeah

none

maggie21620
Starting Member

27 Posts

Posted - 2013-06-27 : 13:21:27

mUmU88
that was awsome help now we want to be able to find information only by operator id (opr
I want to do a parameter that i enter the id and it brings back information about that one individual

none

maggie21620
Starting Member

27 Posts

Posted - 2013-06-27 : 16:12:01

I got it to run thanks for all the help.

none

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources