Search code examples
sqloracle-databasedatenulloracle12c

Need help eliminating Null data and retrieving last months records

SELECT 
    CLIENTS.LAST_NAME, 
    CLIENTS.FIRST_NAME, 
    CLIENTS.DOB, 
    CLIENTS.ALT_ID,
    CLIENT_SCREENING_TOOLS.SCREEN_DATE,
    CASE 
       WHEN CLIENT_SCREENING_TOOLS.TEST_NAME = 'Patient Health Questionnaire (PHQ-9)' 
          THEN 'D - Depression Screen'
       WHEN CLIENT_SCREENING_TOOLS.TEST_NAME = 'Resident CAGE-AID Questionnaire' 
          THEN 'B - Substance Abuse Screen'
       ELSE 'NULL'
    END AS SERVICE
FROM
    CLIENTS
JOIN 
    CLIENT_SCREENING_TOOLS ON CLIENTS.SHISID = CLIENT_SCREENING_TOOLS.SHISID
WHERE  
    CLIENTS.LAST_NAME != 'TEST' 
    AND CLIENT_SCREENING_TOOLS.TEST_NAME != 'NULL'

I feel like I am pretty close on this one, but my where statement isn't working correctly. My service column is still populating the NULL data. The other part that I need help with is pulling just last months data. If you look at the image, the date format is 2020-03-14 17:38:00.

enter image description here

Let me know if have any tips on my formatting, this is new to me.

Current code: 

SELECT CLIENTS.LAST_NAME, 
   CLIENTS.FIRST_NAME, 
   CLIENTS.DOB, 
   CLIENTS.ALT_ID,
   CLIENT_SCREENING_TOOLS.SCREEN_DATE,
   CASE 
        WHEN CLIENT_SCREENING_TOOLS.TEST_NAME = 'Patient Health Questionnaire (PHQ-9)' THEN 'D - Depression Screen'
        WHEN CLIENT_SCREENING_TOOLS.TEST_NAME = 'Resident CAGE-AID Questionnaire' THEN 'B - Substance Abuse Screen'
        ELSE CLIENT_SCREENING_TOOLS.TEST_NAME
    END AS SERVICE
FROM CLIENTS
JOIN CLIENT_SCREENING_TOOLS
ON CLIENTS.SHISID = CLIENT_SCREENING_TOOLS.SHISID
WHERE CLIENTS.LAST_NAME != 'TEST' 
AND CLIENT_SCREENING_TOOLS.TEST_NAME IN
(
'Patient Health Questionnaire (PHQ-9)',
'Resident CAGE-AID Questionnaire'
)
AND SCREEN_DATE 
BETWEEN
 add_months(trunc(sysdate,'mm'),-1) 
AND last_day(add_months(trunc(sysdate,'mm'),-1))

This is before the last month code: enter image description here

This is after the code:

enter image description here

Solution

  • There are multiple requirements in your question, let me address one-by-one.

    CLIENT_SCREENING_TOOLS.TEST_NAME != 'NULL'

    This is incorrect, NULL is not a string to compare using != operator. The correct way is to use IS NOT NULL:

    WHERE CLIENTS.LAST_NAME != 'TEST' 
AND CLIENTS.LAST_NAME IS NOT NULL
AND CLIENT_SCREENING_TOOLS.TEST_NAME IS NOT NULL

    the date format is 2020-03-14 17:38:00

    DATE doesn't have any format. What you see is the way it is displayed as per your locale-specific NLS settings.

    You have coded your CASE expression to have NULL value as string in the ELSE part:

    CASE 
   WHEN CLIENT_SCREENING_TOOLS.TEST_NAME = 'Patient Health Questionnaire (PHQ-9)' 
      THEN 'D - Depression Screen'
   WHEN CLIENT_SCREENING_TOOLS.TEST_NAME = 'Resident CAGE-AID Questionnaire' 
      THEN 'B - Substance Abuse Screen'
   ELSE 'NULL' --> This is why you see NULL in your output
END AS SERVICE

    Whenever the THEN expression is not met, it will display 'NULL' as string.

    Instead, you could simply make your filter condition as:

    AND CLIENT_SCREENING_TOOLS.TEST_NAME IN
( 
  'Patient Health Questionnaire (PHQ-9)' , 
  'Resident CAGE-AID Questionnaire'
)

    The other part that I need help with is pulling just last months data

    To get LAST MONTH data, you could use below condition:

    AND SCREEN_DATE 
BETWEEN
    add_months(trunc(sysdate,'mm'),-1) 
AND last_day(add_months(trunc(sysdate,'mm'),-1))

    For example,

    alter session set nls_date_format = 'YYYY-MM-DD';
SELECT
    add_months(trunc(sysdate, 'mm'), - 1) "start",
    last_day(add_months(trunc(sysdate, 'mm'), - 1)) "end"
FROM
    dual;

start      end       
---------- ----------
2020-04-01 2020-04-30