SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maggie21620
Starting Member

USA
27 Posts

Posted - 06/26/2013 :  13:25:53  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 06/26/2013 :  13:29:46  Show Profile  Reply with Quote
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
Go to Top of Page

maggie21620
Starting Member

USA
27 Posts

Posted - 06/26/2013 :  14:07:54  Show Profile  Reply with Quote
Thank you for the help but i don't get any results at all when i ran it


none
Go to Top of Page

maggie21620
Starting Member

USA
27 Posts

Posted - 06/26/2013 :  14:12:03  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/26/2013 :  14:47:43  Show Profile  Reply with Quote
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
Go to Top of Page

maggie21620
Starting Member

USA
27 Posts

Posted - 06/26/2013 :  14:56:03  Show Profile  Reply with Quote
Yes i would get just the 10 or 11 agents that they have

none
Go to Top of Page

maggie21620
Starting Member

USA
27 Posts

Posted - 06/26/2013 :  14:58:19  Show Profile  Reply with Quote
If i put a like command in I got results


none
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/26/2013 :  15:01:43  Show Profile  Reply with Quote
now use Visakh method and pass in the same value via parameter, check if it works or not?

Cheers
MIK
Go to Top of Page

maggie21620
Starting Member

USA
27 Posts

Posted - 06/26/2013 :  15:14:28  Show Profile  Reply with Quote
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
Go to Top of Page

maggie21620
Starting Member

USA
27 Posts

Posted - 06/26/2013 :  15:49:41  Show Profile  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/26/2013 :  20:17:11  Show Profile  Reply with Quote
Try this:

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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/27/2013 :  00:47:36  Show Profile  Reply with Quote
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
Go to Top of Page

maggie21620
Starting Member

USA
27 Posts

Posted - 06/27/2013 :  10:32:19  Show Profile  Reply with Quote
MuMu88
Thank you that so worked. Yeah


none
Go to Top of Page

maggie21620
Starting Member

USA
27 Posts

Posted - 06/27/2013 :  13:21:27  Show Profile  Reply with Quote
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
Go to Top of Page

maggie21620
Starting Member

USA
27 Posts

Posted - 06/27/2013 :  16:12:01  Show Profile  Reply with Quote
I got it to run thanks for all the help.

none
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000