Hello All,
I have data with the following information
Three columns ID, ProjectCycleTime(Mins), CapcityValue
Values for IDS are
2006958
2006958
2006958
Values for ProjectCycleTime(Mins are
121
470
15
Values for CapcityValue are
9/9/16 17:00
9/9/16 17:00
9/9/16 17:00
I need to create a new column which takes the first value of CapcityValue and adds from ProjectCycleTime.
So for example: (9/9/16 17:00 + 121) then for the second record I take the value and add 470.
Can someone please guide me on how I can add that to SQL database?
Thank you!
Assuming CapcityValue is defined as type datetime:
alter table mytable
add newcolumn as dateadd(minute, ProjectCycleTime, CapcityValue)
Thank you! Any idea how the logic should be to get the calculation correct?
This is what I have so far
( CASE WHEN wt.SchedulingType = 'Front Loaded'
AND wt.ChildExists = 0
THEN dbo.harbor_fn_DateAdjustWorkingDaysOnly(0 - wt.ProjectedCycleTime, wt.CapacitySlotDateTimeValue)
ELSE dbo.harbor_fn_DateAdjustWorkingDaysOnly(NOT SURE WHAT TO DO HERE)
END ) AS ForwardSchdDate,
Not sure what this function does:
dbo.harbor_fn_DateAdjustWorkingDaysOnly
Does my computed column not produce correct results? If not, how does it differ from what you want?
Yea, the information you provided doesn't show the correct results.
For example
I have the following values
ProjectCycleTime
60
90
CapacityValue
2011-03-16 16:00:00:000
From your logic I get the following results for FordwardSchdDate
FordwardSchdDate
2011-03-16 17:00:00.000
2011-03-16 17:30:00.000
The first record is correct but the second record should show up as 2011-03-16 18:30:00.000
Wait, in your original post, you had
Values for ProjectCycleTime(Mins are
121
470
15
Values for CapcityValue are
9/9/16 17:00
9/9/16 17:00
9/9/16 17:00
and wanted to add 121 for the first and 470 for the second. But CapcityValue is the same in all cases.
Now it seems like you want the results to be cumulative.
Which is it?
Sorry maybe I wasn't clear but it needs to be the way I just provided. I need the results to be cumulative
OK I think we can do this with a window function, but we can't do it in DML
So you'll need a query that has:
dateadd(minute, SUM(ProjectCycleTime) OVER(Partition by ID order by ???), CapcityValue)
But we'll need something for the ???