CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11goracle-sqldeveloper
Jay Khan
Jay Khan

Reputation: 98

How to partition my data by a specific date and another identifier SQL

with cte as
(
    select to_date('01-JUN-2020','DD-MON-YYYY')+(level-1) DT
    from dual
    connect bY level<= 30
)
select * 
from cte x
left outer join 
    (select date from time where emp in (1, 2)) a on x.dt = a.date

In this scenario I am trying to find the missing days that these persons didn't report to work... it works well for 1 person. I get back their missing days correctly. But when I add 2 persons.. I do not get back the correct missing days for them because I'm only joining on date I guess.

I would like to know how I can partition this data by the persons id and date to be able get accurate days that each were missing.

Please help, thanks.

Upvotes: 1

Views: 282

Answers (1)

GMB
GMB

Reputation: 222412

You would typically cross join the list of dates with the list of persons, and then use not exists to pull out the missing person/date tuples:

with cte as ( 
    select date '2020-06-01' + level - 1 dt
    from dual 
    connect by level <= 30 
)
select c.dt, e.emp
from cte c
cross join (select distinct emp from times) e
where not exists (
    select 1
    from times t
    where t.emp = e.emp and t.dt = e.date
)

Note that this uses a literal date rather than to_date(), which is more appropriate here.

This gives the missing tuples for all persons at once. If you want just for a predefined list of persons, then:

with cte as ( 
    select date '2020-06-01' + level - 1 dt
    from dual 
    connect by level <= 30 
)
select c.dt, e.emp
from cte c
cross join (select 1 emp from dual union all select 2 from dual) e
where not exists (
    select 1
    from times t
    where t.emp = e.emp and t.dt = e.date
)

If you want to also see the "presence" dates, then use a left join rather than not exists, as in your original query:

with cte as ( 
    select date '2020-06-01' + level - 1 dt
    from dual 
    connect by level <= 30 
)
select c.dt, e.emp, -- enumerate the relevant columns from "t" here
from cte c
cross join (select 1 emp from dual union all select 2 from dual) e
left join times t on t.emp = e.emp and t.dt = e.date

Upvotes: 1

Related Questions