Calculating Dates

Question

Calculating Dates

ajkarora

SSChasing Mays

Points: 601
More actions
January 31, 2017 at 12:18 pm

#401525

I have a table with the following information
I want to update the Fordward Schd Date Column where the values are Null.
The logic is to update the column for SubAss 1.2 first, then 1.1.1 then 1.1 and lastly 1.
For 1.2 it’ll take the snapshot date and add CycleTime to get the FordSchd Date value. Then code will take the max value from GC and get values for GB. Then get the max value for GB and get Fordward Schd for GA and then lastly get Max Value from GA to calculate Fordward Schd Date for M. So this is how it should look like
I am able to figure this out without looking into SubAss I did something like this
SET @DATE = DATEADD (MINUTE, CycleTime,SnapShotDate)
But I am not sure how to setup the logic from bottom to top. Any ideas?
c

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

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

Joe Torre SSChampion Points: 10248 More actions · Answer 1

ajkarora - Tuesday, January 31, 2017 12:18 PM
I have a table with the following information
I want to update the Fordward Schd Date Column where the values are Null.
The logic is to update the column for SubAss 1.2 first, then 1.1.1 then 1.1 and lastly 1.
For 1.2 itâ€™ll take the snapshot date and add CycleTime to get the FordSchd Date value. Then code will take the max value from GC and get values for GB. Then get the max value for GB and get Fordward Schd for GA and then lastly get Max Value from GA to calculate Fordward Schd Date for M. So this is how it should look like
I am able to figure this out without looking into SubAss I did something like this
SET @DATE = DATEADD (MINUTE, CycleTime,SnapShotDate)
But I am not sure how to setup the logic from bottom to top. Any ideas?
c

If you post a create table statement and insert statements perhaps someone will workout an answer for you.

ajkarora SSChasing Mays Points: 601 More actions · Answer 2

Joe Torre - Tuesday, January 31, 2017 1:17 PM
ajkarora - Tuesday, January 31, 2017 12:18 PM
I have a table with the following information
I want to update the Fordward Schd Date Column where the values are Null.
The logic is to update the column for SubAss 1.2 first, then 1.1.1 then 1.1 and lastly 1.
For 1.2 itâ€™ll take the snapshot date and add CycleTime to get the FordSchd Date value. Then code will take the max value from GC and get values for GB. Then get the max value for GB and get Fordward Schd for GA and then lastly get Max Value from GA to calculate Fordward Schd Date for M. So this is how it should look like
I am able to figure this out without looking into SubAss I did something like this
SET @DATE = DATEADD (MINUTE, CycleTime,SnapShotDate)
But I am not sure how to setup the logic from bottom to top. Any ideas?
c
If you post a create table statement and insert statements perhaps someone will workout an answer for you.

Here's the Query that I created

DECLARE
        @TravLotID2 AS INT,
        @ProcessID AS INT,
        @Date AS DATETIME,
        @ProjectedCycleTime as INT,
        @SnapShotDate DATETIME = GETDATE()

SELECT                TL.TravLotID,
                    TL.LotID,
                    TD.RowNum,
                    TL.TravType,
                    TL.SubAss,
                    dbo.harbor_fn_ProcessRunTime(TL.TravLotID, p.ProcessID, tdmt.ManualRunTime, 1) AS ProjectedCycleTime,
                    (SELECT MAX(ms.DateTimeIn) FROM TravelerLot t1
                    LEFT JOIN MoveStat ms ON TL.TravLotID = ms.TravLotID AND P.ProcessID = ms.ProcessID) AS DateTimeIn,
                    (SELECT MAX(ms.DateTimeOut)
                    FROM TravelerLot t1
                    LEFT JOIN MoveStat ms ON TL.TravLotID = ms.TravLotID AND P.ProcessID = ms.ProcessID) AS DateTimeOut,
                    GETDATE() AS SnapShotDate

INTO #TEMPTABLE
            FROM  TravelerLot AS TL
            LEFT JOIN STATUS s1 ON s1.statusid = TL.statusid
            LEFT JOIN TravDetail AS td ON tl.TravID = td.TravID
            INNER JOIN WorkOrderLot AS wol ON tl.LotID = wol.LotID
            LEFT JOIN Process AS p ON td.ProcessID = p.ProcessID
             LEFT JOIN harbor_TravDetailManualRunTime tdmt ON tl.TravLotID = tdmt.TravLotID
                           AND td.TravLineNum = tdmt.TravLineNum
             WHERE SubASS != '999' AND s1.StatusDesc = 'Active' AND tl.lotid = 88479 --REMOVE LOTID WHEN GOING LIVE
             ORDER BY TL.LotID, TL.SubAss, tl.TravLotID

