CODEWITHSUNDEEP
sql-server
KMBonin
KMBonin

Reputation: 111

SQL Server CTE Use Previous Computed Date as Next Start Date

I have a table that holds tasks. Each task has an allotted number of hours that it's supposed to take to complete the task.

I'm storing the data in a table, like so:

declare @fromtable table (recordid int identity(1,1), orderdate date, deptid int, task varchar(500), estimatedhours int);

I also have a function that calculates the completion date of the task, based on the start date, estimated hours, and department, and some other math that computes headcount, hours available to work, etc. 

dbo.fn_getCapEndDate(aStartDate,estimatedHours,deptID)

I need to generate the start and end date for each record in @fromtable. The first record will start with column orderdate as the start date for the computation, then each subsequent record will use the previous record's computedEndDate as their start date.

What I'm trying to achieve:

enter image description here

Here's what I have started with:

with MyCTE as
(
   select mt.recordID, mt.deptID, mt.estimatedhours, mt.JobNumber, ROW_NUMBER() over (order by recordID) as RowNum,
   convert(date,mt.orderdate) as computedStart, 
   case when mt.recordID = 1 then convert(date,dbo.fn_getCapEndDate(mt.orderdate,mt.estimatedhours,mt.deptid)) end as computedEnd
   from @fromtable mt  
)

select c1.*, c2.recordID, 
case when c2.recordid is null then c1.computedStart else c2.computedEnd end as StartDate,
case when c2.recordid is null then c1.computedEnd else dbo.fn_getCapEndDate(c2.computedEnd,c1.estimatedhours,c1.deptid) end as computedEnd
from MyCTE c1
left join MyCTE c2 on c1.RowNum = c2.RowNum + 1;

With this, the first two columns have the correct start/end dates. Every column after that computes NULL for its start and end values. It "loses" the value of the previous column's computed end date.

What can I do to fix the issue and return the values as needed?

EDIT: Sample data in text format:

estimatedhours  OrderDate
0    1/1/2017
0    1/1/2017
0    1/1/2017
0    1/1/2017
500  1/1/2017
32   1/1/2017
0    1/1/2017
0    1/1/2017
320  1/1/2017
0    1/1/2017
5    1/1/2017
0    1/1/2017
4    1/1/2017

Upvotes: 0

Views: 72

Answers (1)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

You can use lead as below:

select RecordId, EstimatedHours, StartDate,
   ComputedEnd = LEAD(StartDate) over (order by RecordId)
From yourTable

Upvotes: 1

Related Questions