SQLTeam.com | Weblogs | Forums

Query Question


#1

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!


#2

Assuming CapcityValue is defined as type datetime:

alter table mytable
add newcolumn as dateadd(minute, ProjectCycleTime, CapcityValue)

#3

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,


#4

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?


#5

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


#6

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?


#7

Sorry maybe I wasn't clear but it needs to be the way I just provided. I need the results to be cumulative


#8

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 ???