Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

T-SQL to add 7 days to ship date Expand / Collapse
Author
Message
Posted Saturday, July 26, 2014 5:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:10 AM
Points: 25, Visits: 159
Hello All,

A quick question to all regarding my query. Below statement adds 7 days to the shipment date if we miss the ship date. For example something that was suppose to ship on 07/24/2014 did not get shipped for some reason; following query adds 7 days i.e 07/31/2014 to the ship date. The only problem is the 'Test Ship' column adds 7 days to the shipment days only on Sundays if we miss the shipment date. I mean for the jobs that had scheduled ship date 07/24/2014, and we missed the jobs to ship on the 07/24/2014, below column 'Test Ship' updates next schedule ship day 07/31/2014 only on SUNDAY. For example say J012345 job did not get shipped on 07/24/2014, 'Test Ship' column will update the date tomorrow i.e 07/27/2014 (Sunday) instead of updating it on FRIDAY 07/25/2014. Am I missing anything here? How can I get 'Test Ship' to update the date on FRIDAY instead of SUNDAY? I would appreciate your help on this. Thanks.




'Test Ship' = Case when j.JobStatus <>'S'
and Cast(x.ExpectedDate as Date) < DATEADD(wk, DATEDIFF(wk,3,cast (GETDATE() as date)), 3)
then Dateadd(day,4,cast (DATEADD(week, DATEDIFF(week,0,GETDATE()),-1) as date))
when j.JobStatus ='S' then Cast(j.LastShippedDate as DATE)
else Cast(x.ExpectedDate as Date) END
Post #1596547
Posted Saturday, July 26, 2014 10:43 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 2,000, Visits: 5,201
It is better to break down the problem into smaller parts using nested case statements, more readable and easier to follow. Here is an example which should be close to what you are after.


USE tempdb;
GO

DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,JobStatus CHAR(1) NOT NULL
,ExpectedDate VARCHAR(10) NOT NULL
,LastShippedDate VARCHAR(10) NULL
);

INSERT INTO @TEST_DATA (JobStatus,ExpectedDate,LastShippedDate)
VALUES
('S','2014-06-21','2014-06-21')
,('F','2014-07-15',NULL)
,('F','2014-07-16',NULL)
,('F','2014-07-17',NULL)
,('F','2014-07-18',NULL)
,('F','2014-07-19',NULL)
,('F','2014-07-20',NULL)
,('F','2014-07-21',NULL)
,('F','2014-07-22',NULL)
,('F','2014-07-23',NULL)
,('F','2014-07-24',NULL)
,('F','2014-07-25',NULL)
,('F','2014-07-26',NULL)
,('F','2014-07-27',NULL)
,('F','2014-07-28',NULL)
,('F','2014-07-29',NULL)
,('F','2014-07-30',NULL)
,('F','2014-07-31',NULL);

SELECT
J.DT_ID
,J.JobStatus
,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), CAST(GETDATE() AS DATE)) BETWEEN 1 AND 5 THEN
CASE
/* SET SATURDAY AND SUNDAY SHIPMENTS TO NEXT FRIDAY */
WHEN DATENAME(WEEKDAY,CAST(J.ExpectedDate AS DATE)) = 'Sunday' THEN DATEADD(DAY,5,CAST(J.ExpectedDate AS DATE))
WHEN DATENAME(WEEKDAY,CAST(J.ExpectedDate AS DATE)) = 'Saturday' THEN 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), CAST(GETDATE() AS DATE)) > 5 THEN
CASE
/* SET SHIPMENTS TO NEXT FRIDAY EXEPT FRIDAY GOES TO MONDAY */
WHEN DATENAME(WEEKDAY,GETDATE()) = 'Sunday' THEN DATEADD(DAY,5,CAST(GETDATE() AS DATE))
WHEN DATENAME(WEEKDAY,GETDATE()) = 'Saturday' THEN DATEADD(DAY,6,CAST(GETDATE() AS DATE))
WHEN DATENAME(WEEKDAY,GETDATE()) = 'Monday' THEN DATEADD(DAY,4,CAST(GETDATE() AS DATE))
WHEN DATENAME(WEEKDAY,GETDATE()) = 'Tuesday' THEN DATEADD(DAY,3,CAST(GETDATE() AS DATE))
WHEN DATENAME(WEEKDAY,GETDATE()) = 'Wednesday' THEN DATEADD(DAY,2,CAST(GETDATE() AS DATE))
WHEN DATENAME(WEEKDAY,GETDATE()) = 'Thursday' THEN DATEADD(DAY,1,CAST(GETDATE() AS DATE))
WHEN DATENAME(WEEKDAY,GETDATE()) = 'Friday' THEN DATEADD(DAY,3,CAST(GETDATE() AS DATE))
END
/* FUTURE SHIPMENTS */
ELSE CAST(J.ExpectedDate AS DATE)
END AS NEXT_SHIP_DATE
FROM @TEST_DATA J

