SQL calculate no job days

Question

I have to write a oracle SQL query to calculate total "no-job-days" for a given month and job id from LOG table. table has data like:

LogId JobID       LogDate     JobStatu
1       1         09/01/2019  active
2       1         09/02/2019  end
3       2         08/03/2019  active
4       2         08/05/2019  suspended
5       2         08/08/2019  active
6       2         08/15/2019  end

job-days are the days between active and end OR active and Suspended. Job can get suspended in between. so in above example for the month of September job-day is 1 (started on 1st and ended on 2nd)and for month of august it is 9 days (started on 3rd and suspended on 5th so total 2 days of work and again started on 8th and ended on 15th is 7 days so total days of work, 2 + 7 = 9).

expected result, no-job days is for the month of September 29 (total days in month 30 - 1 day work) and for the month of August it 22 (31 - 9).

can anyone please help me to write a Oracle SQL query for this.

Thanks.

Answer 1

You can use this code in Oracle.

In WITH AS I provided both your test data (log_table) and a table of dates (dates).

You can keep the part with the table of dates, if your DB doesn't have in it one already created:

with log_table (LogId, JobID, LogDate, JobStatu) as (
    select 1, 1, to_date('09/01/2019', 'MM/DD/YYYY'), 'active' from dual
    union all select 2, 1, to_date('09/02/2019', 'MM/DD/YYYY'), 'end' from dual
    union all select 3, 2, to_date('08/03/2019', 'MM/DD/YYYY'), 'active' from dual
    union all select 4, 2, to_date('08/05/2019', 'MM/DD/YYYY'), 'suspended' from dual
    union all select 5, 2, to_date('08/08/2019', 'MM/DD/YYYY'), 'active' from dual
    union all select 6, 2, to_date('08/15/2019', 'MM/DD/YYYY'), 'end' from dual
),
dates as (
    select trunc(to_date('31.12.2019', 'DD.MM.YYYY') - (rownum - 1)) as date_val
         , extract(month from trunc (to_date('31.12.2019', 'DD.MM.YYYY') - (rownum - 1))) as month_val
      from dual connect by rownum < 366
)
select dts.month_val as "Month"
     , count(dts.date_val) as "Number of days"
  from dates dts
 where not exists (select 1
                     from log_table lt1
                    where lt1.JobStatu = 'active'
                      and dts.date_val between lt1.LogDate 
                                           and (select min(LogDate) - 1
                                                  from log_table
                                                 where JobID = lt1.JobID
                                                   and JobStatu in ('end', 'suspended')
                                                   and LogDate > lt1.LogDate)
                      and rownum = 1)
 group by dts.month_val
 order by dts.month_val asc 

Result:

    +--------+----------------+
    | Month  | Number of days | 
    +--------+----------------+
    |     1  |             31 | 
    |     2  |             28 | 
    |     3  |             31 | 
    |     4  |             30 | 
    |     5  |             31 | 
    |     6  |             30 | 
    |     7  |             31 | 
    |     8  |             22 | 
    |     9  |             29 | 
    |    10  |             31 | 
    |    11  |             30 | 
    |    12  |             31 | 
    +--------+----------------+

Answer 2

You can try the SQL for Pattern Matching (MATCH_RECOGNIZE):

WITH t(LogId, JobID, LogDate, JobStatus) AS
    (SELECT 1, 1, TO_DATE('09/01/2019','mm/dd/YYYY'), 'active' FROM dual UNION ALL
    SELECT 2, 1, TO_DATE('09/02/2019','mm/dd/YYYY'), 'end' FROM dual UNION ALL
    SELECT 3, 2, TO_DATE('08/03/2019','mm/dd/YYYY'), 'active' FROM dual UNION ALL
    SELECT 4, 2, TO_DATE('08/05/2019','mm/dd/YYYY'), 'suspended' FROM dual UNION ALL
    SELECT 5, 2, TO_DATE('08/08/2019','mm/dd/YYYY'), 'active' FROM dual UNION ALL
    SELECT 6, 2, TO_DATE('08/15/2019','mm/dd/YYYY'), 'end' FROM dual UNION ALL
    SELECT 7, 3, TO_DATE('06/01/2019','mm/dd/YYYY'), 'active' FROM dual UNION ALL
    SELECT 8, 3, TO_DATE('06/04/2019','mm/dd/YYYY'), 'suspended' FROM dual UNION ALL
    SELECT 9, 3, TO_DATE('06/08/2019','mm/dd/YYYY'), 'active' FROM dual UNION ALL
    SELECT 10, 3, TO_DATE('06/12/2019','mm/dd/YYYY'), 'suspended' FROM dual UNION ALL
    SELECT 11, 3, TO_DATE('06/18/2019','mm/dd/YYYY'), 'active' FROM dual UNION ALL
    SELECT 12, 3, TO_DATE('06/25/2019','mm/dd/YYYY'), 'end' FROM dual),
m as 
    (SELECT JobID, ACTIVE_DATE, END_DATE, SUSPEND_DAYS, JobStatus, var_match
    FROM t
         MATCH_RECOGNIZE (
              PARTITION BY JobID
              ORDER BY LogId
              MEASURES 
                    CLASSIFIER() AS var_match,
                    FINAL FIRST(s_ACTIVE.LogDate) AS ACTIVE_DATE,
                    FINAL LAST(s_END.LogDate) AS END_DATE,
                    (s_REACTIVE.LogDate - s_SUSPENDED.LogDate) AS SUSPEND_DAYS
              ALL ROWS PER MATCH
              PATTERN ( s_ACTIVE (s_SUSPENDED s_REACTIVE)* s_END )
              DEFINE
                    s_ACTIVE AS JobStatus = 'active',
                    s_REACTIVE AS JobStatus = 'active',
                    s_END AS JobStatus = 'end',
                    s_SUSPENDED AS JobStatus = 'suspended' 
         )
    )
SELECT JobID, 
    MIN(ACTIVE_DATE) AS START_DATE, 
    MAX(END_DATE) AS END_DATE, 
    SUM(SUSPEND_DAYS) AS SUSPENDED_DAYS,
    MAX(END_DATE) - MIN(ACTIVE_DATE) - NVL(SUM(SUSPEND_DAYS),0) AS ACTIVE_DAYS,
    EXTRACT(DAY FROM (LAST_DAY(MIN(ACTIVE_DATE)))) - (MAX(END_DATE) - MIN(ACTIVE_DATE) - NVL(SUM(SUSPEND_DAYS),0)) AS ACTIVE_DAYS_FROM_CURRENT_MONTH
FROM m    
WHERE JobStatus = 'active'
GROUP BY JobID
ORDER BY JobID; 

Result:

+-------------------------------------------------------------------------------------+
|JOBID|START_DATE|END_DATE  |SUSPENDED_DAYS|ACTIVE_DAYS|ACTIVE_DAYS_FROM_CURRENT_MONTH|
+-------------------------------------------------------------------------------------+
|1    |01.09.2019|02.09.2019|              |1          |29                            |
|2    |03.08.2019|15.08.2019|3             |9          |22                            |
|3    |01.06.2019|25.06.2019|10            |14         |16                            |
+-------------------------------------------------------------------------------------+

Answer 3

I am trying to convert @Fahmi answer to Oracle but getting error:

 with Log_data as
( 
select tkt_logtest_kb.*, lag(logdate) over(partition by jobid order by logid) as t,
lag(jobstatus) over(partition by jobid order by logid) as st
from tkt_logtest_kb) 
select Log_data.jobid, sum(to_date(t, 'MM-dd-yyyy') - to_date(logdate, 'MM-dd-yyyy')) as totaldays from  Log_data 
where st='Active' or (jobstatus='Active' and (st is not null and st<>'Suspended'))
group by Log_data.jobid

ORA-01858: a non-numeric character was found where a numeric was expected 01858. 00000 - "a non-numeric character was found where a numeric was expected" *Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model. *Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation.

Answer 4

You can try below - just change datediff() to equivalent oracle function

DEMO

select jobid,SUM(logdate-t) as totaldays from 
(
select t1.*,lag(logdate) over(partition by jobid order by logid) as t,
lag(jobstatus) over(partition by jobid order by logid) as st
from t1
)A where st='active' or (jobstatus='active' and (st is not null and st<>'suspended'))
group by jobid

NOTE: this demo is in sql server but this concept will work in your case, you need to change the datediff() part