T-SQL to add 7 days to ship date

• SSRS Newbie

  Hall of Fame

  Points: 3244

  More actions

  July 29, 2014 at 3:00 pm

  #1733397

  In the below DDL, I have 2 jobs (J066811 & J066594) which show NEXT_SHIP_DATE as (2014-07-29 & 2014-08-04) instead of 2014-07-31. We ship the jobs only on THURSDAY of every week. I've also attached the screenshot indicating the dates for above 2 jobs.

  USE tempdb;

  GO

  DECLARE @Date DATE;

  SET @Date = GETDATE();

  --SET @Date = '2014-07-25';

  DECLARE @TEST_DATA TABLE

  (

  DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

  ,JobNumberVARCHAR(10) NOT NULL

  ,JobStatus CHAR(1) NOT NULL

  ,ExpectedDate VARCHAR(10) NOT NULL

  ,LastShippedDate VARCHAR(10) NULL

  );

  INSERT INTO @TEST_DATA (JobStatus, JobNumber,ExpectedDate,LastShippedDate)

  VALUES

  ('S', 'J012345','2014-06-26','2014-07-03')

  ,('O', 'J012346','2014-07-03',NULL)

  ,('O', 'J012347','2014-07-03',NULL)

  ,('O', 'J012348','2014-07-10',NULL)

  ,('O', 'J012349','2014-07-10',NULL)

  ,('O', 'J012350','2014-07-10',NULL)

  ,('O', 'J012351','2014-07-10',NULL)

  ,('O', 'J012352','2014-07-17',NULL)

  ,('O', 'J012353','2014-07-17',NULL)

  ,('O', 'J012354','2014-07-17',NULL)

  ,('O', 'J012355','2014-07-17',NULL)

  ,('O', 'J012356','2014-07-17',NULL)

  ,('O', 'J012357','2014-07-24',NULL)

  ,('O', 'J012358','2014-07-24',NULL)

  ,('O', 'J012359','2014-07-24',NULL)

  ,('O', 'J012360','2014-07-24',NULL)

  ,('O', 'J012361','2014-07-24',NULL)

  ,('O', 'J012362','2014-07-31',NULL)

  ,('O', 'J066811','2014-07-29',NULL)

  ,('O', 'J066594','2014-07-28',NULL);

  SELECT

  J.DT_ID

  ,J.JobStatus

  ,J.JobNumber

  ,J.ExpectedDate

  ,J.LastShippedDate

  ,CASE

  /* SHIPPED - SHOW LastShippedDate */

  WHEN j.JobStatus ='S' THEN Cast(j.LastShippedDate as DATE)

  /* MISSED SHIPMENT BY 1 TO 5 DAYS */

  WHEN j.JobStatus <>'S' AND DATEDIFF(DAY,CAST(J.ExpectedDate AS DATE), @Date) BETWEEN 1 AND 5 THEN

  CASE

  /* SET SATURDAY AND SUNDAY SHIPMENTS TO NEXT FRIDAY */

  WHEN DATEDIFF(dd,0,CAST(J.ExpectedDate AS DATE))%7 = 5THEN DATEADD(DAY,5,CAST(J.ExpectedDate AS DATE))

  WHEN DATEDIFF(dd,0,CAST(J.ExpectedDate AS DATE))%7 = 4THEN DATEADD(DAY,6,CAST(J.ExpectedDate AS DATE))

  /* ADD 7 DAYS TO THE OTHER */

  ELSE DATEADD(DAY,7,CAST(J.ExpectedDate AS DATE))

  END

  /* MISSED SHIPMENT BY MORE THAN 5 DAYS */

  WHEN j.JobStatus <>'S' AND DATEDIFF(DAY,CAST(J.ExpectedDate AS DATE), @Date) > 5 THEN

  CASE

  /* SET SHIPMENTS TO NEXT FRIDAY EXEPT FRIDAY GOES TO MONDAY */

  WHEN DATEDIFF(dd,0,@Date)%7 = 6THEN DATEADD(DAY,4,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 5THEN DATEADD(DAY,5,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 0THEN DATEADD(DAY,3,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 1THEN DATEADD(DAY,2,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 2THEN DATEADD(DAY,1,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 3THEN DATEADD(DAY,0,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 4THEN DATEADD(DAY,3,@Date)

  END

  /* FUTURE SHIPMENTS */

  ELSE CAST(J.ExpectedDate AS DATE)

  END AS NEXT_SHIP_DATE

  FROM @TEST_DATA J

• SSRS Newbie

  Hall of Fame

  Points: 3244

  More actions

  July 29, 2014 at 3:03 pm

  #1733398

  Apologies for reposting the above question. I was not able to see the post after refreshing the page that's why I ended up posting multiple times.

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  July 29, 2014 at 10:04 pm

  #1733451

  amolacp (7/29/2014)

  ---

  Apologies for reposting the above question. I was not able to see the post after refreshing the page that's why I ended up posting multiple times.

  No worries, I think I got it in the end:-D

  Here is the simplified version which sets all missed dates to the coming Thursday.

  😎

  USE tempdb;

  GO

  DECLARE @Date DATE;

  SET @Date = GETDATE();

  --SET @Date = '2014-07-25';

  DECLARE @TEST_DATA TABLE

  (

  DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

  ,JobNumberVARCHAR(10) NOT NULL

  ,JobStatus CHAR(1) NOT NULL

  ,ExpectedDate VARCHAR(10) NOT NULL

  ,LastShippedDate VARCHAR(10) NULL

  );

  INSERT INTO @TEST_DATA (JobStatus, JobNumber,ExpectedDate,LastShippedDate)

  VALUES

  ('S', 'J012345','2014-06-26','2014-07-03')

  ,('O', 'J012346','2014-07-03',NULL)

  ,('O', 'J012347','2014-07-03',NULL)

  ,('O', 'J012348','2014-07-10',NULL)

  ,('O', 'J012349','2014-07-10',NULL)

  ,('O', 'J012350','2014-07-10',NULL)

  ,('O', 'J012351','2014-07-10',NULL)

  ,('O', 'J012352','2014-07-17',NULL)

  ,('O', 'J012353','2014-07-17',NULL)

  ,('O', 'J012354','2014-07-17',NULL)

  ,('O', 'J012355','2014-07-17',NULL)

  ,('O', 'J012356','2014-07-17',NULL)

  ,('O', 'J012357','2014-07-24',NULL)

  ,('O', 'J012358','2014-07-24',NULL)

  ,('O', 'J012359','2014-07-24',NULL)

  ,('O', 'J012360','2014-07-24',NULL)

  ,('O', 'J012361','2014-07-24',NULL)

  ,('O', 'J012362','2014-07-31',NULL)

  ,('O', 'J066811','2014-07-29',NULL)

  ,('O', 'J066594','2014-07-28',NULL);

  SELECT

  J.DT_ID

  ,J.JobStatus

  ,J.JobNumber

  ,J.ExpectedDate

  ,J.LastShippedDate

  ,CASE

  /* SHIPPED - SHOW LastShippedDate */

  WHEN j.JobStatus ='S' THEN Cast(j.LastShippedDate as DATE)

  /* MISSED SHIPMENT SET TO NEXT THURSDAY */

  WHEN j.JobStatus <>'S' AND DATEDIFF(DAY,CAST(J.ExpectedDate AS DATE), @Date) > 0 THEN

  CASE

  /* SET SHIPMENTS TO NEXT THURSDAY */

  WHEN DATEDIFF(dd,0,@Date)%7 = 0THEN DATEADD(DAY,3,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 1THEN DATEADD(DAY,2,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 2THEN DATEADD(DAY,1,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 3THEN DATEADD(DAY,7,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 4THEN DATEADD(DAY,3,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 6THEN DATEADD(DAY,4,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 5THEN DATEADD(DAY,5,@Date)

  END

  /* FUTURE SHIPMENTS */

  ELSE CAST(J.ExpectedDate AS DATE)

  END AS NEXT_SHIP_DATE

  FROM @TEST_DATA J

• SSRS Newbie

  Hall of Fame

  Points: 3244

  More actions

  July 30, 2014 at 7:07 am

  #1733569

  Thank you very much for your help. The code you sent works for me. The only problem is when I set @Date=2014-08-01 the Next_ShipDate shows 2014-08-04 instead of 2014-08-07 (Thursday). Since we ship only on Thursday. Here is the DDL. Thanks again for your help.

  USE tempdb;

  GO

  DECLARE @Date DATE;

  /*SET @Date = GETDATE();*/

  SET @Date = '2014-08-01';

  DECLARE @TEST_DATA TABLE

  (

  DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

  ,JobNumberVARCHAR(10) NOT NULL

  ,JobStatus CHAR(1) NOT NULL

  ,ExpectedDate VARCHAR(10) NOT NULL

  ,LastShippedDate VARCHAR(10) NULL

  );

  INSERT INTO @TEST_DATA (JobStatus, JobNumber,ExpectedDate,LastShippedDate)

  VALUES

  ('S', 'J012345','2014-06-26','2014-07-03')

  ,('O', 'J012346','2014-07-03',NULL)

  ,('O', 'J012347','2014-07-03',NULL)

  ,('O', 'J012348','2014-07-10',NULL)

  ,('O', 'J012349','2014-07-10',NULL)

  ,('O', 'J012350','2014-07-10',NULL)

  ,('O', 'J012351','2014-07-10',NULL)

  ,('O', 'J012352','2014-07-17',NULL)

  ,('O', 'J012353','2014-07-17',NULL)

  ,('O', 'J012354','2014-07-17',NULL)

  ,('O', 'J012355','2014-07-17',NULL)

  ,('O', 'J012356','2014-07-17',NULL)

  ,('O', 'J012357','2014-07-24',NULL)

  ,('O', 'J012358','2014-07-24',NULL)

  ,('O', 'J012359','2014-07-24',NULL)

  ,('O', 'J012360','2014-07-24',NULL)

  ,('O', 'J012361','2014-07-24',NULL)

  ,('O', 'J012362','2014-07-31',NULL)

  ,('O', 'J066811','2014-07-29',NULL)

  ,('O', 'J066594','2014-07-28',NULL)

  ,('O', 'J066595','2014-07-30',NULL);

  SELECT

  J.DT_ID

  ,J.JobStatus

  ,J.JobNumber

  ,J.ExpectedDate

  ,J.LastShippedDate

  ,CASE

  /* SHIPPED - SHOW LastShippedDate */

  WHEN j.JobStatus ='S' THEN Cast(j.LastShippedDate as DATE)

  /* MISSED SHIPMENT SET TO NEXT THURSDAY */

  WHEN j.JobStatus <>'S' AND DATEDIFF(DAY,CAST(J.ExpectedDate AS DATE), @Date) >= 0 THEN

  CASE

  /* SET SHIPMENTS TO NEXT THURSDAY */

  WHEN DATEDIFF(dd,0,@Date)%7 = 0THEN DATEADD(DAY,3,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 1THEN DATEADD(DAY,2,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 2THEN DATEADD(DAY,1,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 3THEN DATEADD(DAY,7,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 4THEN DATEADD(DAY,3,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 6THEN DATEADD(DAY,4,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 5THEN DATEADD(DAY,5,@Date)

  END

  /* FUTURE SHIPMENTS */

  ELSE CAST(J.ExpectedDate AS DATE)

  END AS NEXT_SHIP_DATE

  FROM @TEST_DATA J

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  July 30, 2014 at 2:13 pm

  #1733807

  Somehow messed up the order of the days, here is the correct code

  😎

  USE tempdb;

  GO

  DECLARE @Date DATE;

  /*SET @Date = GETDATE();*/

  SET @Date = '2014-08-07';

  DECLARE @TEST_DATA TABLE

  (

  DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

  ,JobNumberVARCHAR(10) NOT NULL

  ,JobStatus CHAR(1) NOT NULL

  ,ExpectedDate VARCHAR(10) NOT NULL

  ,LastShippedDate VARCHAR(10) NULL

  );

  INSERT INTO @TEST_DATA (JobStatus, JobNumber,ExpectedDate,LastShippedDate)

  VALUES

  ('S', 'J012345','2014-06-26','2014-07-03')

  ,('O', 'J012346','2014-07-03',NULL)

  ,('O', 'J012347','2014-07-03',NULL)

  ,('O', 'J012348','2014-07-10',NULL)

  ,('O', 'J012349','2014-07-10',NULL)

  ,('O', 'J012350','2014-07-10',NULL)

  ,('O', 'J012351','2014-07-10',NULL)

  ,('O', 'J012352','2014-07-17',NULL)

  ,('O', 'J012353','2014-07-17',NULL)

  ,('O', 'J012354','2014-07-17',NULL)

  ,('O', 'J012355','2014-07-17',NULL)

  ,('O', 'J012356','2014-07-17',NULL)

  ,('O', 'J012357','2014-07-24',NULL)

  ,('O', 'J012358','2014-07-24',NULL)

  ,('O', 'J012359','2014-07-24',NULL)

  ,('O', 'J012360','2014-07-24',NULL)

  ,('O', 'J012361','2014-07-24',NULL)

  ,('O', 'J012362','2014-07-31',NULL)

  ,('O', 'J066811','2014-07-29',NULL)

  ,('O', 'J066594','2014-07-28',NULL)

  ,('O', 'J066595','2014-07-30',NULL);

  SELECT

  J.DT_ID

  ,J.JobStatus

  ,J.JobNumber

  ,J.ExpectedDate

  ,J.LastShippedDate

  ,DATEDIFF(dd,0,@Date)%7

  ,CASE

  /* SHIPPED - SHOW LastShippedDate */

  WHEN j.JobStatus ='S' THEN Cast(j.LastShippedDate as DATE)

  /* MISSED SHIPMENT SET TO NEXT THURSDAY */

  WHEN j.JobStatus <>'S' AND DATEDIFF(DAY,CAST(J.ExpectedDate AS DATE), @Date) >= 0 THEN

  CASE

  /* SET SHIPMENTS TO NEXT THURSDAY */

  WHEN DATEDIFF(dd,0,@Date)%7 = 0THEN DATEADD(DAY,3,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 1THEN DATEADD(DAY,2,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 2THEN DATEADD(DAY,1,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 3THEN DATEADD(DAY,7,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 4THEN DATEADD(DAY,6,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 5THEN DATEADD(DAY,5,@Date)

  WHEN DATEDIFF(dd,0,@Date)%7 = 6THEN DATEADD(DAY,4,@Date)

  END

  /* FUTURE SHIPMENTS */

  ELSE CAST(J.ExpectedDate AS DATE)

  END AS NEXT_SHIP_DATE

  FROM @TEST_DATA J

• ZZartin

  SSC-Dedicated

  Points: 30920

  More actions

  July 30, 2014 at 3:48 pm

  #1733853

  SELECT CASE WHEN DATEPART(weekday, getdate()) < 5

  THEN DATEADD(day, 4, DATEADD(week, datediff(week, '1/6/2013', getdate()), '1/6/2013'))

  ELSE DATEADD(day, 11, DATEADD(week, datediff(week, '1/5/2014', getdate()), '1/5/2014')) END

  This should always get you the next thursday date, change the DATEPART(weekday, getdate()) < 5 to DATEPART(weekday, getdate()) <= 5 if you want to keep thursday dates as is.

• LutzM

  SSC Guru

  Points: 107049

  More actions

  July 30, 2014 at 3:53 pm

  #1733856

  at ZZartin:

  Pleae run your code with the following line at the very beginning:

  SET DATEFIRST 4

  Strong hint: DATEFIRST is not deterministic...

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• ZZartin

  SSC-Dedicated

  Points: 30920

  More actions

  July 30, 2014 at 4:05 pm

  #1733859

  Fair enough, if for whatever reason the default is changed.

  Replace that with

  (DATEDIFF(day, '1/6/2013', getdate()) % 7) < 4