Results
DT_ID       JobStatus ExpectedDate LastShippedDate NEXT_SHIP_DATE
----------- --------- ------------ --------------- --------------
1 S 2014-06-21 2014-06-21 2014-06-21
2 F 2014-07-15 NULL 2014-08-01
3 F 2014-07-16 NULL 2014-08-01
4 F 2014-07-17 NULL 2014-08-01
5 F 2014-07-18 NULL 2014-08-01
6 F 2014-07-19 NULL 2014-08-01
7 F 2014-07-20 NULL 2014-08-01
8 F 2014-07-21 NULL 2014-08-01
9 F 2014-07-22 NULL 2014-07-29
10 F 2014-07-23 NULL 2014-07-30
11 F 2014-07-24 NULL 2014-07-31
12 F 2014-07-25 NULL 2014-08-01
13 F 2014-07-26 NULL 2014-08-01
14 F 2014-07-27 NULL 2014-07-27
15 F 2014-07-28 NULL 2014-07-28
16 F 2014-07-29 NULL 2014-07-29
17 F 2014-07-30 NULL 2014-07-30
18 F 2014-07-31 NULL 2014-07-31
Post #1596573
Posted Sunday, July 27, 2014 2:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 7,164, Visits: 13,259
Based on Eirikurs great solution I added a few minor "enhancements":

a) instead of using GETDATE() in a CASE statement, I've added a separat variable. This has two advantages: it makes the code a little more compact and it avoids using a non-deterministic value that will be re-evaluated each time at runtime. Start a batch with 10.000 rows on a Thursday at 23:59:59.994 and you'll see what I mean: some rows will have a shipment day of the following friday and some will be set to the following monday.
Using a variable the code will always use a consistant value.

b) I replaced the non-deterministic function DATENAME(WEEKDAY,GETDATE()) = '...' with a solution that is independend on the settings of SET LANGUAGE:
DATEDIFF(dd,0,@Date)%7 basically calculates the days since 1900-01-01 (which was a Monday) and takes the Modulo of it. This leads to values from 0 (Monday) to 6 (Sunday) regardless of the language setting being active for that code block.
You can verify the negative effect of DATENAME by adding SET LANGUAGE 'GERMAN' to Eirikurs code after the declaration of @TEST_DATA

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
,JobStatus CHAR(1) NOT NULL
,ExpectedDate VARCHAR(10) NOT NULL
,LastShippedDate VARCHAR(10) NULL
);

INSERT INTO @TEST_DATA (JobStatus,ExpectedDate,LastShippedDate)
VALUES
('S','2014-06-21','2014-06-21')
,('F','2014-07-15',NULL)
,('F','2014-07-16',NULL)
,('F','2014-07-17',NULL)
,('F','2014-07-18',NULL)
,('F','2014-07-19',NULL)
,('F','2014-07-20',NULL)
,('F','2014-07-21',NULL)
,('F','2014-07-22',NULL)
,('F','2014-07-23',NULL)
,('F','2014-07-24',NULL)
,('F','2014-07-25',NULL)
,('F','2014-07-26',NULL)
,('F','2014-07-27',NULL)
,('F','2014-07-28',NULL)
,('F','2014-07-29',NULL)
,('F','2014-07-30',NULL)
,('F','2014-07-31',NULL);

