Nested SELECT and CASE statements within same table

Question

Long story short, I was handed this query (edited a bit for the forum world) to get it working in order to start creating reports. Having some issues with the 3 CASE statements (tried to add comment within the code to explain a little better what is happening). All data is being pulled from the same table/view.

I know I need to create some nested SELECT statements in order to make it work, but I'm struggling. What I'm trying to have happen is...

1. CASE 2 to only run if CASE 1 IS NOT NULL, where the column would be a result of (amd_month + 1 month). Format being YYYYMM

2. CASE 3 to only run if CASE 2 IS NOT NULL, where the column would be a result of (ext_srg_start_date + 6 months). Format being YYYYMM

   SELECT
   part,
   amd_month,
   amd   actual_demand,
   round(roll_12amd * 1.15) srg_lmt,
   round(roll_12amd * 1.5) ext_srg_lmt,
   -- CASE: 1 
   -- calculate level- COMPLETE
   CASE
   WHEN (amd > roll_12amd * 1.5) THEN 'Extreme Surge'
   WHEN (amd > roll_12amd * 1.15) THEN 'Surge'
   ELSE NULL
   END AS srg_nt,
   -- CASE: 2
   -- if amd_month = 12, (+1 year to) and (-11 from month) from ext_srg_start_date, 
   -- else +1 month to ext_srg_start_date - COMPLETE
   CASE
   WHEN substr(amd_month, 5, 2) IN (
       '12'
   ) THEN concat(TO_CHAR(substr(amd_month, 1, 4) + 1), lpad(TO_CHAR(substr(amd_month, 5, 2) - 11), 2, '0'))
   ELSE concat(substr(amd_month, 1, 4), lpad(TO_CHAR(substr(amd_month, 5, 2) + 1), 2, '0'))
   END AS ext_srg_start_date,
   ---- CASE: 3
   ---- +6 to ext_srg_start_date - IN PROGRESS
   CASE
   WHEN substr(amd_month, 5, 2) IN (
       '01',
       '02',
       '03',
       '04',
       '05',
       '06'
   ) THEN concat(substr(amd_month, 1, 4), lpad(TO_CHAR(substr(amd_month, 5, 2) + 6), 2, '0'))
   ELSE concat(TO_CHAR(substr(amd_month, 1, 4) + 1), lpad(TO_CHAR(substr(amd_month, 5, 2) - 6), 2, '0'))
   END AS ext_srg_carry_date
   FROM
   connect.table
   

Any help would be awesome. Thanks!

Answer 1

You can do away with case statements 2 and 3 if AMD_Month is a date data type, you can also do away with them if it's not but it takes an extra step:

If AMD_Month is a Date column then ext_srg_start_date and ext_srg_carry_date could be defined as:

...
, AMD_Month + INTERVAL '1' MONTH ext_srg_start_date
, AMD_Month + INTERVAL '6' MONTH ext_srg_carry_date
...

If AMD_Month is a string, then you first need to convert it to a date and I guess then convert the result back to a string:

...
, TO_CHAR( TO_DATE( AMD_Month, 'YYYYMM')
         + INTERVAL '1' MONTH, 'YYYYMM') ext_srg_start_date
, TO_CHAR( TO_DATE( AMD_Month, 'YYYYMM')
         + INTERVAL '6' MONTH, 'YYYYMM') ext_srg_carry_date
...

Date arithmatic works so much better when you store date data types as dates or timestamps than when you store them as strings.