CODEWITHSUNDEEP
sqloracle-databaseselect
Felipe M
Felipe M

Reputation: 459

Select Where Date Between

I would like to SELECT a table calendar and combine the results with the days of the month. I mean,

Table: Calendar

ID  TEAM     EMPLOYER         START                     END
17  19          8       04/08/2014 18:01:00     11/08/2014 07:59:00
18  19          39      11/08/2014 18:01:00     18/08/2014 07:59:00
19  19          44      18/08/2014 18:01:00     25/08/2014 07:59:00
20  19          38      25/08/2014 18:01:00     01/09/2014 07:59:00

And I have a SELECT for the days of the month. 

Select Days.Dt
  From (Select Trunc(To_Date('2014', 'YYYY'), 'y') - 1 + Rownum Dt
          From All_Objects
         Where Rownum <= Add_Months(Trunc(To_Date('2014', 'YYYY'), 'y'), 12) -
               Trunc(To_Date('2014', 'YYYY'), 'y')) Days
  Where To_Char(Dt, 'mm/yyyy') = '08/2014'

What I want is something like this:

        DAY             EMPLOYER_END                EMPLOYER_START
1   01/08/2014          
2   02/08/2014
3   03/08/2014
4   04/08/2014                                          4
5   05/08/2014                4                         4
6   06/08/2014                4                         4
7   07/08/2014                4                         4
8   08/08/2014                4                         4
9   09/08/2014                4                         4
10  10/08/2014                4                         4
11  11/08/2014                4                         39
12  12/08/2014                39                        39

The employer starts at 18:01 (always) and end at 07:59 (always).

Does anyone know if it's possible? And the way I can do that.

Thanks!

Upvotes: 0

Views: 129

Answers (2)

cdummy
cdummy

Reputation: 455

I guess this can be useful to you

WITH mindates AS
  (SELECT TRUNC(MIN(startdate),'month') st_date,
    TRUNC(MAX(enddate)) ed_date
  FROM calendar
  ) ,
  dates AS
  (SELECT st_date+ rownum-1 AS dates_col
  FROM mindates,
    dual
    CONNECT BY rownum <= (ed_date- st_date)+1
  )
SELECT d.dates_col dates,
  MIN((
  CASE
    WHEN d.dates_col=c.startdate
    THEN NULL
    ELSE c.employer
  END)) AS employer_end,
  MIN((
  CASE
    WHEN d.dates_col=c.enddate
    THEN NULL
    ELSE c.employer
  END )) AS employer_start
FROM dates d
LEFT OUTER JOIN calendar c
ON d.dates_col BETWEEN c.startdate AND c.enddate
GROUP BY d.dates_col
ORDER BY d.dates_col;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Your desired results do not match your sample data. However, I think you want something like this:

with dates as (
      Select Days.Dt
      From (Select Trunc(To_Date('2014', 'YYYY'), 'y') - 1 + Rownum Dt
            From All_Objects
            Where Rownum <= Add_Months(Trunc(To_Date('2014', 'YYYY'), 'y'), 12) -
                            Trunc(To_Date('2014', 'YYYY'), 'y')
           ) Days
      Where To_Char(Dt, 'mm/yyyy') = '08/2014'
     )
select d.dt,
       sum(case when c.employer_start = d.ddt then 0 else 1 end) as employer_end,
       sum(case when c.employer_end = d.dt then 1 else 0 end) as employer_start
from dates d left outer join
     calendar c
     on d.dt between c.employer_start and c.employer_end
group by d.dt
order by d.dt;

Upvotes: 1

Related Questions