SELECT
J.DT_ID
,J.JobStatus
,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 = 6 THEN DATEADD(DAY,5,CAST(J.ExpectedDate AS DATE))
WHEN DATEDIFF(dd,0,CAST(J.ExpectedDate AS DATE))%7 = 5 THEN 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 = 6 THEN DATEADD(DAY,5,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 5 THEN DATEADD(DAY,6,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 0 THEN DATEADD(DAY,4,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 1 THEN DATEADD(DAY,3,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 2 THEN DATEADD(DAY,2,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 3 THEN DATEADD(DAY,1,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 4 THEN DATEADD(DAY,3,@Date)
END
/* FUTURE SHIPMENTS */
ELSE CAST(J.ExpectedDate AS DATE)
END AS NEXT_SHIP_DATE
FROM @TEST_DATA J





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1596582
Posted Sunday, July 27, 2014 2:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 2,000, Visits: 5,201
Thanks Lutz, I was thinking about revisiting it for the getdate
Post #1596584
Posted Monday, July 28, 2014 7:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:10 AM
Points: 25, Visits: 159
Hi Eirikur & SSC,

I appreciate the response from you guys. I apologize for not being clear before. We have jobs that only get shipped on Thursday of every week. The below T-SQL statement works fine. Let me be more clear, for example if we missed the ship date 07/24/2014 (last Thursday) for a job the 'TestNewShipDate' adds 7 days to the ship date i.e. 07/31/2014 which works according to my requirement. The only issue is the 'TestNewShipDate' updates/adds 7 days (07/31/2014) to the ship date if we missed the shipdate (07/24/2014) only on Sundays which is (07/27/2014). I want the 'TestNewShipDate' to add/update 7 days, on Friday (07/25/2014) instead of updating the date on Sunday for the above case. Need your help guys.

Thanks

select j.JobNumber, j.JobStatus,
Cast(x.ExpectedDate as Date) 'OldShipDate'

, 'TestNewShipDate' =
Case
when j.JobStatus <>'S'
and Cast(x.ExpectedDate as Date)
< DATEADD(wk, DATEDIFF(wk,3,cast (GETDATE() as date)), 3)
then Dateadd(day,4,cast (DATEADD(week, DATEDIFF(week,0,GETDATE()),-1) as date))

when j.JobStatus ='S'
then Cast(j.LastShippedDate as DATE)
else Cast(x.ExpectedDate as Date)
END


, 'Shipped' = Case When j.LastShippedDate = '' Then Null Else Cast(j.LastShippedDate As Date) End
from JobExtra j (NOLOCK)
Join ArchiveJobHeader m (nolock) on m.JobNumber = j.JobNumber
Left Join JobMaster x (nolock) on x.JobNumber = j.JobNumber
where m.customercode='3308'

JobNumber JobStatus OldShipDate TestNewShipDate Shipped
J012345 S (Shipped) 2014-07-10 2014-07-17 2014-07-17
J012346 (Blank) (Not Shipped) 2014-07-10 2014-07-31 NULL
Post #1596863
Posted Monday, July 28, 2014 8:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 13,481, Visits: 12,342
amolacp (7/28/2014)
Hi Eirikur & SSC,

I appreciate the response from you guys. I apologize for not being clear before. We have jobs that only get shipped on Thursday of every week. The below T-SQL statement works fine. Let me be more clear, for example if we missed the ship date 07/24/2014 (last Thursday) for a job the 'TestNewShipDate' adds 7 days to the ship date i.e. 07/31/2014 which works according to my requirement. The only issue is the 'TestNewShipDate' updates/adds 7 days (07/31/2014) to the ship date if we missed the shipdate (07/24/2014) only on Sundays which is (07/27/2014). I want the 'TestNewShipDate' to add/update 7 days, on Friday (07/25/2014) instead of updating the date on Sunday for the above case. Need your help guys.

Thanks

select j.JobNumber, j.JobStatus,
Cast(x.ExpectedDate as Date) 'OldShipDate'

, 'TestNewShipDate' =
Case
when j.JobStatus <>'S'
and Cast(x.ExpectedDate as Date)
< DATEADD(wk, DATEDIFF(wk,3,cast (GETDATE() as date)), 3)
then Dateadd(day,4,cast (DATEADD(week, DATEDIFF(week,0,GETDATE()),-1) as date))

when j.JobStatus ='S'
then Cast(j.LastShippedDate as DATE)
else Cast(x.ExpectedDate as Date)
END


, 'Shipped' = Case When j.LastShippedDate = '' Then Null Else Cast(j.LastShippedDate As Date) End
from JobExtra j (NOLOCK)
Join ArchiveJobHeader m (nolock) on m.JobNumber = j.JobNumber
Left Join JobMaster x (nolock) on x.JobNumber = j.JobNumber
where m.customercode='3308'

JobNumber JobStatus OldShipDate TestNewShipDate Shipped
J012345 S (Shipped) 2014-07-10 2014-07-17 2014-07-17
J012346 (Blank) (Not Shipped) 2014-07-10 2014-07-31 NULL


We can help but we need something to work with. Notice the sample tables and data posted to your first attempt. Something like that.

Also, why the NOLOCK hints. Are you ok with missing and/or duplicate data?

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/

If you are going to stick with it, make sure you use the WITH keyword. Not using it has been deprecated.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1596887
Posted Monday, July 28, 2014 3:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:10 AM
Points: 25, Visits: 159
Here is the DDL. Suppose, if today's date is 2014-08-01 and we miss the shipdate for the job# J012362, the 'TestNewShipDate' still shows 2014-07-31 instead of 2014-08-01. I've also included the DDL at the end of this message. A quick note, since we only ship on Thursday of every weel, I want the TestNewShipDate to get updated to next Thursday. Also, I've attached the results screenshot to this message. I appreciate your help.

USE tempdb;
GO

DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,JobNumber VARCHAR(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);



SELECT
J.DT_ID
,J.JobNumber
,J.JobStatus
,J.ExpectedDate
,

'TestNewShipDate'=Case
when j.JobStatus <>'S'
and Cast(J.ExpectedDate as Date)
< cast (GETDATE()+4 as DATE)
then Dateadd(day,4,cast (DATEADD(week, DATEDIFF(week,0,GETDATE()),-1) as date))



when j.JobStatus ='S'
then Cast(j.LastShippedDate as DATE)
else Cast(J.ExpectedDate as Date)
END
,cast (GETDATE()+4 as DATE) as TodaysDate
,J.LastShippedDate
FROM @TEST_DATA J



  Post Attachments 
DateIssue.png (4 views, 34.30 KB)
Post #1597105
Posted Tuesday, July 29, 2014 8:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:10 AM
Points: 25, Visits: 159
Can somebody give me a hand on this? Stuck with issue.
Post #1597330
Posted Tuesday, July 29, 2014 11:14 AM This worked for the OP Answer marked as solution
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 2,000, Visits: 5,201
amolacp (7/29/2014)
Can somebody give me a hand on this? Stuck with issue.


There is no magic here, the case statement I posted (use Lutz's version) allows you to control this to the detail, not certain what would be the reason for not using that. Read through the code and come back with any questions you might have. Of course you must adjust it to your needs, but I cannot see any scenario where it doesn't work.
Here is a modification of Lutz's code towards your requirements
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
,JobNumber VARCHAR(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);

SELECT
J.DT_ID
,J.JobStatus
,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 = 5 THEN DATEADD(DAY,5,CAST(J.ExpectedDate AS DATE))
WHEN DATEDIFF(dd,0,CAST(J.ExpectedDate AS DATE))%7 = 4 THEN 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 = 6 THEN DATEADD(DAY,4,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 5 THEN DATEADD(DAY,5,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 0 THEN DATEADD(DAY,3,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 1 THEN DATEADD(DAY,2,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 2 THEN DATEADD(DAY,1,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 3 THEN DATEADD(DAY,0,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 4 THEN DATEADD(DAY,3,@Date)
END
/* FUTURE SHIPMENTS */
ELSE CAST(J.ExpectedDate AS DATE)
END AS NEXT_SHIP_DATE
FROM @TEST_DATA J

Results
DT_ID       JobStatus ExpectedDate LastShippedDate NEXT_SHIP_DATE
----------- --------- ------------ --------------- --------------
1 S 2014-06-26 2014-07-03 2014-07-03
2 O 2014-07-03 NULL 2014-07-31
3 O 2014-07-03 NULL 2014-07-31
4 O 2014-07-10 NULL 2014-07-31
5 O 2014-07-10 NULL 2014-07-31
6 O 2014-07-10 NULL 2014-07-31
7 O 2014-07-10 NULL 2014-07-31
8 O 2014-07-17 NULL 2014-07-31
9 O 2014-07-17 NULL 2014-07-31
10 O 2014-07-17 NULL 2014-07-31
11 O 2014-07-17 NULL 2014-07-31
12 O 2014-07-17 NULL 2014-07-31
13 O 2014-07-24 NULL 2014-07-31
14 O 2014-07-24 NULL 2014-07-31
15 O 2014-07-24 NULL 2014-07-31
16 O 2014-07-24 NULL 2014-07-31
17 O 2014-07-24 NULL 2014-07-31
18 O 2014-07-31 NULL 2014-07-31
Post #1597416
Posted Tuesday, July 29, 2014 1:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:10 AM
Points: 25, Visits: 159
Eirikur Eiriksson- Thank you very much. The T-SQL statment you suggested works. Thanks, again
Post #1597477
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse