SQL & PL/SQL

Hello,

I am trying to build a query to get days working hours per a company.

Requirement: If the consequent days have the same open and close timings then we have to club them together, if not display individually. For better understanding of the requirement, please see the below two examples.

Ex1: Below are the company working hours

        Open time(8AM) and close time(6PM)  from Monday to Friday

        Open time(9AM) and close time(5PM) on Saturday

        Office closed on Sunday

   Expected Output:

       Oracle    MON-FRI 8AM to 6PM

       Oracle    SAT         9AM to 5PM

       Oracle    SUN        Closed

Ex2:

        Open time(8AM) and close time(6PM) on Monday

        Open time(9AM) and close time(7PM) on Tuesday

        Open time(8AM) and close time(6PM) on Wednesday and Thusday

        Open time(9AM) and close time(7PM) on  Friday

        Closed on Saturday and Sunday.

   Expected Output:

       Oracle    MON          8AM to 6PM

       Oracle    THU           9AM to 7PM

I have form a query for the data. Please use it and help me to get the result.

with temp as (select 'Oracle' Company, 'MO' WK, 'Y' OPEN_IND, '8AM' OPN_TM, '6PM' CLOS_TM from dual               UNION ALL               select 'Oracle' Company, 'TU' WK, 'Y' OPEN_IND, '9AM' OPN_TM, '7PM' CLOS_TM from dual               UNION ALL               select 'Oracle' Company, 'WE' WK, 'Y' OPEN_IND, '9AM' OPN_TM, '7PM' CLOS_TM from dual               UNION ALL               select 'Oracle' Company, 'TH' WK, 'Y' OPEN_IND, '8AM' OPN_TM, '6PM' CLOS_TM from dual               UNION ALL               select 'Oracle' Company, 'FR' WK, 'Y' OPEN_IND, '8AM' OPN_TM, '1PM' CLOS_TM from dual               UNION ALL               select 'Oracle' Company, 'SA' WK, 'N' OPEN_IND, NULL OPN_TM, NULL CLOS_TM from dual               UNION ALL               select 'Oracle' Company, 'SU' WK, 'N' OPEN_IND, NULL OPN_TM, NULL CLOS_TM from dual               )                select t.*  from temp t

Thanks.

with temp as (select 'Oracle' Company, 'MO' WK, 'Y' OPEN_IND, '8AM' OPN_TM, '6PM' CLOS_TM from dual              UNION ALL              select 'Oracle' Company, 'TU' WK, 'Y' OPEN_IND, '9AM' OPN_TM, '7PM' CLOS_TM from dual              UNION ALL              select 'Oracle' Company, 'WE' WK, 'Y' OPEN_IND, '9AM' OPN_TM, '7PM' CLOS_TM from dual              UNION ALL              select 'Oracle' Company, 'TH' WK, 'Y' OPEN_IND, '8AM' OPN_TM, '6PM' CLOS_TM from dual              UNION ALL              select 'Oracle' Company, 'FR' WK, 'Y' OPEN_IND, '8AM' OPN_TM, '1PM' CLOS_TM from dual              UNION ALL              select 'Oracle' Company, 'SA' WK, 'N' OPEN_IND, NULL OPN_TM, NULL CLOS_TM from dual              UNION ALL              select 'Oracle' Company, 'SU' WK, 'N' OPEN_IND, NULL OPN_TM, NULL CLOS_TM from dual              )               select t.*  from temp t