CODEWITHSUNDEEP
sql
Benjol
Benjol

Reputation: 66551

Skipping rows in sql query (finding end date based on start date and worked days)

Given these two (simplified) tables:

Task  (list of tasks/employee with their start date and estimated cost)
---------
TaskId: int
EmpId: int
Start: Date
Days: int

WorkableDays (list of working dates/employee - i.e., without weekends/holidays)
---------
EmpId: int
Day: Date

Is there any way to get this result using just Access SQL (or any other SQL for that matter)?

TaskId, EmpId, EndDate

EDIT: if it simplifies things any, it's one employee per task (TaskId is the unique key here, not TaskId+EmpId)

(I'm including the EmpId for completeness sake, I'm not sure it's pertinent to the question)

Note: I think I'm pushing my luck with this one, but I'm trying to work out if I can do it just in SQL.

Upvotes: 2

Views: 382

Answers (3)

Andomar
Andomar

Reputation: 238116

You could have a where clause that says there must be N working days between the start and the end day. Unlike the row_number() variants, this should work in MS Access. For example:

declare @Task table (taskid int, empid int, start date, days int)
insert @Task values (1, 1, '2010-01-01', 1)
insert @Task values (2, 1, '2010-01-01', 2)
insert @Task values (3, 1, '2010-01-01', 3)

declare @WorkableDays table (empid int, day date)
insert @WorkableDays values (1, '2010-01-01')
insert @WorkableDays values (1, '2010-01-02')
insert @WorkableDays values (1, '2010-01-05')

select  t.taskid
,       t.start
,       endday.day as end
from    @Task t
join    @WorkableDays endday
on      endday.empid = t.empid
where   t.days = 
        (
        select  COUNT(*)
        from    @WorkableDays wd
        where   wd.empId = t.empId
                and wd.day between t.start and endday.day
        )

This prints:

taskid   start       end
1        2010-01-01  2010-01-01
2        2010-01-01  2010-01-02
3        2010-01-01  2010-01-05

Upvotes: 2

Matt Gibson
Matt Gibson

Reputation: 38238

Don't know about Access, I'm afraid, but in T-SQL, I'd do something like this (for employee 1, task 1, in this example):

SELECT 
    TaskId,
    EmpId,
    Day AS EndDate 
FROM
    (
        SELECT 
            task.TaskId,
            task.EmpId,
            task.Days,
            WorkableDays.Day,
            RANK() OVER (PARTITION BY task.EmpID, task.TaskID ORDER BY Day ASC) 'TaskActualDayNumber'
        FROM 
            task 
                INNER JOIN WorkableDays ON task.empID = WorkableDays.empID AND WorkableDays.Day >= task.Start
        WHERE
            task.EmpID = 1 AND
            task.TaskID = 1
    ) CalculateDayNumbers
WHERE
    Days = TaskActualDayNumber

The inner query will rank the days in the future of the task in ascending order of the available days in WorkableDays, therefore projecting a "day of this task" value forward across all future dates from the start date. Then the outer query just selects the one value where that day number coincides with the number of days estimated for the task.

EDIT: As Michael Pakhantsov points out, ROW_NUMBER() will produce the same results as RANK() in this case, I think.

Upvotes: 0

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25380

in t-SQL

     SELECT o.TaskId, o.EmpId, o.Date
FROM
(
SELECT TaskId, EmpId, t.Days, w.date, ROW_NUMBER() OVER(PARTITION BY w.EmpId order BY w.Date) DayNumber 
FROM Task t, DayNumberWorkableDays w
WHERE t.EmpId = w.EmpId
AND w.Date >= t.Start
) o
WHERE o.DayNumber = o.Days

Upvotes: 0

Related Questions