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
;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
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
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
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
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.
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
Open in new window