Query Question
-
Hello All,
Please see the image of what I am trying to accomplish
I tried to add the following code to get the value for FordwardSchdDate
( CASE WHEN wt.TravLineNum = 1
THEN dbo.harbor_fn_DateAdjustWorkingDaysOnly(0 - wt.ProjectedCycleTime, wt.CapacitySlotDateTimeValue)
ELSE dateadd(minute, SUM(wt.ProjectedCycleTime) OVER(Partition by wt.ProjectedCycleTime), wt.CapacitySlotDateTimeValue)
END ) AS ForwardSchdDate
This logic works fine for the first value when TravLineNum is 1. However, the cumulative dates are not calculating up correctly. Does anyone have any suggestions on how I can fix this?
-
Would you be open to try the Quirky Update?
-
Advanced windowing functions, LAG/LEAD, since this appears to be a SQL Server 2014 question.
-
This is an option using windowing functions.
This is also an example on how you should post sample data.
CREATE TABLE #Sample(
TravLotID int,
TravLineNum int,
ProyectCycleTime int,
CapacityValue datetime,
FordwardSchdDate datetime,
CONSTRAINT PK_Sample PRIMARY KEY CLUSTERED (TravLotID, TravLineNum)
);
INSERT INTO #Sample(TravLotID, TravLineNum,ProyectCycleTime,CapacityValue)
VALUES(14405, 1, 60, '20110316 16:00:00'), (14405, 2, 90, NULL);
WITH CTE AS(
SELECT *,
DATEADD(MI, SUM(ProyectCycleTime) OVER(PARTITION BY TravLotID
ORDER BY TravLineNum
ROWS UNBOUNDED PRECEDING),
FIRST_VALUE(CapacityValue) OVER(PARTITION BY TravLotID
ORDER BY TravLineNum
ROWS UNBOUNDED PRECEDING)) AS CalcFordwardSchdDate
FROM #Sample
)
UPDATE CTE
SET FordwardSchdDate = CalcFordwardSchdDate;
SELECT *
FROM #Sample;
GO
DROP TABLE #Sample;
-
so sorry but I posted in SQL Sever 2014 by mistake. Will this work on SQL Server 2008?
-
ajkarora (11/15/2016)
so sorry but I posted in SQL Sever 2014 by mistake. Will this work on SQL Server 2008?No, it has functionality that was introduced on 2012.
Have you read the article I posted?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply