SQL limit data set

Question

I have a dataset that is updated daily by a query. The query first pulls 11 different dates from the DB based on a combination of month end time frames(each month end that is not a stated time frame) and stated time frames (Current Month End, Prior Month End, Current Quarter End, Prior Quarter end, etc).

The issue I am running into is that in January/April/July/October & February/May/August/November - we are seeing duplicate dates flow into the query. I.E., in the middle of January, the current month-end would be December and the current quarter end would be December. Or in the middle of February, the current 'Prior Month End' and 'Current Quarter End' would both be December.

This is causing aggregation issues downstream in Tableau.

The Date table that is created looks like:

Period    Period_Selection
12/31/18  Current Quarter End
12/31/18  Prior Month End
1/31/19   Current Month End
9/30/18   Prior Quarter End

What I would like to do is write a case statement or where clause that limits the data table to a singular instance of each date. These cases will be specific, so it will either affect the pairs: 'current month end' & 'current quarter end' or 'prior month end' and 'current quarter end'. in the above example, the clause would see that there are two examples of '12/31/18' and then drop/ignore one of the instances, for example, drop 'prior month end' and keep 'current quarter end'.

SQL Sample:

SELECT PERIOD_DATE, PERIOD_SELECTION
FROM db.PERIOD_PROMPT
WHERE db_ORDER_NO IN ( '60000000', '50000000', '40000000')-- 50.. = Current Quarter end, 60.. = current month end, 40.. = prior month end

UNION ALL

SELECT ADD_MONTHS(PERIOD_DT, -3) AS PERIOD_DATE
, 'Prior Quarter End' AS PERIOD_SELECTION
FROM db.PERIOD_PROMPT 
WHERE db_ORDER_NO IN (50000000) --CURRENT QUARTER END

Please edit your question and include the query you're running and sample data for the tables. Also - don't know what your query does, but would `DISTINCT` help in this case? Thanks. — Bob Jarvis - Слава Україні, Mar 27 '19 at 15:12

score 0 · Answer 1 · answered Mar 27 '19 at 15:52

If you want one row for each PERIOD_DATE, then you probably want to GROUP BY PERIOD_DATE. If you don't care which PERIOD_SELECTION is chosen, then you can just pick an aggregate function to choose for you. Here, I'm using MAX to pick the PERIOD_SELECTION which comes last alphabetically ('Prior Month End', in this case).

-- sample data
with period_prompt as (select to_date('12/31/18', 'mm/dd/yy') as period_dt, 'Current Quarter End' as period_selection, 50000000 as db_order_no from dual
                       union all 
                       select to_date('12/31/18', 'mm/dd/yy') as period_dt, 'Prior Month End' as period_selection, 40000000 as db_order_no from dual)
select period_date, 
    max(period_selection) as period_selection
from ( -- your query
    SELECT PERIOD_DT as PERIOD_DATE, PERIOD_SELECTION
    FROM PERIOD_PROMPT
    WHERE db_ORDER_NO IN ( '60000000', '50000000', '40000000')-- 50.. = Current Quarter end, 60.. = current month end, 40.. = prior month end
    UNION ALL
    SELECT ADD_MONTHS(PERIOD_DT, -3) AS PERIOD_DATE
    , 'Prior Quarter End' AS PERIOD_SELECTION
    FROM PERIOD_PROMPT 
    WHERE db_ORDER_NO IN (50000000) --CURRENT QUARTER END
    )
group by period_date;

But if you have opinions about which specific PERIOD_SELECTION should be displayed, you'll need to order them - come up with a sorting algorithm. Sometimes there's a natural choice - like maybe you want the PERIOD_SELECTION with the highest DB_ORDER_NO. But in this case, I'm not sure you have a good column for sorting by, so we'll create one.

In this example, I'm adding a PRIORITY column, and setting it up so that a db_order_no of 50000000 (current quarter end) has a priority of 2, and everything else has a 1. Then we call MAX(period_selection), but specify that we want the period_selection entry which has the highest priority.

with period_prompt as (select to_date('12/31/18', 'mm/dd/yy') as period_dt, 'Current Quarter End' as period_selection, 50000000 as db_order_no from dual
                       union all 
                       select to_date('12/31/18', 'mm/dd/yy') as period_dt, 'Prior Month End' as period_selection, 40000000 as db_order_no from dual)
select period_date, 
    max(period_selection) keep (dense_rank first order by priority desc) as period_selection
from (
    SELECT PERIOD_DT PERIOD_DATE, PERIOD_SELECTION, 
        case db_order_no
            when 50000000 then 2
            else 1
        end as priority
    FROM PERIOD_PROMPT
    WHERE db_ORDER_NO IN ( '60000000', '50000000', '40000000')-- 50.. = Current Quarter end, 60.. = current month end, 40.. = prior month end
    UNION ALL
    SELECT ADD_MONTHS(PERIOD_DT, -3) AS PERIOD_DATE
    , 'Prior Quarter End' AS PERIOD_SELECTION
    , 1 as priority
    FROM PERIOD_PROMPT 
    WHERE db_ORDER_NO IN (50000000) --CURRENT QUARTER END
    )
group by period_date;

Output:

PERIOD_DATE PERIOD_SELECTION   
----------- -------------------
30-SEP-18   Prior Quarter End  
31-DEC-18   Current Quarter End

thank you for your help! Will your architecture still work when the dates do not equal each other? for example in a few weeks after March accounting close, Current month end, prior month end and current quarter end will all be different and i want to capture everything that is distinct/unique. So this issue where a 'MAX(date)' will work 2/3's of the time, but 1/3 of the time i will need all dates — John, Mar 30 '19 at 11:27
I'm not sure what you mean... if rows have the same date, then `group by period_date` will combine them. If you want to combine rows with different dates, then you'll need to transform the dates so they ARE the same, something like `group by trunc(period_date, 'MONTH')` — kfinity, Apr 01 '19 at 13:01
ok i see what you are saying now, that makes sense! quick follow up question, i have not used the with clause that you are referencing above- what is the utility of using this? thanks again for your help! — John, Apr 01 '19 at 20:44
The `with` clause is known as Common Table Expression (CTE). Here, I'm just using it to simulate some test data without having to create a table, insert into it, etc. But CTEs have many uses, see https://stackoverflow.com/questions/4740748/when-to-use-common-table-expression-cte — kfinity, Apr 02 '19 at 12:39

SQL limit data set

1 Answers1