Pseudo Code Generator

SQL Query Logic for NPI_NUMBER Patient Counts

This pseudo code outlines the logic of an SQL query aimed at retrieving NPI_NUMBERs along with distinct counts of PATIENT_ID and SERVICE_DATE pairs, utilizing various filtered datasets and aggregate functions for a specified date range.

Prompt

SELECT NPI_NUMBER,COUNT(DISTINCT PATIENT_ID, SERVICE_DATE) AS COUNT_PAT FROM
(SELECT CHLD_BIRTH.*,B.NPI_NUMBER FROM
(
  SELECT * FROM ZURZV_COMMERCIAL_ANALYTICS_PROD.CONFORMED.LAAD_CHILD_BIRTH_EVENTS_FULL
  WHERE SERVICE_DATE BETWEEN '2023-07-01' AND '2024-06-30' AND PRIORITY_ORDER=1 
)CHLD_BIRTH
LEFT JOIN
(SELECT DISTINCT A.*,B.NPI_NUMBER FROM
(SELECT *,CASE WHEN PROVIDER_RENDERING_ID IN ('0','',' ') AND PROCEDURE_PROVIDER_ID NOT IN ('0','',' ') 
THEN PROCEDURE_PROVIDER_ID ELSE PROVIDER_RENDERING_ID END AS  PROVIDER_ID_UPDATED FROM
(SELECT A.*,B.PROCEDURE_PROVIDER_ID FROM
(SELECT DX_FCT.* FROM 
(
  SELECT * FROM ZURZV_COMMERCIAL_ANALYTICS_PROD.CONFORMED.DIAGNOSIS_FACT
  WHERE SERVICE_DATE BETWEEN '2023-07-01' AND '2024-06-30'
)DX_FCT
INNER JOIN
(
  SELECT  DISTINCT DIAGNOSIS_CD, REPLACE(DIAGNOSIS_CD,'.','') AS DIAGNOSIS_CD_UPD,  
  INDICATION_LVL1 AS INDICATION_LVL, ICD_VERSION_TYPE
    FROM ZURZV_COMMERCIAL_ANALYTICS_PROD.CONFORMED.SAGE_DX_CODES
  WHERE UPPER(INDICATION_LVL1)='BIRTH'
)IND
ON REPLACE(dx_fct.DIAGNOSIS_CD,'.','') = ind.DIAGNOSIS_CD_UPD)A
LEFT JOIN
(   SELECT DISTINCT CLAIM_ID, CAST(PROVIDER_RENDERING_ID AS VARCHAR) AS PROCEDURE_PROVIDER_ID
   FROM 
   (
   SELECT CLAIM_ID, MAX(PROVIDER_RENDERING_ID_NUMERIC) AS PROVIDER_RENDERING_ID
   FROM
   (
     SELECT DISTINCT PROVIDER_RENDERING_ID, 
     CLAIM_ID, CAST (PROVIDER_RENDERING_ID AS NUMERIC) AS PROVIDER_RENDERING_ID_NUMERIC
     FROM ZURZV_COMMERCIAL_ANALYTICS_PROD.CONFORMED.PROCEDURE_FACT
   )
   GROUP BY 1
     ))B
     ON
     A.CLAIM_ID=B.CLAIM_ID))A
 
Left join
(SELECT * FROM SCUDE_PROD.CONFORMED.CLAIMS_PROVIDER_INFO) B
ON A.PROVIDER_ID_UPDATED=B.PROVIDER_ID)B
ON CHLD_BIRTH.PATIENT_ID = B.PATIENT_ID
AND CHLD_BIRTH.SERVICE_DATE= B.SERVICE_DATE) GROUP BY ALL;

Answer

Pseudo Code for SQL Query Logic

The following pseudo code represents the logic of the provided SQL query. It structures the operations in a sequential and comprehensible manner.

Main Objective

Retrieve the NPI_NUMBER and count of distinct PATIENT_ID and SERVICE_DATE pairs from a set of joined tables, with specific conditions on service dates and priorities.

Pseudo Code Structure

Define Main Function

Function: GetPatientCounts
- Inputs: None
- Outputs: List of NPI_NUMBERs and COUNT_PAT

Step 1: Filter Child Birth Events

Let CHLD_BIRTH be the result of:
- Query LAAD_CHILD_BIRTH_EVENTS_FULL
  - Where: SERVICE_DATE between '2023-07-01' and '2024-06-30'
  - And: PRIORITY_ORDER equals 1

Step 2: Prepare Diagnosis Fact

Let DX_FCT be the result of:
- Query DIAGNOSIS_FACT
  - Where: SERVICE_DATE between '2023-07-01' and '2024-06-30'
- Join with:
  - Query SAGE_DX_CODES
    - Select: DISTINCT DIAGNOSIS_CD, REPLACE(DIAGNOSIS_CD,'.','') AS DIAGNOSIS_CD_UPD, INDICATION_LVL1 AS INDICATION_LVL, ICD_VERSION_TYPE
    - Where: UPPER(INDICATION_LVL1) equals 'BIRTH'
- Join Condition: WHERE REPLACE(DX_FCT.DIAGNOSIS_CD, '.', '') equals IND.DIAGNOSIS_CD_UPD

Step 3: Prepare Provider Facts

Let PROC_FACT_RESULT be the result of:
- Query from PROCEDURE_FACT
  - Select: DISTINCT PROVIDER_RENDERING_ID, CLAIM_ID, CAST(PROVIDER_RENDERING_ID AS NUMERIC) AS PROVIDER_RENDERING_ID_NUMERIC
- Group by: CLAIM_ID
  - Aggregate: MAX(PROVIDER_RENDERING_ID_NUMERIC)
Let B be the result of:
- Join DX_FCT with PROC_FACT_RESULT
  - Join Condition: A.CLAIM_ID equals B.CLAIM_ID
Let B_WITH_NPI be the result of:
- Select Distinct: A.*, B.NPI_NUMBER from B
- Set PROVIDER_ID_UPDATED:
  - IF PROVIDER_RENDERING_ID in ('0', '', ' ') AND PROCEDURE_PROVIDER_ID NOT IN ('0', '', ' ')
  - THEN PROVIDER_ID_UPDATED = PROCEDURE_PROVIDER_ID
  - ELSE PROVIDER_ID_UPDATED = PROVIDER_RENDERING_ID

Step 4: Final Join with Child Birth

Let FINAL_RESULT be the result of:
- Join CHLD_BIRTH with B_WITH_NPI
  - Join Condition: CHLD_BIRTH.PATIENT_ID equals B.PATIENT_ID AND CHLD_BIRTH.SERVICE_DATE equals B.SERVICE_DATE

Step 5: Aggregate Results

SELECT: NPI_NUMBER, COUNT(DISTINCT PATIENT_ID, SERVICE_DATE) AS COUNT_PAT FROM FINAL_RESULT
GROUP BY: NPI_NUMBER

Summary

The pseudo code initiates a series of filtered queries to extract necessary data from multiple sources.
Intermediate tables are constructed to handle specific logic like service date filtering, diagnoses, and provider information updates.
Finally, the results are aggregated to produce the desired output in a clear and structured format.

This structured approach to writing pseudo code provides clarity on the flow of data operations and the logic underlying the original SQL statement.