MS SQL Server Query

Below query results all carrier rows (there is column with the name of carrier). Now we need only selected carriers result rows like
If 'A','B' selected then need to display only A, B carrier related rows not C,D,E,ect....
If 'C' selected then need to display only C carrier related rows not A,B,D,E,etc....
Please help me on this.
WITH AllPORows AS (
SELECT ROW_NUMBER() OVER (PARTITION BY PURCH_ORDER
ORDER BY PURCH_ORDER DESC) AS IDNUM, CR_NAME, PRODUCT_NAME, PURCH_ORDER, FTN, LASR_SENT, L_DDD, FOC_DATE, LFOC_Sent, COMPLETION_DATE, LOC_LATA, LOC_REGION, LOC_STATE, LOC_CITY, DATE_MM, VW_FOC__CKT_DEL, VW_DESR_DUE__FOC, VW_INST_COMPLT__WANT, VW_LEC_CDEL__INST_COMPLT
FROM TBL_PIE_CUST_TDM_WLN_NB_DS0
WHERE CR_NAME IN ('Verizon','Frontier') AND DATE_MM IN ('9') AND DATE_YYYY IN (2015) AND LOC_REGION IS NOT NULL AND LOC_LATA IS NOT NULL OR LOC_LATA = '' AND ACT_TYPE IS NOT NULL AND FOC_DATE IS NOT NULL OR FOC_DATE ='' AND COMPLETION_DATE IS NOT NULL OR COMPLETION_DATE ='' AND PURCH_ORDER IS NOT NULL OR PURCH_ORDER ='' UNION ALL
SELECT ROW_NUMBER() OVER (PARTITION BY PURCH_ORDER
ORDER BY PURCH_ORDER DESC) AS IDNUM, CR_NAME, PRODUCT_NAME, PURCH_ORDER, FTN, LASR_SENT, L_DDD, FOC_DATE, LFOC_Sent, COMPLETION_DATE, LOC_LATA, LOC_REGION, LOC_STATE, LOC_CITY, DATE_MM, VW_FOC__CKT_DEL, VW_DESR_DUE__FOC, VW_INST_COMPLT__WANT, VW_LEC_CDEL__INST_COMPLT
FROM TBL_PIE_CUST_TDM_WLN_NB_DS1
WHERE CR_NAME IN ('Verizon','Frontier') AND DATE_MM IN ('9') AND DATE_YYYY IN (2015) AND LOC_REGION IS NOT NULL AND LOC_LATA IS NOT NULL OR LOC_LATA = '' AND ACT_TYPE IS NOT NULL AND FOC_DATE IS NOT NULL OR FOC_DATE ='' AND COMPLETION_DATE IS NOT NULL OR COMPLETION_DATE ='' AND PURCH_ORDER IS NOT NULL OR PURCH_ORDER =''), AllPORowsWrapper AS (
SELECT ROW_NUMBER() OVER (
ORDER BY PURCH_ORDER DESC) AS ROWNUMBER, *
FROM AllPORows
WHERE IDNUM = '1')
SELECT *
FROM AllPORowsWrapper
WHERE ROWNUMBER BETWEEN 0 AND 200 

Open in new window

sql2012 DBAServerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GanapathiFacets DeveloperCommented:
I think the problem is the way your have written your WHERE clause. You haven't properly combined your AND and OR conditions. If any of the OR conditions is true, then the other AND conditions will be bypassed.

Check the highlighted conditions in attachment.Query
Vitor MontalvãoMSSQL Senior EngineerCommented:
You don't need the OR's statements since you're already filtering for IS NOT NULL and empty strings are NOT NULL, so try to run the following:
WITH AllPORows AS (
	SELECT ROW_NUMBER() OVER (PARTITION BY PURCH_ORDER ORDER BY PURCH_ORDER DESC) AS IDNUM, 
		CR_NAME, PRODUCT_NAME, PURCH_ORDER, FTN, LASR_SENT, L_DDD, FOC_DATE, LFOC_Sent, COMPLETION_DATE, LOC_LATA, LOC_REGION, LOC_STATE, LOC_CITY, DATE_MM, VW_FOC__CKT_DEL, VW_DESR_DUE__FOC, VW_INST_COMPLT__WANT, VW_LEC_CDEL__INST_COMPLT
	FROM TBL_PIE_CUST_TDM_WLN_NB_DS0
	WHERE CR_NAME IN ('Verizon','Frontier') AND DATE_MM IN ('9') AND DATE_YYYY IN (2015) AND LOC_REGION IS NOT NULL 
		AND LOC_LATA IS NOT NULL AND ACT_TYPE IS NOT NULL AND FOC_DATE IS NOT NULL AND COMPLETION_DATE IS NOT NULL AND PURCH_ORDER IS NOT NULL 
	UNION ALL
	SELECT ROW_NUMBER() OVER (PARTITION BY PURCH_ORDER ORDER BY PURCH_ORDER DESC) AS IDNUM, 
		CR_NAME, PRODUCT_NAME, PURCH_ORDER, FTN, LASR_SENT, L_DDD, FOC_DATE, LFOC_Sent, COMPLETION_DATE, LOC_LATA, LOC_REGION, LOC_STATE, LOC_CITY, DATE_MM, VW_FOC__CKT_DEL, VW_DESR_DUE__FOC, VW_INST_COMPLT__WANT, VW_LEC_CDEL__INST_COMPLT
	FROM TBL_PIE_CUST_TDM_WLN_NB_DS1
	WHERE CR_NAME IN ('Verizon','Frontier') AND DATE_MM IN ('9') AND DATE_YYYY IN (2015) AND LOC_REGION IS NOT NULL 
		AND LOC_LATA IS NOT NULL AND ACT_TYPE IS NOT NULL AND FOC_DATE IS NOT NULL AND COMPLETION_DATE IS NOT NULL AND PURCH_ORDER IS NOT NULL
	), 
