December 4, 2014 at 7:17 am
WITH C AS (
SELECT [TSR_REP_NAME] AS 'REP_NAME',* FROM [dbo].[QLOGIC_SPIFF_PAYOUT]
UNION
SELECT [EI_TSR_REP_NAME] AS 'REP_NAME',* FROM [dbo].[QLOGIC_SPIFF_PAYOUT]
)
SELECT DISTINCT REP_NAME FROM C
WHERE COUNTRY IN (@COUNTRY) AND SEGMENT IN (@SEGMENT) AND [TSR_MGR_NAME] in (@MGR_NAME) OR [EI_TSR_MGR_NAME] in (@MGR_NAME)
ORDER BY 1 ASC
This query suppose to only three records
It is pulling 5 records
Please find the table structure as below here we are combing [TSR_REP_NAME] and [EI_TSR_REP_NAME] as REP_NAME and [TSR_MGR_NAME] and [EI_TSR_MGR_NAME] as MGR_NAME.
Basically the query what I sent is that dataset what I am using in SSRS so that I can filter REP_NAME based on MGR_NAME
CREATE TABLE [dbo].[QLOGIC_SPIFF_PAYOUT](
[ACCT_ID] [decimal](15, 0) NULL,
[ACCT_NAME] [nvarchar](250) NULL,
[COUNTRY] [nvarchar](60) NULL,
[SEGMENT] [nvarchar](60) NULL,
[FISC_QTR_VAL] [nvarchar](10) NULL,
[FISC_WEEK_VAL] [nvarchar](10) NULL,
[CLDR_DATE] [smalldatetime] NULL,
[ITM_NBR] [nvarchar](30) NULL,
[ACCOUNT_TYPE] [nvarchar](6) NULL,
[SKU_TYPE] [nvarchar](8) NULL,
[ORDER_STATUS] [nvarchar](30) NULL,
[PAYOUT] [decimal](18, 4) NULL,
[SYS_QTY] [decimal](18, 4) NULL,
[BONUS_FLAG] [nvarchar](1) NULL,
[TSR_REP_NAME] [nvarchar](360) NULL,
[TSR_REP_BADGE_NUM] [nvarchar](30) NULL,
[TSR_MGR_NAME] [nvarchar](360) NULL,
[TSR_MGR_BADGE_NUM] [nvarchar](30) NULL,
[EI_TSR_REP_NAME] [nvarchar](360) NULL,
[EI_TSR_REP_BADGE_NUM] [nvarchar](30) NULL,
[EI_TSR_MGR_NAME] [nvarchar](360) NULL,
[EI_TSR_MGR_BADGE_NUM] [nvarchar](30) NULL
) ON [PRIMARY]
For MANAGER DATA SET I AM USING BELOW QUERY. It is Cascade based report
WITH D AS (
SELECT [TSR_MGR_NAME] AS 'MGR_NAME',* FROM [dbo].[QLOGIC_SPIFF_PAYOUT]
UNION
SELECT [EI_TSR_MGR_NAME] AS 'MGR_NAME',* FROM [dbo].[QLOGIC_SPIFF_PAYOUT]
)
SELECT DISTINCT MGR_NAME FROM D
WHERE COUNTRY IN (@COUNTRY) AND SEGMENT IN (@SEGMENT)
ORDER BY 1 ASC
December 4, 2014 at 7:21 am
Well is that query returning 5 distinct names?
December 4, 2014 at 7:30 am
Yes you are correct
December 4, 2014 at 7:34 am
help request ideally rep_name data set should return only three records. It is pulling 5 records. It is pulling 3 records as tsr_rep_name and records as
ei_tsr_rep_name and in the rep_name data set I am using tsr_mgr_name='some name' ideally it should pull tsr_rep_name because mgr_name is tsr_mgr_name
December 4, 2014 at 7:39 am
We'd need to see the data to see why it's returning more records than you want, but can you try including COUNTRY, SEGMENT, TSR_MGR_NAME and EI_TSR_MGR_NAME in the select?
Also do you want to put ( ) around the criteria against manager name?
WHERE COUNTRY IN (@COUNTRY) AND SEGMENT IN (@SEGMENT) AND ([TSR_MGR_NAME] in (@MGR_NAME) OR [EI_TSR_MGR_NAME] in (@MGR_NAME) )
December 5, 2014 at 2:58 am
Please script as below
CREATE TABLE #QLOGIC
(
[ACCT_ID] NVARCHAR(255),
[COUNTRY] NVARCHAR(255),
[SEGMENT] VARCHAR(255),
[TSR_REP_NAME] NVARCHAR(100),
[TSR_REP_BADGE_NUM] NVARCHAR(255),
[TSR_MGR_NAME] NVARCHAR(100),
[TSR_MGR_BADGE_NUM] NVARCHAR(255),
[EI_TSR_REP_NAME] NVARCHAR(100),
[EI_TSR_REP_BADGE_NUM] NVARCHAR(255),
[EI_TSR_MGR_NAME] NVARCHAR(100),
[EI_TSR_MGR_BADGE_NUM] NVARCHAR(255)
)
INSERT INTO #QLOGIC
VALUES('727386973','US','US LI CORP CHANNELS','Jennings, Phillip','606600','Betts, Brian','21589','Soto, Andrea A','589171','Okray, Lupe','598284')
INSERT INTO #QLOGIC
VALUES('725395506','US','HCLS','Dallmeier, Rick','7540','Betts, Brian','21589','Morales, Luris A.','596665','Okray, Lupe','598284')
INSERT INTO #QLOGIC
VALUES('727393937','US','US LI CORP CHANNELS','Dallmeier, Rick','7540','Betts, Brian','21589','Morales, Luris A.','596665','Okray, Lupe','598284')
INSERT INTO #QLOGIC
VALUES ('725398742','US','HCLS','McCarty, Wes','14605','Betts, Brian','21589','Gomez, Harold J','187450','Okray, Lupe','598284')
INSERT INTO #QLOGIC
VALUES ('727393356','US','US LI CORP CHANNELS','Carpenter, Scott','831449','Betts, Brian','21589','NULL','NULL','NULL','NULL')
INSERT INTO #QLOGIC
VALUES ('595724036','US','US LI ESL CHANNELS','Henderson, Chris R.','426529','Betts, Brian','21589','Soto, Andrea A','589171','Okray, Lupe','598284')
INSERT INTO #QLOGIC
VALUES('597544808','US','US LI CORP CHANNELS','Kacprzak, Marek','22042','Betts, Brian','21589','NULL','NULL','NULL','NULL')
INSERT INTO #QLOGIC
VALUES('3111846777','US LI ESL CHANNELS','US','Pharr, Brad','8077','Betts, Brian','21589','Morales, Luris A.','596665','Okray, Lupe','598284')
INSERT INTO #QLOGIC
VALUES('597547122','US','US LI CORP CHANNELS','Jennings, Phillip','606600','Betts, Brian','21589','Soto, Andrea A','589171','Okray, Lupe','598284')
INSERT INTO #QLOGIC
VALUES('597544817','US','US LI CORP CHANNELS','Kacprzak, Marek','22042','Betts, Brian','21589','NULL','NULL','NULL','NULL')
INSERT INTO #QLOGIC
VALUES('725397388','US','HCLS','Acheson, Nathan','577613','Betts, Brian','21589','Gomez, Harold J','187450','Okray, Lupe','598284')
INSERT INTO #QLOGIC
VALUES('597546341','US','US LI CORP CHANNELS','Garza, Luciano','42155','Betts, Brian','21589','Morales, Luris A.','596665','Okray, Lupe','598284')
INSERT INTO #QLOGIC
VALUES('727386973','US','US LI CORP CHANNELS','Jennings, Phillip','606600','Betts, Brian','21589','Soto, Andrea A','589171','Okray, Lupe','598284')
INSERT INTO #QLOGIC
VALUES('727393356','US','US LI CORP CHANNELS','Carpenter, Scott','831449','Betts, Brian','21589','NULL','NULL','NULL','NULL')
if I query
SELECT * FROM #QLOGIC
WHERE TSR_MGR_NAME='Betts, Brian' AND COUNTRY='US'AND SEGMENT='HCLS'
I get three records
DECLARE @MGR_NAME NVARCHAR(100)='Betts, Brian',@COUNTRY NVARCHAR(100)='US',@SEGMENT NVARCHAR(255)='HCLS'
;WITH C AS (
SELECT [TSR_REP_NAME] AS 'REP_NAME',* FROM #QLOGIC
UNION
SELECT [EI_TSR_REP_NAME] AS 'REP_NAME',* FROM #QLOGIC
)
SELECT DISTINCT REP_NAME FROM C
WHERE COUNTRY IN (@COUNTRY) AND SEGMENT IN (@SEGMENT) AND ([TSR_MGR_NAME] in (@MGR_NAME) OR [EI_TSR_MGR_NAME] in (@MGR_NAME) )
IF I query I get 5 rep_name with records,
Acheson, Nathan
Dallmeier, Rick
Gomez, Harold J
McCarty, Wes
Morales, Luris A.
Morales, Luris A. and Gomez, Harold J related to EI_TSR_MGR_NAME
My requirement is for [TSR_MGR_NAME] we have three records associated(REP_NAME) that should display
Expected result
Dallmeier, Rick
McCarty, Wes
Acheson, Nathan
December 5, 2014 at 6:14 am
If you run this you can see the cause, all I've done is added an S column to show which query is causing the issue.
DECLARE
@MGR_NAME NVARCHAR(100)='Betts, Brian'
,@COUNTRY NVARCHAR(100)='US'
,@SEGMENT NVARCHAR(255)='HCLS'
;WITH C AS (
SELECT 0 as s, [TSR_REP_NAME] AS 'REP_NAME',* FROM #QLOGIC
UNION
SELECT 1 as s, [EI_TSR_REP_NAME] AS 'REP_NAME',* FROM #QLOGIC
)
SELECT DISTINCT s, REP_NAME FROM C
WHERE COUNTRY = (@COUNTRY)
AND SEGMENT = (@SEGMENT)
AND
([TSR_MGR_NAME] = (@MGR_NAME)
OR [EI_TSR_MGR_NAME] in (@MGR_NAME) )
Its because you have move the EI_TSR_REP_NAME into the REP_NAME and the two additional names are coming from that part of the query, which is technically correct as the Manager of Gomes, and Morales are is Betts.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 5, 2014 at 7:27 am
I figured the problem. Now I have another problem that I need to figure
DECLARE@MGR_NAMENVARCHAR(100)='Okray, Lupe'
SELECT*FROM#QLOGIC
WHERECOUNTRYIN('US')ANDSEGMENTIN('HCLS')
AND[TSR_MGR_NAME]IN(@MGR_NAME)OR[EI_TSR_MGR_NAME]IN(@MGR_NAME)
It pulls all the rows related 'Okray, Lupe' ideally it should pull based on filters. It is pulling other segment data also. Please execute the query with the sample I have given
December 5, 2014 at 7:37 am
I told you yesterday you need to put the manager name parts in ( )
AND ([TSR_MGR_NAME] IN(@MGR_NAME) OR [EI_TSR_MGR_NAME] IN(@MGR_NAME))
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply