We help IT Professionals succeed at work.
Get Started

MS SQL Server Query

119 Views
Last Modified: 2021-04-21
Hi All,

Below query resulting duplicate records with same [PURCH_ORDER]. We need rows having unique [PURCH_ORDER] with all the condition given.

Please advise how to display unique [PURCH_ORDER] with all the condition given.

Thanks in advance.
with AllPORows as 
(select ROW_NUMBER() OVER (order by PURCH_ORDER DESC) as ROWNUMBER, 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 ('AT&T','CenturyLink') 
AND DATE_MM in ('8') 
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 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 (order by PURCH_ORDER DESC) as ROWNUMBER, 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 ('AT&T','CenturyLink') 
AND DATE_MM in ('8') 
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 OR FOC_DATE ='' 
AND COMPLETION_DATE IS NOT NULL OR COMPLETION_DATE ='' 
AND PURCH_ORDER IS NOT NULL OR PURCH_ORDER ='' ) 
select * from AllPORows where ROWNUMBER between 0 and 100 

Open in new window

Comment
Watch Question
Senior DBA
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 4 Answers and 14 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE