Calculating Dates

1

Pasted image1327×496 115 KB

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 NULL,
[SubAss] VARCHAR 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);

Any Idea on how this can be done?

2

Try something like:

DROP TABLE IF EXISTS dbo.TEST
GO

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())
   , (136855,88479,2,'M','1',5,NULL,GETDATE(),NULL)
   , (136855,88479,3,'M','1',5,NULL,GETDATE(),NULL)
   , (136855,88479,1,'GA','1.1',5,GETDATE(),GETDATE(),GETDATE())
   , (136855,88479,2,'GA','1.1',5,NULL,GETDATE(),NULL)
   , (136855,88479,3,'GA','1.1',5,NULL,GETDATE(),NULL)
   , (136855,88479,1,'GB','1.1.1',5,GETDATE(),GETDATE(),GETDATE())
   , (136855,88479,2,'GB','1.1.1',5,NULL,GETDATE(),NULL)
   , (136855,88479,3,'GB','1.1.1',5,NULL,GETDATE(),NULL)
   , (136855,88479,1,'GC','1.2',5,GETDATE(),GETDATE(),GETDATE())
   , (136855,88479,2,'GC','1.2',5,NULL,GETDATE(),NULL)
   , (136855,88479,3,'GC','1.2',5,NULL,GETDATE(),NULL);

SELECT 
    TravLotID
  , LotID
  , RowNum
  , TravType
  , SubAss
  , CycleTime
  , DateTimeOut
  , SnapShotDate
  , Max(IsNull(FordwardSchdDate, DateAdd(mi, CycleTime, SnapShotDate))) OVER (PARTITION BY SubAss)  FordwardSchdDate
FROM dbo.TEST;

DROP TABLE IF EXISTS dbo.TEST
GO