October 27, 2014 at 8:16 am
Hello All,
In my below T-sql the shipment date is set to next Thursday, if the shipment is missed in current week. Now, I've to change the t-sql code to change the shipment dates to Monday instead of Thursday. Can somebody give me a hand on this please? Thanks.
Regards
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-10-23','2014-10-23')
,('O', 'J012346','2014-10-16',NULL)
,('O', 'J012347','2014-10-17',NULL)
,('O', 'J012348','2014-10-22',NULL)
,('O', 'J012349','2014-10-02',NULL)
,('O', 'J012350','2014-10-09',NULL)
,('O', 'J012351','2014-09-04',NULL)
,('O', 'J012352','2014-09-11',NULL)
,('O', 'J012353','2014-09-18',NULL)
,('O', 'J012354','2014-09-18',NULL)
,('O', 'J012355','2014-09-14',NULL)
,('O', 'J012356','2014-09-16',NULL)
,('O', 'J012357','2014-09-19',NULL)
,('O', 'J012358','2014-09-01',NULL)
,('O', 'J012359','2014-09-06',NULL)
,('O', 'J012360','2014-09-07',NULL)
,('O', 'J012361','2014-09-12',NULL)
,('O', 'J012362','2014-09-16',NULL);
SELECT
J.DT_ID
,J.JobStatus
,J.ExpectedDate
,J.LastShippedDate
,'New Ship Date'= CASE
/* SHIPPED - SHOW LastShippedDate */
WHEN j.JobStatus ='S' OR j.JobStatus ='I' OR j.JobStatus ='V' 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 (NEED TO SET SHIPMENTS TO NEXT MONDAY)*/
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
FROM @TEST_DATA J
October 27, 2014 at 8:47 am
Please help guys.
October 27, 2014 at 9:09 am
Based on your test data is looks like this works:
WHEN j.JobStatus <>'S' AND DATEDIFF(DAY,CAST(J.ExpectedDate AS DATE), @Date) >= 0 THEN
CASE
/* SET SHIPMENTS TO NEXT THURSDAY (NEED TO SET SHIPMENTS TO NEXT MONDAY)*/
WHEN DATEDIFF(DAY,0,@Date)%7 = 0THEN DATEADD(DAY,7,@Date)
WHEN DATEDIFF(DAY,0,@Date)%7 = 1THEN DATEADD(DAY,6,@Date)
WHEN DATEDIFF(DAY,0,@Date)%7 = 2THEN DATEADD(DAY,5,@Date)
WHEN DATEDIFF(DAY,0,@Date)%7 = 3THEN DATEADD(DAY,4,@Date)
WHEN DATEDIFF(DAY,0,@Date)%7 = 4THEN DATEADD(DAY,3,@Date)
WHEN DATEDIFF(DAY,0,@Date)%7 = 5THEN DATEADD(DAY,2,@Date)
WHEN DATEDIFF(DAY,0,@Date)%7 = 6THEN DATEADD(DAY,1,@Date)
END
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 27, 2014 at 9:17 am
I am a little bit scared that you have code that will set it to Thursday but you can't figure out how to modify the code so it returns a different day. I am guessing you don't understand the current code because the change should be pretty simple.
Take a look at this blog and see it helps you. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 27, 2014 at 9:48 am
Sean- Thank you for the link. I will go over it. I'm new to T-SQL
Jack- Thanks.
October 27, 2014 at 11:33 am
Hi Jack,
The T-SQL you suggested works fine. But when the ExpectedDate has (10/28/2014 or 10/29/2014 or 10/29/2014 or 10/30/2014) the date does not change to Monday (11/03/2014). Can you please help? Here is the T-SQL
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-10-23','2014-10-23')
,('O', 'J012346','2014-10-16',NULL)
,('O', 'J012347','2014-10-17',NULL)
,('O', 'J012348','2014-10-22',NULL)
,('O', 'J012349','2014-10-02',NULL)
,('O', 'J012350','2014-10-09',NULL)
,('O', 'J012362','2014-10-28',NULL)
,('O', 'J012362','2014-10-29',NULL)
,('O', 'J012362','2014-10-30',NULL)
,('O', 'J012362','2014-10-31',NULL);
SELECT
J.DT_ID
,J.JobStatus
,J.ExpectedDate
,J.LastShippedDate
,'New Ship Date'= CASE
/* SHIPPED - SHOW LastShippedDate */
WHEN j.JobStatus ='S' OR j.JobStatus ='I' OR j.JobStatus ='V' 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 (NEED TO SET SHIPMENTS TO NEXT MONDAY)*/
WHEN DATEDIFF(dd,0,@Date)%7 = 0THEN DATEADD(DAY,7,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 1THEN DATEADD(DAY,6,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 2THEN DATEADD(DAY,5,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 3THEN DATEADD(DAY,4,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 4THEN DATEADD(DAY,3,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 5THEN DATEADD(DAY,2,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 6THEN DATEADD(DAY,1,@Date)
END
/* FUTURE SHIPMENTS */
ELSE CAST(J.ExpectedDate AS DATE)
END
FROM @TEST_DATA J
October 27, 2014 at 11:36 am
The difference in days between today (2014-10-27) and the dates you have listed is not greater than or equal to 0, so the code will not change the expected ship date.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply