Query Question

Question

Query Question

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Would you be open to try the Quirky Update?

http://www.sqlservercentral.com/articles/T-SQL/68467/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Advanced windowing functions, LAG/LEAD, since this appears to be a SQL Server 2014 question.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ajkarora SSChasing Mays Points: 601 More actions · Answer 4

so sorry but I posted in SQL Sever 2014 by mistake. Will this work on SQL Server 2008?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

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?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2