Geek asks and answers

Is there a good SQL approach to summarize a table with date range blocks as input?

Does anyone have a good approach for generating a daily resume from a table containing time intervals given by start and end dates (where the zero-stop facility goes on indefinitely)?

The best way to describe the problem is with an example. Imagine database tracking schedules for employees in a restaurant where employees are often hired and fired. Sometimes employees may return and re-hire.

For a table with one row for each employee, as shown below:

position,name,start,end
bottle washer,Fred,1/2/2013,1/5/2013
bottle washer,Barney,1/4/2013,1/7/2013
bottle washer,Betty,1/10/2013,
bottle washer,Wilma,1/12/2013,1/13/2013
cook,Bilbo,1/1/2013,1/3/2013
cook,Frodo,1/5/2013,1/8/2013
cook,Bilbo,1/7/2013

I am looking for a query that creates one row for each day / position, providing the staffing level for that day, for example:

position,date,staffing
bottle washer,1/1/2013,0
bottle washer,1/2/2013,1
bottle washer,1/3/2013,1
bottle washer,1/4/2013,2
bottle washer,1/5/2013,2
bottle washer,1/6/2013,1
bottle washer,1/7/2013,1
bottle washer,1/8/2013,0
bottle washer,1/9/2013,0
bottle washer,1/10/2013,1
bottle washer,1/11/2013,1
bottle washer,1/12/2013,2
bottle washer,1/13/2013,2
bottle washer,1/14/2013,1
bottle washer,1/15/2013,1
cook,1/1/2013,1
cook,1/2/2013,1
cook,1/3/2013,1
cook,1/4/2013,0
cook,1/5/2013,1
cook,1/6/2013,1
cook,1/7/2013,2
cook,1/8/2013,2
cook,1/9/2013,1
cook,1/10/2013,1
cook,1/11/2013,1
cook,1/12/2013,1
cook,1/13/2013,1
cook,1/14/2013,1
cook,1/15/2013,1

, , , SQL-. , . Oracle, .

?

+3
3

. , .

​​ A ​​ B.

A B.

. 14- 15- , .

with schedules as
(
    --Schedule data.
    select 'bottle washer' position, 'Fred'   name, date '2013-01-02' startdate, date '2013-01-05' enddate from dual union all
    select 'bottle washer' position, 'Barney' name, date '2013-01-04' startdate, date '2013-01-07' enddate from dual union all
    select 'bottle washer' position, 'Betty'  name, date '2013-01-10' startdate, null              enddate from dual union all
    select 'bottle washer' position, 'Wilma'  name, date '2013-01-12' startdate, date '2013-01-13' enddate from dual union all
    select 'cook'          position, 'Bilbo'  name, date '2013-01-01' startdate, date '2013-01-03' enddate from dual union all
    select 'cook'          position, 'Frodo'  name, date '2013-01-05' startdate, date '2013-01-08' enddate from dual union all
    select 'cook'          position, 'Bilbo'  name, date '2013-01-07' startdate, null              enddate from dual
),
dates as
(
    --Date range, based on schedule data.
    select first_startdate + level - 1 the_date
    from
    (
        select min(startdate) first_startdate
            ,(max(enddate) - min(startdate))+1 date_diff
        from schedules
    )
    connect by level <= date_diff
)
--Count of staff working per position, per day.
select position, the_date, count(schedules.startdate) staffing
from dates
left join schedules partition by (position)
    on dates.the_date between schedules.startdate
        and nvl(schedules.enddate, date '9999-12-31')
group by position, the_date
order by position, the_date;
+2

CTE , . , . :