ALTER TABLE #TEMPTABLE
ADD ForwardSchdDate DATETIME;

UPDATE #TEMPTABLE SET ForwardSchdDate = DateTimeOut

WHILE (isnull((Select COUNT (*) FROM #TempTable WHERE ForwardSchdDate IS NULL),0) > 0 )
BEGIN
Select Top 1    @TravLotID2 = TravLotID,
                @ProcessID = ProcessID,
                @RowNum = rownum,
                @ProjectedCycleTime = ProjectedCycleTime
FROM #TempTable
where ForwardSchdDate IS NULL
Order By RowNum

-- PRINT 'ROWNUM ' + CONVERT(Varchar(10),@RowNum)
SET @Date = (Select ForwardSchdDate From #TempTable where RowNum = (@RowNum - 1) AND TravlotID = @TravLotID2)
-- PRINT '@DATE ' + CONVERT(Varchar(10),@DATE)
SET @DATE = DATEADD (MINUTE,@ProjectedCycleTime,@SnapShotDate)--@Date)
-- PRINT '@DATE ' + CONVERT(Varchar(10),@DATE)
UPDATE #TempTable SET ForwardSchdDate = @Date where RowNum=@RowNum AND TravlotID = @TravLotID2
END

SELECT * FROM #TEMPTABLE
ORDER BY SubAss DESC

Thom A SSC Guru Points: 99130 More actions · Answer 3

Is that your solution or? You haven't given us DDL and DLM, as Joe suggested, so the above query doesn't help us.We don't have access to your data, so running it going to not help us to help you.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ajkarora SSChasing Mays Points: 601 More actions · Answer 4

Thom A - Tuesday, January 31, 2017 1:42 PM
Is that your solution or? You haven't given us DDL and DLM, as Joe suggested, so the above query doesn't help us.We don't have access to your data, so running it going to not help us to help you.

That's not the complete solution. I'm going based on that Query to get the information I need. However, Here's the DDL for table and INSERT date

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TEST](
       [TravLotID] [INT] NULL,
       [LotID] [INT] NULL,
       [RowNum] [INT] NULL,
       [TravType] [VARCHAR](2) NULL,
       [SubAss] [VARCHAR](15) NULL,
       [CycleTime] [INT] NULL,
       [DateTimeOut] [DATETIME] NULL,
       [SnapShotDate] [DATETIME] NULL,
       [FordwardSchdDate] [DATETIME] NULL
) ON [PRIMARY]

GO

Here's the insert query

INSERT INTO dbo.test VALUES (136855,88479,1,'M','1',5,GETDATE(),GETDATE(),GETDATE());
INSERT INTO dbo.test VALUES (136855,88479,2,'M','1',5,NULL,GETDATE(),NULL);
INSERT INTO dbo.test VALUES (136855,88479,3,'M','1',5,NULL,GETDATE(),NULL);
INSERT INTO dbo.test VALUES (136855,88479,1,'GA','1.1',5,GETDATE(),GETDATE(),GETDATE());
INSERT INTO dbo.test VALUES (136855,88479,2,'GA','1.1',5,NULL,GETDATE(),NULL);
INSERT INTO dbo.test VALUES (136855,88479,3,'GA','1.1',5,NULL,GETDATE(),NULL);
INSERT INTO dbo.test VALUES (136855,88479,1,'GB','1.1.1',5,GETDATE(),GETDATE(),GETDATE());
INSERT INTO dbo.test VALUES (136855,88479,2,'GB','1.1.1',5,NULL,GETDATE(),NULL);
INSERT INTO dbo.test VALUES (136855,88479,3,'GB','1.1.1',5,NULL,GETDATE(),NULL);
INSERT INTO dbo.test VALUES (136855,88479,1,'GC','1.2',5,GETDATE(),GETDATE(),GETDATE());
INSERT INTO dbo.test VALUES (136855,88479,2,'GC','1.2',5,NULL,GETDATE(),NULL);
INSERT INTO dbo.test VALUES (136855,88479,3,'GC','1.2',5,NULL,GETDATE(),NULL);

Thanks!