AllPORowsWrapper AS (
	SELECT ROW_NUMBER() OVER (ORDER BY PURCH_ORDER DESC) AS ROWNUMBER, *
	FROM AllPORows
	WHERE IDNUM = '1')
SELECT *
FROM AllPORowsWrapper
WHERE ROWNUMBER <= 200

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sql2012 DBAServerAuthor Commented:
The base query is displaying correct results as per our requirement for all carriers.

Now I want to filter rows based on selected carrier(s) (as I mentioned above example) without changing existing logic.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post some sample data?
Examples of what you are getting now and what are you pretending to get.
sql2012 DBAServerAuthor Commented:
@Vitor: In the output result, there is column with the name of CR_NAME. The output of CR_NAME column is like:
CR_NAME
=========
A
A
D
B
C
C
C
C
B

The base query is displaying all columns data including CR_NAME. Now I want to display only selected CR_NAME data.
For ex:
If CR_NAME : A,B selected then need to display only CR_NAME: A, B related rows not C,D, (all columns data including CR_NAME)
If CR_NAME :C selected then need to display only CR_NAME: C related rows not A,B,D,E (all columns data including CR_NAME).

We can't use SP with input parameter because some time we are selecting 1 CR_NAME, some time 3 CR_NAME(s) and some time 2,4,6
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's why I asked for a sample because your query is filtering CR_NAME to only bring 'Verizon','Frontier':
WHERE CR_NAME IN ('Verizon','Frontier')

If you want only A and B then you should change the filter to: WHERE CR_NAME IN ('A','B')
ste5anSenior DeveloperCommented:
btw, why is date_mm a (VAR)CHAR column and not an INT as date_yyyy?
PortletPaulEE Topic AdvisorCommented:
You have quite a lot of OR conditions, and you NEED to use parentheses with those otherwise your query will produce bizarre results. I have said this at least 3 times so far (on different questions) but it is the same set of conditions I keep seeing.

Note the parentheses please. These are not put in to make it look pretty, they are there to help control the logic, they will help ensure that ONLY 'Verizon', 'Frontier' are returned.
      WHERE CR_NAME IN ('Verizon', 'Frontier')
            AND DATE_MM IN ('9')
            AND DATE_YYYY IN (2015)
            AND LOC_REGION IS NOT NULL
            AND ( LOC_LATA IS NOT NULL OR LOC_LATA = '' )
            AND ACT_TYPE IS NOT NULL
            AND ( FOC_DATE IS NOT NULL OR FOC_DATE = '' )
            AND ( COMPLETION_DATE IS NOT NULL OR COMPLETION_DATE = '' )
            AND ( PURCH_ORDER IS NOT NULL OR PURCH_ORDER = '' )

Open in new window

Vitor also keeps advising you do NOT actually NEED the OR conditions at all which is probably true, but if you insist on using those OR conditions please - this time - listen to the advice regarding parentheses. Please.
PortletPaulEE Topic AdvisorCommented:
WITH AllPORows
AS (
      SELECT
            ROW_NUMBER() OVER (PARTITION BY PURCH_ORDER
                                ORDER BY PURCH_ORDER DESC) AS IDNUM

It isn't logical to order by the same field that you partition by.
Any row within a purchase order could get a row_number of 1 that way.

It is more logical to order by some other field or fields

e.g.

WITH AllPORows
AS (
      SELECT
            ROW_NUMBER() OVER (PARTITION BY PURCH_ORDER
                               ORDER BY FOC_DATE DESC) AS IDNUM

So here a row with the MOST RECENT FOC_DATE would get the row_number of 1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.