-1

i have build a query to set the partitions and used the row_number the issue here is i am missing out on certain person_id records but when i use a where condition i am able to fetch the record.

`SELECT C.* FROM
(SELECT
B.PROVIDER_PAU_DK AS PROVIDER_PAU_DK,
B.PERSON_ID AS PERSON_ID,
B.POS_BEGIN_DATE AS POS_BEGIN_DATE,  
B.POS_END_DATE AS POS_END_DATE,
ROW_NUMBER () OVER (PARTITION BY POS_END_DATE) AS ENDDATE
FROM
(SELECT
A.PROVIDER_PAU_DK AS PROVIDER_PAU_DK,
A.PERSON_ID AS PERSON_ID,
A.POS_BEGIN_DATE AS POS_BEGIN_DATE,  
A.POS_END_DATE AS POS_END_DATE
FROM
(SELECT  
PROVIDER_PAU_DK,
PERSON_ID,
ROW_NUMBER () OVER (PARTITION BY     PERSON_ID,POS_BEGIN_DATE,POS_END_DATE)    AS RN,
--ROW_NUMBER () OVER (PARTITION BY PERSON_ID,POS_END_DATE) AS RN1,
POS_BEGIN_DATE,  
POS_END_DATE
FROM CCR_EDTWH.DIM_PROVIDER_PAU
WHERE PERSON_ID in (16198964)
ORDER BY PERSON_ID)A
WHERE A.RN=1) B)C
WHERE C.ENDDATE IN (1,2) 
--and person_id=16198964`
Cœur
  • 37,241
  • 25
  • 195
  • 267
Sachin Balusu
  • 1
  • 3

1 Answers1

0

You partition over POS_END_DATE and then filter the result to include only rows with enddate 1 and 2. That means if your person_id happens to be number 3 for a particular field it will be filtered out. However, if you specifically filter for the record you are looking for, it will be the only one in the query and gets a smaller row number which make it show up.

For your testing I would remove the filter on the row_number columns to see what their value is.

Peter Schuetze
  • 16,185
  • 4
  • 44
  • 58