1 2 Previous Next 20 Replies Latest reply on Oct 20, 2017 9:42 PM by mathguy

    Date range

    user13328581

      Hello Experts;

       

      Please feel free to critic the design but I have the following sample data below

       

      with t (id, start_date, end_date) as

      (

      select 1, to_date('01/01/2017 17:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/01/2017 22:00:00', 'MM/DD/YYYY HH24:MI:SS') from dual

      union all

      select 2, to_date('01/02/2017 08:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/02/2017 16:00:00', 'MM/DD/YYYY HH24:MI:SS') from dual

      union all

      select 3, to_date('01/03/2017 12:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/03/2017 20:00:00', 'MM/DD/YYYY HH24:MI:SS') from dual

      union all

      select 4, to_date('03/01/2017 10:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('03/01/2017 18:00:00', 'MM/DD/YYYY HH24:MI:SS') from dual

      union all

      select 5, to_date('03/01/2017 11:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('03/01/2017 19:00:00', 'MM/DD/YYYY HH24:MI:SS') from dual

      union all

      select 6, to_date('03/02/2017 17:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('03/02/2017 22:00:00', 'MM/DD/YYYY HH24:MI:SS') from dual

      )

       

      Expected output if the date given as input was 01/01/2017

      Sun - 01/01/2017   Mon - 01/02/2017 Tues - 01/03/2017 Wed - 01/04/2017 Thurs - 01/05/2017 Fri - 01/05/2017 Sat - 01/05/2017

                 1                2                 3

              

      Expected output if the date given as input was 02/26/2017   

      Sun - 02/26/2017   Mon - 02/27/2017 Tues - 02/28/2017 Wed - 03/01/2017 Thurs - 03/02/2017 Fri - 03/03/2017 Sat - 03/04/2017

                                                                                                              4

                                                                                                              5

                                                                                                                                                6

      The expected output is based on a week range, A week starts on sun and end on a saturday. Hence, if I input a date 02/26/2017, we will look for the week range and look for all id that falls in the range. I am using Oracle 11g.

       

      All help is appreciated. Thank you

        • 1. Re: Date range
          Frank Kulash

          Hi,

          user13328581 wrote:

           

          Hello Experts;

           

          Please feel free to critic the design but I have the following sample data below

           

          with t (id, start_date, end_date) as

          (

          select 1, to_date('01/01/2017 17:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/01/2017 22:00:00', 'MM/DD/YYYY HH24:MI:SS') from dual

          union all

          select 2, to_date('01/02/2017 08:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/02/2017 16:00:00', 'MM/DD/YYYY HH24:MI:SS') from dual

          union all

          select 3, to_date('01/03/2017 12:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/03/2017 20:00:00', 'MM/DD/YYYY HH24:MI:SS') from dual

          union all

          select 4, to_date('03/01/2017 10:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('03/01/2017 18:00:00', 'MM/DD/YYYY HH24:MI:SS') from dual

          union all

          select 5, to_date('03/01/2017 11:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('03/01/2017 19:00:00', 'MM/DD/YYYY HH24:MI:SS') from dual

          union all

          select 6, to_date('03/01/2017 17:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('03/01/2017 22:00:00', 'MM/DD/YYYY HH24:MI:SS') from dual

          )

           

          Expected output if the date given as input was 01/01/2017

          Sun - 01/01/2017 Mon - 01/02/2017 Tues - 01/03/2017 Wed - 01/04/2017 Thurs - 01/05/2017 Fri - 01/05/2017 Sat - 01/05/2017

          1 2 3

           

          Expected output if the date given as input was 02/26/2017

          Sun - 02/26/2017 Mon - 02/27/2017 Tues - 02/28/2017 Wed - 03/01/2017 Thurs - 03/02/2017 Fri - 03/03/2017 Sat - 03/04/2017

          4

          5

          6

          The expected output is based on a week range, A week starts on sun and end on a saturday. Hence, if I input a date 02/26/2017, we will look for the week range and look for all id that falls in the range. I am using Oracle 11g.

           

          All help is appreciated. Thank you

          Sorry, I don't understand exactly what output you want.  Did this forum distort your desired output?

           

          Here's one way to see if the range start_date-end_date overlaps with the target week (that is, if any part of the start_date to end_date range is between the Sunday on or before test_date and the Saturday onb or after test_date)

          SELECT    d.test_date, t.*

          FROM      dates_to_test  d

          JOIN                     t  ON  t.start_date  <  TRUNC ( d.test_date + 1

                                                                 , 'IW'

                                                                 ) + 6

                                      AND t.end_date    >= TRUNC ( d.test_date + 1

                                                                 , 'IW'

                                                                 ) - 1

          ORDER BY  d.test_date, t.start_date

          ;

          I created another table, dates_wanted, so I could test multiple weeks at once.  Here's the dates_wanted table:

          CREATE TABLE  dates_to_test  AS

          SELECT  TO_DATE ('01/01/2017', 'MM/DD/YYYY') AS test_date FROM dual UNION ALL

          SELECT  TO_DATE ('02/26/2017', 'MM/DD/YYYY')              FROM dual

          ;

          and here's the output the query above produces:

          TEST_DATE           ID START_DATE     END_DATE

          -------------- ------- -------------- --------------

          01/01/2017 Sun       1 01/01/2017 Sun 01/01/2017 Sun

          01/01/2017 Sun       2 01/02/2017 Mon 01/02/2017 Mon

          01/01/2017 Sun       3 01/03/2017 Tue 01/03/2017 Tue

          02/26/2017 Sun       4 03/01/2017 Wed 03/01/2017 Wed

          02/26/2017 Sun       5 03/01/2017 Wed 03/01/2017 Wed

          02/26/2017 Sun       6 03/01/2017 Wed 03/01/2017 Wed

          This does not assume that the target date is midnight on Sunday; it can be any point in the week that begins ar midnight on Sunday.

           

          The querry above does not depend on your NLS settings.  It uses the ISO week, which is the same regardless of NLS settings.  The ISO week starts 1 day later than your week, which is why the magic number 1 appears in join condition.

          • 2. Re: Date range
            user13328581

            Hi frank the expected result is shown below. Note, i have a front-end application that passing the parameter, so if the date parameter is passed as 01/01/2017 then it should show result 1, if the date parameter passed is 02/26/2017 then it should show result 2

             

            Expected output if the date given as input was 01/01/2017  -- Result 1

            Sun - 01/01/2017   Mon - 01/02/2017 Tues - 01/03/2017 Wed - 01/04/2017 Thurs - 01/05/2017 Fri - 01/05/2017 Sat - 01/05/2017

                       1                2                 3

                   

            Expected output if the date given as input was 02/26/2017   -- Result 2

            Sun - 02/26/2017   Mon - 02/27/2017 Tues - 02/28/2017 Wed - 03/01/2017 Thurs - 03/02/2017 Fri - 03/03/2017 Sat - 03/04/2017

                                                                                                                    4

                                                                                                                    5

                                                                                                                                                      6

            • 3. Re: Date range
              Frank Kulash

              Hi,

              user13328581 wrote:

               

              Hi frank the expected result is shown below. Note, i have a front-end application that passing the parameter, so if the date parameter is passed as 01/01/2017 then it should show result 1, if the date parameter passed is 02/26/2017 then it should show result 2

               

              Expected output if the date given as input was 01/01/2017 -- Result 1

              Sun - 01/01/2017 Mon - 01/02/2017 Tues - 01/03/2017 Wed - 01/04/2017 Thurs - 01/05/2017 Fri - 01/05/2017 Sat - 01/05/2017

              1 2 3

               

              Expected output if the date given as input was 02/26/2017 -- Result 2

              Sun - 02/26/2017 Mon - 02/27/2017 Tues - 02/28/2017 Wed - 03/01/2017 Thurs - 03/02/2017 Fri - 03/03/2017 Sat - 03/04/2017

              4

              5

              6

              Oh, I see; you want the ids pivoted, so all the Sundays are in one column, all the Mondays in the next column, etc.

               

              What if start_date and end_date are on different days?  In the query below, only start_date is considered.  An id will appear in the output only if start_date is in the target week.

              WITH  got_dow    AS

              (

                  SELECT  id

                  ,       TRUNC (start_date) - TRUNC (start_date, 'IW')  AS dow

                  FROM    t

                  WHERE   start_date  >= TRUNC ( &test_date + 1

                                               , 'IW'

                                               ) - 1

                  AND     start_date  <  TRUNC ( &test_date + 1

                                               , 'IW'

                                               ) + 6

              )

              ,    got_rnum    AS

              (

                  SELECT  g.*

                  ,       ROW_NUMBER () OVER ( PARTITION BY  dow

                                               ORDER BY      id

                                             )  AS rnum

                  FROM    got_dow  g

              )

              SELECT    *  -- or list all columns except rnum

              FROM      got_rnum

              PIVOT     (    MIN (id)

                        FOR  dow  IN  ( 6  AS sun

                                      , 0  AS mon

                                      , 1  AS tue

                                      , 2  AS wed

                                      , 3  AS thu

                                      , 4  AS fri

                                      , 5  AS sat

                                      )

                        )

              ORDER BY  rnum

              ;

              I used a substitution variable to pass in the test_date; you can do any other way.

               

              Again, in the sample data you posted, end_date is always on the same calendar day as start_date. If that's not always the case, then post some new sample data and desired results where they are on different days, or different weeks.  Explain, in general terms, what you want for output.

               

              The output I get for test_date = DATE '2017-02-26' is

                 RNUM     SUN     MON     TUE     WED     THU     FRI     SAT

              ------- ------- ------- ------- ------- ------- ------- -------

                    1                               4

                    2                               5

                    3                               6

              In reply #2, it looks like you asked for id=6 to be in the Thursday column.  Was that just a typo? If not, explain.

               

              If you want the column names to depend on test_date, then you need dynamic SQL.

              See   Re: Please Help in query   for an alternative.

              • 4. Re: Date range
                mathguy

                Are the start_date and the end_date in each row always on the same calendar date? (So, an "event" always begins and ends during the same 24-hour period from midnight to midnight?) If not, what is the logic for the desired output?

                 

                Then: Is the number of rows in the output equal to the maximum number of "events" in one day? It seems so from your second example. But, in the second example, why doesn't 6 line up vertically below 4 and 5? All three events are on the same day. (And, if 6 was on a different day, the output would be just two rows, not three - right?)

                 

                If you want to line up one event per day horizontally, do you have a preferred ordering? Such as "the earliest start_date event on each day goes in one row; the second earliest on each day in another row" etc.?

                 

                Does your user interface guarantee that the input date is a Sunday, or does that need to be checked?

                • 5. Re: Date range
                  user13328581

                  @ Frank

                   

                  Oh, I see; you want the ids pivoted, so all the Sundays are in one column, all the Mondays in the next column, etc.

                   

                  Yes

                   

                  What if start_date and end_date are on different days? 

                   

                  The Start_date and end_date will always be on the same day

                   

                  In reply #2, it looks like you asked for id=6 to be in the Thursday column.  Was that just a typo? If not, explain.

                   

                  It was a typo, I just fixed  it by assigning a different day to  ID=6

                   

                  Thanks, I will test your posted solution

                   

                  mathguy

                   

                  Are the start_date and the end_date in each row always on the same calendar date? (So, an "event" always begins and ends during the same 24-hour period from midnight to midnight?) If not, what is the logic for the desired output?

                   

                  Then: Is the number of rows in the output equal to the maximum number of "events" in one day? It seems so from your second example. But, in the second example, why doesn't 6 line up vertically below 4 and 5? All three events are on the same day. (And, if 6 was on a different day, the output would be just two rows, not three - right?)

                   

                  It was a typo, I just fixed  it by assigning a different day to  ID=6

                   

                  Does your user interface guarantee that the input date is a Sunday, or does that need to be checked?

                   

                  Yes there is a guarantee the input date is a sunday.

                   

                  Thanks for all the comments and feedback

                  • 6. Re: Date range
                    mathguy

                    Here is one way to do this.

                     

                    select sun, mon, tue, wed, thu, fri, sat

                    from   (

                             select id, to_char(start_date, 'Dy') dy,

                                    row_number() over (partition by trunc(start_date) to_char(start_date, 'Dy') order by start_date) rn

                             from   t

                             where  start_date >= to_date(:dt, 'mm/dd/yyyy') and start_date < to_date(:dt, 'mm/dd/yyyy') + 7

                           )

                    pivot  ( max(id) for dy in ('Sun' sun, 'Mon' mon, 'Tue' tue, 'Wed' wed, 'Thu' thu, 'Fri' fri, 'Sat' sat) )

                    order by rn   --  If needed.

                    ;

                     

                    Note that the date will not appear in column names. Dynamic column names = dynamic SQL; best avoided if possible. How critical is it in your problem?

                     

                    EDIT: As can be seen in the code above, I changed the expression in the PARTITION BY clause of the ROW_NUMBER() analytic function. Why? Because we use the abbreviated name of the day of the week anyway (for column names), and since we need to compute that anyway in one place, it is best to use it as many times as needed and possible (the computation is done only once for the entire query). No need to compute the TRUNC(...) if the TO_CHAR(...) is already available for the same purpose.

                    • 7. Re: Date range
                      user13328581

                      Both solutions are excellent...Thanks all for the help so far..

                       

                      I know dynamic column names = dynamic sql are bad, however is there another options available to get the dates to appear in the column names. Would it require the re-design of my data model..Thanks

                      • 8. Re: Date range
                        mathguy

                        As far as I know, at least if you need to do the pivoting IN SQL (and not, for example, in your reporting software - a much better alternative!), the only options to have the dates in the column names are for them to be hard-coded (not an option if the user may input different dates) OR to use dynamic SQL.

                        • 9. Re: Date range
                          user13328581

                          ok, then it looks like I will have to keep a table of dates and use that to create the dynamic columns right?

                          • 10. Re: Date range
                            Frank Kulash

                            Hi,

                            user13328581 wrote:

                             

                            ok, then it looks like I will have to keep a table of dates and use that to create the dynamic columns right?

                            A table is one way to get the column names, but it's not the only way.  For example, you could generate the column names in a PL/SQL loop, with no table involved, not even dual.

                             

                            If you want to use something that resembles a table, then keeping a permanent table is just one way, not the only way.

                            It's easy enough to generate a result set on the fly, like this:

                            SELECT  TRUNC ( TO_DATE (:test_date, 'MM/DD/YYYY') + 1

                                          , 'IW'

                                          ) + LEVEL - 2   AS A_DATE

                            FROM    dual

                            CONNECT BY  LEVEL  <= 7

                            ;

                            Output (when :test_date='02/26/2017'):

                             

                            A_DATE
                            Sun_02_26_2017
                            Mon_02_27_2017
                            Tue_02_28_2017
                            Wed_03_01_2017
                            Thu_03_02_2017
                            Fri_03_03_2017
                            Sat_03_04_2017

                             

                            If you're certain that :test_date is a Sunday, you can make it a little simpler.

                            • 11. Re: Date range
                              user13328581

                              Thanks a lot for the suggestions, I will start work on the pl/sql

                              • 12. Re: Date range
                                Frank Kulash

                                Hi,

                                user13328581 wrote:

                                 

                                Thanks a lot for the suggestions, I will start work on the pl/sql

                                Have fun!

                                 

                                If I were doing this (and didn't have a front-end tool that could dynamically name the columns), I wouldn't use PL/SQL or dynamic SQL.  The link in reply #3 ( Re: Please Help in query  ) shows how you can get output like this:

                                SUNDAY     MONDAY     TUESDAY    WEDNESDAY  THURSDAY   FRIDAY     SATURDAY

                                ---------- ---------- ---------- ---------- ---------- ---------- ----------

                                02/26/2017 02/27/2017 02/28/2017 O3/01/2017 O3/02/2017 O3/03/2017 O3/04/2017

                                ========== ========== ========== ========== ========== ========== ==========

                                                                     4

                                                                     5

                                                                     6

                                using plain old static SQL.

                                • 13. Re: Date range
                                  mathguy

                                  Here is one way to do your dynamic pivoting.

                                   

                                  DISCLAIMER: This is my first serious attempt at dynamic pivoting. Even though the code compiles without errors and produces the expected results, it probably does a lot of things wrong, inefficiently, or both. Take it with a grain of salt. I am posting this as much for my own benefit as for yours - I will learn from the contributions from other posters.

                                   

                                  I am also cheating; you said you are using Oracle 11, and I am using a feature introduced in 12.1: the DBMS_SQL package now has a procedure, RETURN_RESULT, that allows me to easily get the output from a ref cursor. The rest of the code is probably the same, but for an Oracle 11 solution you (or I!) need another mechanism for this part.

                                   

                                  First the end result: I call the procedure with different input dates to see what the output looks like. (The procedure code is shown at the end.) I used your WITH clause to create a table named T with the columns ID, START_DATE, END_DATE and the data exactly as you provided it.

                                   

                                  From my SQL*Plus session:

                                   

                                  SQL> exec week_events(to_date('01/01/2017', 'mm/dd/yyyy'))

                                   

                                  PL/SQL procedure successfully completed.

                                   

                                  ResultSet #1

                                   

                                  Sun - 01/01/2017 Mon - 01/02/2017 Tue - 01/03/2017 Wed - 01/04/2017 Thu - 01/05/2017 Fri - 01/06/2017 Sat - 01/07/2017
                                  ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
                                                 1                2                3

                                   

                                  1 row selected.

                                   

                                  Elapsed: 00:00:00.00
                                  SQL> exec week_events(to_date('01/01/2010', 'mm/dd/yyyy'))

                                   

                                  PL/SQL procedure successfully completed.

                                   

                                  ResultSet #1

                                   

                                  no rows selected

                                   

                                  Elapsed: 00:00:00.00
                                  SQL> exec week_events(to_date('02/26/2017', 'mm/dd/yyyy'))

                                   

                                  PL/SQL procedure successfully completed.

                                   

                                  ResultSet #1

                                   

                                  Sun - 02/26/2017 Mon - 02/27/2017 Tue - 02/28/2017 Wed - 03/01/2017 Thu - 03/02/2017 Fri - 03/03/2017 Sat - 03/04/2017
                                  ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
                                                                                                    4
                                                                                                    5
                                                                                                    6

                                   

                                  3 rows selected.

                                   

                                  Elapsed: 00:00:00.01

                                   

                                  Actually, with the way I wrote the code, it works with any input date (it doesn't have to be a Sunday) - it just goes for seven days from the input date. This wasn't intentional, I just realized it as I was thinking about it.

                                   

                                  SQL> exec week_events(to_date('02/28/2017', 'mm/dd/yyyy'))

                                   

                                  PL/SQL procedure successfully completed.

                                   

                                  ResultSet #1

                                   

                                  Tue - 02/28/2017 Wed - 03/01/2017 Thu - 03/02/2017 Fri - 03/03/2017 Sat - 03/04/2017 Sun - 03/05/2017 Mon - 03/06/2017
                                  ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
                                                                  4
                                                                  5
                                                                  6

                                   

                                  3 rows selected.

                                   

                                  Elapsed: 00:00:00.02

                                   

                                  Here is the code for the WEEK_EVENTS procedure:

                                   

                                  create or replace procedure week_events(dt date)

                                  as

                                    rc sys_refcursor;

                                    sql_str clob;

                                  begin

                                   

                                    for i in 0 .. 6 loop

                                      sql_str := sql_str

                                                   || case when i > 0 then ', ' end

                                                   || to_char(dt + i, 'dy') || ' as '

                                                   || '"'

                                                   || to_char(dt + i, 'Dy - mm/dd/yyyy')

                                                   || '"';

                                    end loop;

                                   

                                    sql_str := 'select ' || sql_str

                                           || q'{

                                  from   (

                                           select id, to_char(start_date, 'Dy') dy,

                                                  row_number() over (partition by to_char(start_date, 'Dy') order by start_date) rn

                                           from   t

                                           where  start_date >= to_date('}'

                                           || to_char(dt, 'mm/dd/yyyy')

                                           || q'{', 'mm/dd/yyyy') and start_date < to_date('}'

                                           || to_char(dt + 7, 'mm/dd/yyyy')

                                           || q'{', 'mm/dd/yyyy')

                                         )

                                  pivot  ( max(id) for dy in ('Sun' sun, 'Mon' mon, 'Tue' tue, 'Wed' wed, 'Thu' thu, 'Fri' fri, 'Sat' sat) )

                                  order by rn

                                  }';

                                   

                                    open rc for sql_str;

                                    dbms_sql.return_result(rc);

                                   

                                  end;

                                  /

                                  1 person found this helpful
                                  • 14. Re: Date range
                                    user13328581

                                    I looked at the link and unfortunately I am still not getting how this still can still be done in plain old sql...

                                    1 2 Previous Next