Link to home
Start Free TrialLog in
Avatar of MSSQL DBA
MSSQL DBA

asked on

MS SQL Server Query

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

Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

union all allows duplicate. Change it to union. But, be aware that union is slower than union all.

Also, have ROW_NUMBER() outside your first cte.

;with cte1
(Select
.
.
Union
.
.
),
cte2
( Select Row_Number... From cte1)
Select * From cte2
where ROWNUMBER between 0 and 100

;with AllPORows as 
(select 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 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 ='' ), 
AllPORows2 AS(
select ROW_NUMBER() OVER (order by PURCH_ORDER DESC) as ROWNUMBER, * from AllPORows 
)
Select  * from AllPORows2 where ROWNUMBER between 0 and 100 

Open in new window

Avatar of MSSQL DBA
MSSQL DBA

ASKER

Thanks Mike.

I have modified below query as per your suggestion, Can you pls review below query once again.
;with AllPORows as 
(select 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  
select 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 ='' ), 
AllPORows2 AS(
select ROW_NUMBER() OVER (order by PURCH_ORDER DESC) as ROWNUMBER, * from AllPORows 
)
Select  * from AllPORows2 where ROWNUMBER between 0 and 100 

Open in new window

It look good to me. All I can do now is cross my fingers and wait for you to test it.

You may need to add

Order By ROWNUMBER

at the very end
SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
MSSQL DBA, do you still need help with this question?
Hi Mike,Paul & Vitor,

Yes, I need your more help on below query. The below query (without union) is still displaying duplicate rows. Please suggest how to modify below query to eliminate duplicate purch_order.
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_ETHERNET_WLN
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


Ex:
1. Row 3 & 4 duplicate PURCH_ORDER coming we need only row 3.
2. same for row 5 & 6...row 5 & 6 duplicate PURCH_ORDER coming we need only row 5.
I am herewith attaching sample output of above query for your reference.

Just an thought: for each child select clause, do we need to add rowid number?? In this case,row 3 would give id 1 & row 4 would give id 2. we shal only consider rowid = 1

Please advise with query.
Thanks in advance.
Book2.xlsx
Tried with below query, still no luck..its still displaying duplicate purch order
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_ETHERNET_WLN
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 order by ROWNUMBER

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Vitor,  Yes you are correct.
Row 3 & 4 are same purch order and I want to display only row 3 (first row id) in my report. Can you please give me logic/query for this scenario?

Similarly row 5 & 6 and I want to display row 5 in my report (first row id).

Appreciate your help/or suggestion.
Supression of values should be done in the reporting tool, not SQL.

Are you using SSRS?
Try this:
WITH CTE_GroupRows AS (
	SELECT CR_NAME, PRODUCT_NAME, PURCH_ORDER, FTN, LASR_SENT, L_DDD, 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, MIN(FOC_DATE) AS MIN_FOC_DATE
	FROM TBL_PIE_CUST_ETHERNET_WLN
	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 
		AND COMPLETION_DATE IS NOT NULL 
		AND PURCH_ORDER IS NOT NULL 
	GROUP BY CR_NAME, PRODUCT_NAME, PURCH_ORDER, FTN, LASR_SENT, L_DDD, 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
	),
AllPORows AS (
	SELECT ROW_NUMBER() OVER (ORDER BY PURCH_ORDER DESC) AS ROWNUMBER, 
		CR_NAME, PRODUCT_NAME, PURCH_ORDER, FTN, LASR_SENT, L_DDD, MIN_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 CTE_GroupRows
SELECT *
FROM AllPORows
WHERE ROWNUMBER BETWEEN 0 AND 100 order by ROWNUMBER

Open in new window

NOTE: I removed the OR's since those criteria were redundant because empty string ('') is already NOT NULL so already covered by the IS NOT NULL criteria.
User generated imageThe repetition of PURCH_ORDER (yellow highlight) is NOT duplication! and the rows ARE "distinct"

Notice the blue highlights, these are some of the differences in the rows

This is how SQL behaves. It WILL repeat the PURCH_ORDER in such circumstances and it is correct behaviour for SQL.

PLEASE inform us about how you are preparing the actual report. Most reporting tools have their own methods for suppressing the output of certain information. It is way more efficient to do it in the reporting tool than attempting to do it in SQL.

For example, of you are preparing the "report" as an HTML page using PHP, then "suppress" the output of PURCH_ORDER when not wanted using PHP.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial