CODEWITHSUNDEEP
sqloracle-database
dasdasd
dasdasd

Reputation: 2031

Check if date range fall within a WW

I have a question about comparing date ranges. I have a table that hold a state log of a machine. The state can be 0 or 1. In addition I have the date when the machine state change started and when it ended

START_DATE | END_DATE | STATE

For example:

     START_DATE     |       END_DATE        | STATE
2019-05-28 07:12:43   2019-05-29 09:12:43       1
2019-05-29 09:12:43   2019-06-01 08:12:43       0
2019-06-11 10:12:43   2019-06-12 16:12:43       1
2019-06-12 16:12:43   2019-06-12 17:12:43       0

I want to make a report that iterate through each WW (work week) and check what average state was on that WW.

My problem is that a state change could have happened on WW22 and ended on WW24 so when I GROUP BY WW I get no values on WW23 because there was no start or end state on WW23. But on WW23 that machine was on state 0 because it started on WW22 and ended on WW24 but through all the this time the state was 0.

It seems that I cant use GROUP BY WW to solve it. I may need to check START_DATE and END_DATE on cases there is no records on WW23. to add something like:

CASE WHEN WW BETWEEN START_DATE AND END_DATE THEN...

But im not sure how to loop on the WW without using GROUP BY.

I use SQL ORACLE

Thanks.

Upvotes: 0

Views: 110

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

I hope I understood correctly. It would be good if You showed us your query and tell us how you count average state and where these weeks come from. Anyway here is query which generates all weeks for year 2019 and joins with your log. 

select to_char(wsd, 'iw') week, wsd, start_date, end_date, state
  from (
    select trunc(date '2019-01-01', 'iw') + level * 7 - 7 wsd
      from dual 
      connect by trunc(date '2019-01-01', 'iw') + level * 7 <= date '2020-01-01')
  left join log on wsd < end_date and start_date < wsd + 7

Interesting is this range:

week  week_start_date  log_start            log_end              state
21    2019-05-20
22    2019-05-27       2019-05-28 07:12:43  2019-05-29 09:12:43  1
22    2019-05-27       2019-05-29 09:12:43  2019-06-01 08:12:43  0
23    2019-06-03
24    2019-06-10       2019-06-11 10:12:43  2019-06-12 16:12:43  1
24    2019-06-10       2019-06-12 16:12:43  2019-06-12 17:12:43  0
25    2019-06-17

I don't know how you count average state for weeks 22 and 24. Maybe it is weighted average of substracted times, maybe somehow other. But it's not important, now you have row for week 23, with missing state. If this means that previous value is valid for this week use:

nvl(state, lag(state) over (order by wsd))

or 

coalesce(state, lag(state) over (order by wsd), 0)

when you want 0 as default value when we also miss previous week(s). If two weeks are missing add ignore nulls to lag.

Then you can group data by weeks and count average values.

dbfiddle demo

Upvotes: 1

Related Questions