I am building a universe and trying to get a count of all my parnet records, but I am getting duplicate parnets because they have children. So I tried doing distinct but because the children are different for the same parnet it thinks that it is counting distinct records. If anyone has something I could put in the where condition or any other solutions that would be helpful. My SQL of the query with the count I am talking about looks like this:
SELECT DISTINCT
COUNT(PRXY_ACSS.Parnet_PAT_ID)
OVER (PARTITION BY (MYC.Parnet_STATUS_C)
ORDER BY (MYC.Parnet_STATUS_C)),
MYC2.NAME
FROM
MYC2
RIGHT OUTER JOIN MYC
ON (MYC.Parnet_STATUS_C=MYC2.NAME.STATUS_C)
INNER JOIN PRXY_ACSS.Parnet_PAT_ID
ON (PRXY_ACSS.Parnet_PAT_ID=CLARITY_PATIENT_MYC1.PAT_ID)
I would appreciate any help. Thanks!!
SELECT DISTINCT
COUNT(PRXY_ACSS.Parnet_PAT_ID)
OVER (PARTITION BY (MYC.Parnet_STATUS_C)
ORDER BY (MYC.Parnet_STATUS_C)),
MYC2.NAME
FROM
MYC2
RIGHT OUTER JOIN MYC
ON (MYC.Parnet_STATUS_C=MYC2.NAME.STATUS_C)
INNER JOIN PRXY_ACSS.Parnet_PAT_ID
ON (PRXY_ACSS.Parnet_PAT_ID=CLARITY_PATIENT_MYC1.PAT_ID)
I would appreciate any help. Thanks!!