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

chiruinfo5262
New Contributor II

‎05-26-2025 08:04 AM

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

 

 

 

 

 

 

Labels:
0 Kudos