Trying to convert oracle sql to databricks sql but not getting the desired output

[chiruinfo5262]

ORACLE SQL:

COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN SELECTED_PERIOD_START_DATE AND SELECTED_PERIOD_END_DATE THEN 1 END ) SELECTED_PERIOD_BM,
COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN COMPARISON_PERIOD_START_DATE AND COMPARISON_PERIOD_END_DATE THEN 1 END ) COMPARISON_PERIOD_BM,
COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN COMPARISON_PERIOD_START_DATE AND COMPARISON_PERIOD_END_DATE THEN 1 END )/5 YEARLY_BM,
CASE WHEN COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN SELECTED_PERIOD_START_DATE AND SELECTED_PERIOD_END_DATE THEN 1 END )>0 THEN 1 ELSE 0 END NO_OF_FAILED_ASSETS_SELECTED_PERIOD,
CASE WHEN COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN SELECTED_PERIOD_START_DATE AND SELECTED_PERIOD_END_DATE THEN 1 END )>0 AND COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN COMPARISON_PERIOD_START_DATE AND COMPARISON_PERIOD_END_DATE THEN 1 END )>0 THEN 1 ELSE 0 END NO_OF_REPEATEDLY_FAILED_ASSETS_SELECTED_PERIOD
, 'NONLINEAR' as classType,
COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN SELECTED_PERIOD_START_DATE_3months AND SELECTED_PERIOD_END_DATE THEN 1 END ) Last_3_months_BM,
COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN SELECTED_PERIOD_START_DATE_6months AND SELECTED_PERIOD_END_DATE THEN 1 END ) Last_6_months_BM
FROM
(SELECT
ASSETLIST.ASSETNUM,
MONTHS.M ORDER_ID,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-MONTHS.M-12)+1) SELECTED_PERIOD_START_DATE,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-MONTHS.M)) SELECTED_PERIOD_END_DATE,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-MONTHS.M-72)+1) COMPARISON_PERIOD_START_DATE,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-MONTHS.M-12)) COMPARISON_PERIOD_END_DATE,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-MONTHS.M-3)+1) SELECTED_PERIOD_START_DATE_3months,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-MONTHS.M-6)+1) SELECTED_PERIOD_START_DATE_6months

Databricks SQL

COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN SELECTED_PERIOD_START_DATE AND SELECTED_PERIOD_END_DATE THEN 1 END) AS SELECTED_PERIOD_BM,

    COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN COMPARISON_PERIOD_START_DATE AND COMPARISON_PERIOD_END_DATE THEN 1 END) AS COMPARISON_PERIOD_BM,

    COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN COMPARISON_PERIOD_START_DATE AND COMPARISON_PERIOD_END_DATE THEN 1 END) / 5 AS YEARLY_BM,

    CASE 

        WHEN COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN SELECTED_PERIOD_START_DATE AND SELECTED_PERIOD_END_DATE THEN 1 END) > 0 

        THEN 1 ELSE 0 

    END AS NO_OF_FAILED_ASSETS_SELECTED_PERIOD,

    CASE 

        WHEN COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN SELECTED_PERIOD_START_DATE AND SELECTED_PERIOD_END_DATE THEN 1 END) > 0 

        AND COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN COMPARISON_PERIOD_START_DATE AND COMPARISON_PERIOD_END_DATE THEN 1 END) > 0 

        THEN 1 ELSE 0 

    END AS NO_OF_REPEATEDLY_FAILED_ASSETS_SELECTED_PERIOD,

    'NONLINEAR' AS classType,

    COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN SELECTED_PERIOD_START_DATE_3months AND SELECTED_PERIOD_END_DATE THEN 1 END) AS Last_3_months_BM,

    COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN SELECTED_PERIOD_START_DATE_6months AND SELECTED_PERIOD_END_DATE THEN 1 END) AS Last_6_months_BM

    FROM

      (

        select ASSETLIST.asset,

    MONTHS.M AS ORDER_ID,

DATE_ADD(ADD_MONTHS(LAST_DAY(DATE_FORMAT(CAST(CURRENT_DATE AS DATE), 'yyyy-MM-dd')), -MONTHS.M - 12),1) AS SELECTED_PERIOD_START_DATE,

  ADD_MONTHS(LAST_DAY(DATE_FORMAT(CAST(CURRENT_DATE AS DATE), 'yyyy-MM-dd')), -MONTHS.M) AS SELECTED_PERIOD_END_DATE,

  DATE_ADD(ADD_MONTHS(LAST_DAY(DATE_FORMAT(CAST(CURRENT_DATE AS DATE),'yyyy-MM-dd')), -MONTHS.M - 72),1) AS COMPARISON_PERIOD_START_DATE,

  ADD_MONTHS(LAST_DAY(DATE_FORMAT(CAST(CURRENT_DATE AS DATE),'yyyy-MM-dd')), -MONTHS.M - 12) AS COMPARISON_PERIOD_END_DATE,

  DATE_ADD(ADD_MONTHS(LAST_DAY(DATE_FORMAT(CAST(CURRENT_DATE AS DATE),'yyyy-MM-dd')), -MONTHS.M - 3),1)  AS SELECTED_PERIOD_START_DATE_3months,

  DATE_ADD(ADD_MONTHS(LAST_DAY(DATE_FORMAT(CAST(CURRENT_DATE AS DATE), 'yyyy-MM-dd')),-MONTHS.M - 6),1)  AS SELECTED_PERIOD_START_DATE_6months

[nikhilj0421]

Hi @chiruinfo5262 , what's the difference you are observing in the output? 

Could you please elaborate more on it?

[chiruinfo5262]

Hi @nikhilj0421 

There is a difference of 18000 records in the output between oracle code output and databricks code output, not sure why

[nikhilj0421]

Please review this for the reference: https://www.databricks.com/blog/how-migrate-your-oracle-plsql-code-databricks-lakehouse-platform

[Granty]

This is a helpful comparison! I've definitely run into similar date formatting issues when migrating queries. The Oracle TRUNC function and Databricks' DATE_FORMAT/CAST combo can be tricky to reconcile. Speaking of needing a break after debugging SQL, anyone ever unwind with a quick game? I find Snow Rider 3D oddly relaxing after staring at code all day.