WITH tbl AS
         (SELECT 'bottle washer' AS position,
                 'Fred' AS name,
                 to_date ('01/02/2013', 'mm/dd/yyyy') AS startdate,
                 to_date ('01/05/2013', 'mm/dd/yyyy') AS enddate
            FROM dual
          UNION ALL
          SELECT 'bottle washer',
                 'Barney',
                 to_date ('01/04/2013', 'mm/dd/yyyy'),
                 to_date ('01/07/2013', 'mm/dd/yyyy')
            FROM dual
          UNION ALL
          SELECT 'bottle washer',
                 'Betty',
                 to_date ('01/10/2013', 'mm/dd/yyyy'),
                 NULL
            FROM dual
          UNION ALL
          SELECT 'bottle washer',
                 'Wilma',
                 to_date ('01/12/2013', 'mm/dd/yyyy'),
                 to_date ('01/13/2013', 'mm/dd/yyyy')
            FROM dual
          UNION ALL
          SELECT 'cook',
                 'Bilbo',
                 to_date ('01/01/2013', 'mm/dd/yyyy'),
                 to_date ('01/03/2013', 'mm/dd/yyyy')
            FROM dual
          UNION ALL
          SELECT 'cook',
                 'Frodo',
                 to_date ('01/05/2013', 'mm/dd/yyyy'),
                 to_date ('01/08/2013', 'mm/dd/yyyy')
            FROM dual
          UNION ALL
          SELECT 'cook',
                 'Bilbo',
                 to_date ('01/07/2013', 'mm/dd/yyyy'),
                 NULL
            FROM dual),
     daterange AS
         (    SELECT to_date ('01-JAN-2013') + rownum - 1 AS dd
                FROM dual
          CONNECT BY rownum <= 15
          START WITH rownum = 1)
  SELECT t.position,
         to_char(d.dd, 'mm/dd/yyyy') as "date",
         count (
             CASE
                 WHEN     t.enddate IS NOT NULL
                      AND d.dd BETWEEN t.startdate AND t.enddate THEN
                     1
                 WHEN t.enddate IS NULL AND d.dd >= t.startdate THEN
                     1
                 ELSE
                     NULL
             END)
             AS staffing
    FROM tbl t CROSS JOIN daterange d
GROUP BY t.position, d.dd
ORDER BY t.position, d.dd;

:

POSITION         DATE          STAFFING
---------------- ------------- ---------
bottle washer    01/01/2013    0
bottle washer    01/02/2013    1
bottle washer    01/03/2013    1
bottle washer    01/04/2013    2
bottle washer    01/05/2013    2
bottle washer    01/06/2013    1
bottle washer    01/07/2013    1
bottle washer    01/08/2013    0
bottle washer    01/09/2013    0
bottle washer    01/10/2013    1
bottle washer    01/11/2013    1
bottle washer    01/12/2013    2
bottle washer    01/13/2013    2
bottle washer    01/14/2013    1
bottle washer    01/15/2013    1
cook             01/01/2013    1
cook             01/02/2013    1
cook             01/03/2013    1
cook             01/04/2013    0
cook             01/05/2013    1
cook             01/06/2013    1
cook             01/07/2013    2
cook             01/08/2013    2
cook             01/09/2013    1
cook             01/10/2013    1
cook             01/11/2013    1
cook             01/12/2013    1
cook             01/13/2013    1
cook             01/14/2013    1
cook             01/15/2013    1
+2

( ..), . , ( MS Access SQL Server, , Oracle, SQL)

, tblCal, CalDate - .

SELECT tblEmps.Position,tblCal.CalDate,count(tblEmps.Name) As Pos
FROM tblCal INNER JOIN 
    tblEmps On (tblCal.CalDate >= tblEmps.Start And tblCal.CalDate <= tblEmps.End)
GROUP BY tblEmps.Position,tblCal.CalDate

0:

SELECT a.Dt,a.Position,COALESCE(b.Pos,0) As Staff
FROM (SELECT tblCal.Dt, tblEmps.Position FROM tblCal, tblEmps) As a
    LEFT JOIN (SELECT tblEmps.Position, tblCal.CalDate, count(tblEmps.Name) As Pos
        FROM tblCal INNER JOIN 
            tblEmps On (tblCal.CalDate >= tblEmps.Start And tblCal.CalDate <= 
                tblEmps.End)
        GROUP BY tblEmps.Position,tblCal.CalDate) As b
    ON (a.Dt = b.Dt And a.Position = b.Position)

Obviously, it is assumed that all the positions required in the request at some point had an employee.

+1
source

More articles:


All Articles