July 26, 2014 at 5:50 pm
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
July 26, 2014 at 10:43 pm
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
July 27, 2014 at 2:15 am
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 = 6THEN DATEADD(DAY,5,CAST(J.ExpectedDate AS DATE))
WHEN DATEDIFF(dd,0,CAST(J.ExpectedDate AS DATE))%7 = 5THEN 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,5,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 5THEN DATEADD(DAY,6,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 0THEN DATEADD(DAY,4,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 1THEN DATEADD(DAY,3,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 2THEN DATEADD(DAY,2,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 3THEN DATEADD(DAY,1,@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
July 27, 2014 at 2:25 am
Thanks Lutz, I was thinking about revisiting it for the getdate:-D
😎
July 28, 2014 at 7:44 am
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
July 28, 2014 at 8:18 am
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/[/url]
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 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/
July 28, 2014 at 3:57 pm
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
,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);
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
July 29, 2014 at 8:41 am
Can somebody give me a hand on this? Stuck with issue.
July 29, 2014 at 11:14 am
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
,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);
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 = 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
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
July 29, 2014 at 1:27 pm
Eirikur Eiriksson- Thank you very much. The T-SQL statment you suggested works. Thanks, again:-)
July 29, 2014 at 2:35 pm
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
July 29, 2014 at 2:43 pm
Eirikur- First of all, thanks for all the help. I've a quick question. In the below DDL, job# (J066811 & J066594) have expected date (2014-07-29 & 2014-0728) respectively. The NEXT_SHIP_DATE shows (2014-07-29 & 2014-08-04) instead (2014-07-31). We only ship Jobs on Thursday of every week.
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
July 29, 2014 at 2:45 pm
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
July 29, 2014 at 2:48 pm
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
July 29, 2014 at 2:59 pm
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
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply