July 23, 2015 at 8:52 am
Hello All,
I've a report that manages Scheduled Ship Date based on the Requested Delivery Date. We normally ship every Monday of the week so I've Scheduled Ship Date set to Monday. But for some of the dates this does not work. For instance when the REquested Delivery Date is 2015-07-29, the Scheduled Ship Date does not show following Monday i.e 2015-08-03.
Here is my DDL:
USE tempdb;
GO
DECLARE @Date datetime;
SET @Date = GETDATE();
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','2015-07-10','2015-07-10')
,('S', 'J012346','2015-07-15','2015-07-17')
,('S', 'J012346','2015-07-17','2015-07-15')
,('S', 'J012346','2015-07-08','2015-07-10')
,('O', 'J012347','2015-08-04',NULL)
,('O', 'J012347','2015-07-24',NULL)
,('O', 'J012347','2015-07-31',NULL)
,('O', 'J012347','2015-07-27',NULL)
,('O', 'J012348','2015-07-21',NULL)
,('O', 'J012349','2015-07-31',NULL)
,('O', 'J012350','2015-08-07',NULL)
,('O', 'J012351','2015-08-14',NULL)
,('O', 'J012362','2015-08-28',NULL)
,('O', 'J012363','2015-07-22',NULL)
,('O', 'J012364','2015-07-24',NULL)
,('O', 'J012365','2015-07-31',NULL)
,('O', 'J012366','2015-08-21',NULL)
,('O', 'J012372','2015-07-27',NULL)
,('O', 'J012378','2015-07-29',NULL)
,('O', 'J012367','2015-08-11',NULL)
,('O', 'J012367','2015-07-30',NULL)
,('O', 'J012367','2015-09-18',NULL)
;
SELECT
J.DT_ID
,J.JobStatus /*O-Open, S-Shipped, I-Invoiced*/
,J.ExpectedDate 'Requested Delivery Date'
,J.LastShippedDate
,'Scheduled 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 MONDAY */
WHEN j.JobStatus <>'S' AND DATEDIFF(dd,0,ExpectedDate)/7 <= DATEDIFF(dd,0,@Date) / 7 THEN
DATEADD(DAY,((DATEDIFF(dd,0,@Date)/7) * 7) + 7,0)
/* FUTURE SHIPMENTS */
ELSE CAST(J.ExpectedDate AS DATE)
END
FROM @TEST_DATA J
order by [Requested Delivery Date]
July 23, 2015 at 9:03 am
can you amend this
http://www.sqlservercentral.com/Forums/FindPost1671071.aspx
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 23, 2015 at 9:09 am
J Livingston SQL (7/23/2015)
can you amend this
hmmmmmmmmmmmmm
Didn't like the answer the first time, I guess.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 23, 2015 at 9:39 am
SSRS Newbie (7/23/2015)
Hello All,I've a report that manages Scheduled Ship Date based on the Requested Delivery Date. We normally ship every Monday of the week so I've Scheduled Ship Date set to Monday. But for some of the dates this does not work. For instance when the REquested Delivery Date is 2015-07-29, the Scheduled Ship Date does not show following Monday i.e 2015-08-03.
Here is my DDL:
USE tempdb;
GO
DECLARE @Date datetime;
SET @Date = GETDATE();
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','2015-07-10','2015-07-10')
,('S', 'J012346','2015-07-15','2015-07-17')
,('S', 'J012346','2015-07-17','2015-07-15')
,('S', 'J012346','2015-07-08','2015-07-10')
,('O', 'J012347','2015-08-04',NULL)
,('O', 'J012347','2015-07-24',NULL)
,('O', 'J012347','2015-07-31',NULL)
,('O', 'J012347','2015-07-27',NULL)
,('O', 'J012348','2015-07-21',NULL)
,('O', 'J012349','2015-07-31',NULL)
,('O', 'J012350','2015-08-07',NULL)
,('O', 'J012351','2015-08-14',NULL)
,('O', 'J012362','2015-08-28',NULL)
,('O', 'J012363','2015-07-22',NULL)
,('O', 'J012364','2015-07-24',NULL)
,('O', 'J012365','2015-07-31',NULL)
,('O', 'J012366','2015-08-21',NULL)
,('O', 'J012372','2015-07-27',NULL)
,('O', 'J012378','2015-07-29',NULL)
,('O', 'J012367','2015-08-11',NULL)
,('O', 'J012367','2015-07-30',NULL)
,('O', 'J012367','2015-09-18',NULL)
;
SELECT
J.DT_ID
,J.JobStatus /*O-Open, S-Shipped, I-Invoiced*/
,J.ExpectedDate 'Requested Delivery Date'
,J.LastShippedDate
,'Scheduled 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 MONDAY */
WHEN j.JobStatus <>'S' AND DATEDIFF(dd,0,ExpectedDate)/7 <= DATEDIFF(dd,0,@Date) / 7 THEN
DATEADD(DAY,((DATEDIFF(dd,0,@Date)/7) * 7) + 7,0)
/* FUTURE SHIPMENTS */
ELSE CAST(J.ExpectedDate AS DATE)
END
FROM @TEST_DATA J
order by [Requested Delivery Date]
Take a look at the following, which I added some extra fields to, to help illustrate what's taking place:
DECLARE @Date datetime;
SET @Date = GETDATE();
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','2015-07-10','2015-07-10')
,('S', 'J012346','2015-07-15','2015-07-17')
,('S', 'J012346','2015-07-17','2015-07-15')
,('S', 'J012346','2015-07-08','2015-07-10')
,('O', 'J012347','2015-08-04',NULL)
,('O', 'J012347','2015-07-24',NULL)
,('O', 'J012347','2015-07-31',NULL)
,('O', 'J012347','2015-07-27',NULL)
,('O', 'J012348','2015-07-21',NULL)
,('O', 'J012349','2015-07-31',NULL)
,('O', 'J012350','2015-08-07',NULL)
,('O', 'J012351','2015-08-14',NULL)
,('O', 'J012362','2015-08-28',NULL)
,('O', 'J012363','2015-07-22',NULL)
,('O', 'J012364','2015-07-24',NULL)
,('O', 'J012365','2015-07-31',NULL)
,('O', 'J012366','2015-08-21',NULL)
,('O', 'J012372','2015-07-27',NULL)
,('O', 'J012378','2015-07-29',NULL)
,('O', 'J012367','2015-08-11',NULL)
,('O', 'J012367','2015-07-30',NULL)
,('O', 'J012367','2015-09-18',NULL)
;
SELECT
J.DT_ID
,J.JobStatus /*O-Open, S-Shipped, I-Invoiced*/
,J.ExpectedDate 'Requested Delivery Date'
,J.LastShippedDate
,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 MONDAY */
WHEN j.JobStatus <>'S' AND DATEDIFF(dd,0,ExpectedDate)/7 <= DATEDIFF(dd,0,@Date) / 7 THEN
DATEADD(DAY,((DATEDIFF(dd,0,@Date)/7) * 7) + 7,0)
/* FUTURE SHIPMENTS */
--ELSE CAST(J.ExpectedDate AS DATE)
ELSE DATEADD(DAY,((DATEDIFF(dd,0,J.ExpectedDate)/7) * 7) + 7,0)
END AS [Scheduled Ship Date],
CASE
/* SHIPPED - SHOW LastShippedDate */
WHEN j.JobStatus ='S' OR j.JobStatus ='I' OR j.JobStatus ='V' THEN 'LastShippedDate'
/* MISSED SHIPMENT SET TO NEXT MONDAY */
WHEN j.JobStatus <>'S' AND DATEDIFF(dd,0,ExpectedDate)/7 <= DATEDIFF(dd,0,@Date) / 7 THEN 'DATEADD(DAY,((DATEDIFF(dd,0,@Date)/7) * 7) + 7,0)'
/* FUTURE SHIPMENTS */
ELSE 'ExpectedDate'
END AS CASE_DECISION_BASIS,
DATENAME(dw, J.ExpectedDate) AS ExpectedDateDayName,
DATENAME(dw, CASE
WHEN j.JobStatus ='S' OR j.JobStatus ='I' OR j.JobStatus ='V' THEN Cast(j.LastShippedDate as DATE)
WHEN j.JobStatus <>'S' AND DATEDIFF(dd,0,ExpectedDate)/7 <= DATEDIFF(dd,0,@Date) / 7 THEN
DATEADD(DAY,((DATEDIFF(dd,0,@Date)/7) * 7) + 7,0)
--ELSE CAST(J.ExpectedDate AS DATE)
ELSE DATEADD(DAY,((DATEDIFF(dd,0,J.ExpectedDate)/7) * 7) + 7,0)
END) AS ScheduledShipDateDayName
FROM @TEST_DATA AS J
ORDER BY [Requested Delivery Date]
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 23, 2015 at 3:08 pm
sgmunson,
Thank you for your response. The code you suggested works. I need to assign a Scheduled Ship Date to the closest Monday. For example- if the Requested Delivery Date is 2015-08-04, I need to show Scheduled Ship Date as 2015-08-03. Can we do this using T-SQL? Thanks.
July 23, 2015 at 3:18 pm
SSRS Newbie (7/23/2015)
sgmunson,Thank you for your response. The code you suggested works. I need to assign a Scheduled Ship Date to the closest Monday. For example- if the Requested Delivery Date is 2015-08-04, I need to show Scheduled Ship Date as 2015-08-03. Can we do this using T-SQL? Thanks.
what are your rules for "closest Monday".....say for instance requested date was 2015-08-06...do you want to return 2015-08-03 or 2015-08-10?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 23, 2015 at 3:32 pm
Yes, if the requested date was 2015-08-06 then the return date will be 2015-08-10. Thank you for your prompt response.
July 23, 2015 at 3:43 pm
SSRS Newbie (7/23/2015)
Yes, if the requested date was 2015-08-06 then the return date will be 2015-08-10. Thank you for your prompt response.
can you clarify your rules please.....do we exclude weekends (Sat/Sun) ...what about holidays (in Uk we have several "bank holidays" on a Monday)...??
am thinking you may need to consider a "shipping date" calendar to assist you with this.
your thoughts?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 24, 2015 at 6:31 am
SSRS Newbie (7/23/2015)
sgmunson,Thank you for your response. The code you suggested works. I need to assign a Scheduled Ship Date to the closest Monday. For example- if the Requested Delivery Date is 2015-08-04, I need to show Scheduled Ship Date as 2015-08-03. Can we do this using T-SQL? Thanks.
Yes, we can do it with T-SQL, but as J Livingston SQL indicated, we need to know EXACTLY HOW to decide which of the two Mondays is the best choice for any given Expected Date. If we have to take holidays into consideration, then you're going to need a holiday table, and while it's not rocket science, it will require a fair amount of thought go into how that should work. You have to worry about how it will get updated, and you'll have to know for certain whether or not human decisions by the company can affect the holiday dates. You should also consider that chances are, there may well be other IT-related reporting or processing that would benefit from knowing about holidays, so don't just worry about Monday holidays. Yes, I realize that will significantly broaden the scope of this problem, but it's unavoidable. Let us know...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 24, 2015 at 8:14 am
sgmunson & J Livingston SQL - I appreciate your response. I modified the DDL and included the BATCH NUMBER per the business rule. Here is the business rule:
1) If the Job has a batch number, the Scheduled Ship Date will be next monday to Requested Delivery Date. Say for example if the job has Requested Delivery Date as 2015-07-29 and it also has a batch number then Scheduled Ship Date will be 2015-08-03.
2) If the Job does not have a batch number then the Scheduled Ship Date will be the Monday before the REquested Delivery Date. Say for example if the Job J012347 has Requested Delivery date as 2015-08-04 and it does not have batch number then the Scheduled Ship Date will be the Monday before i.e. 2015-08-04.
Similarly if the Requested Delivery date is 2015-08-07 and it does not have a batch number then Scheduled Delivery Date will be 2015-08-03.
Can we achieve this? I appreciate any help on this. Thanks again guys.
Hope this helps
Here is the DDL:
DECLARE @Date datetime;
SET @Date = GETDATE();
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
,BatchNumber VARCHAR(10)NULL
);
INSERT INTO @TEST_DATA (JobStatus, JobNumber,ExpectedDate,LastShippedDate, BatchNumber)
VALUES
('S', 'J012345','2015-07-10','2015-07-10', 'B001')
,('S', 'J012346','2015-07-15','2015-07-17', 'B001')
,('S', 'J012346','2015-07-17','2015-07-15', 'B002')
,('S', 'J012346','2015-07-08','2015-07-10', 'B002')
,('O', 'J012347','2015-08-04',NULL, NULL)
,('O', 'J012347','2015-07-24',NULL, 'B003')
,('O', 'J012347','2015-07-31',NULL, 'B003')
,('O', 'J012347','2015-07-27',NULL, 'B004')
,('O', 'J012348','2015-07-21',NULL, 'B004')
,('O', 'J012349','2015-07-31',NULL, NULL)
,('O', 'J012350','2015-08-07',NULL, NULL)
,('O', 'J012351','2015-08-14',NULL, NULL)
,('O', 'J012362','2015-08-28',NULL, 'B004')
,('O', 'J012363','2015-07-22',NULL, 'B005')
,('O', 'J012364','2015-07-24',NULL, NULL)
,('O', 'J012365','2015-07-31',NULL, NULL)
,('O', 'J012366','2015-08-21',NULL, 'B006')
,('O', 'J012372','2015-07-27',NULL, 'B007')
,('O', 'J012378','2015-07-29',NULL, 'B008')
,('O', 'J012367','2015-08-11',NULL, NULL)
,('O', 'J012367','2015-07-30',NULL, NULL)
,('O', 'J012367','2015-09-18',NULL, 'B006')
;
SELECT
J.DT_ID
,JobNumber
,J.JobStatus /*O-Open, S-Shipped, I-Invoiced*/
,J.ExpectedDate 'Requested Delivery Date'
,J.LastShippedDate
, BatchNumber
,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 MONDAY */
WHEN j.JobStatus <>'S' AND DATEDIFF(dd,0,ExpectedDate)/7 <= DATEDIFF(dd,0,@Date) / 7 THEN
DATEADD(DAY,((DATEDIFF(dd,0,@Date)/7) * 7) + 7,0)
/* FUTURE SHIPMENTS */
--ELSE CAST(J.ExpectedDate AS DATE)
ELSE DATEADD(DAY,((DATEDIFF(dd,0,J.ExpectedDate)/7) * 7) + 7,0)
END AS [Scheduled Ship Date],
CASE
/* SHIPPED - SHOW LastShippedDate */
WHEN j.JobStatus ='S' OR j.JobStatus ='I' OR j.JobStatus ='V' THEN 'LastShippedDate'
/* MISSED SHIPMENT SET TO NEXT MONDAY */
WHEN j.JobStatus <>'S' AND DATEDIFF(dd,0,ExpectedDate)/7 <= DATEDIFF(dd,0,@Date) / 7 THEN 'DATEADD(DAY,((DATEDIFF(dd,0,@Date)/7) * 7) + 7,0)'
/* FUTURE SHIPMENTS */
ELSE 'ExpectedDate'
END AS CASE_DECISION_BASIS,
DATENAME(dw, J.ExpectedDate) AS ExpectedDateDayName,
DATENAME(dw, CASE
WHEN j.JobStatus ='S' OR j.JobStatus ='I' OR j.JobStatus ='V' THEN Cast(j.LastShippedDate as DATE)
WHEN j.JobStatus <>'S' AND DATEDIFF(dd,0,ExpectedDate)/7 <= DATEDIFF(dd,0,@Date) / 7 THEN
DATEADD(DAY,((DATEDIFF(dd,0,@Date)/7) * 7) + 7,0)
--ELSE CAST(J.ExpectedDate AS DATE)
ELSE DATEADD(DAY,((DATEDIFF(dd,0,J.ExpectedDate)/7) * 7) + 7,0)
END) AS ScheduledShipDateDayName
FROM @TEST_DATA AS J
ORDER BY [Requested Delivery Date]
July 24, 2015 at 9:46 am
SSRS Newbie (7/24/2015)
sgmunson & J Livingston SQL - I appreciate your response. I modified the DDL and included the BATCH NUMBER per the business rule. Here is the business rule:1) If the Job has a batch number, the Scheduled Ship Date will be next monday to Requested Delivery Date. Say for example if the job has Requested Delivery Date as 2015-07-29 and it also has a batch number then Scheduled Ship Date will be 2015-08-03.
2) If the Job does not have a batch number then the Scheduled Ship Date will be the Monday before the REquested Delivery Date. Say for example if the Job J012347 has Requested Delivery date as 2015-08-04 and it does not have batch number then the Scheduled Ship Date will be the Monday before i.e. 2015-08-04.
Similarly if the Requested Delivery date is 2015-08-07 and it does not have a batch number then Scheduled Delivery Date will be 2015-08-03.
Can we achieve this? I appreciate any help on this. Thanks again guys.
Hope this helps
Here is the DDL:
DECLARE @Date datetime;SET @Date = GETDATE();
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
,BatchNumber VARCHAR(10)NULL
);
INSERT INTO @TEST_DATA (JobStatus, JobNumber,ExpectedDate,LastShippedDate, BatchNumber)
VALUES
('S', 'J012345','2015-07-10','2015-07-10', 'B001')
,('S', 'J012346','2015-07-15','2015-07-17', 'B001')
,('S', 'J012346','2015-07-17','2015-07-15', 'B002')
,('S', 'J012346','2015-07-08','2015-07-10', 'B002')
,('O', 'J012347','2015-08-04',NULL, NULL)
,('O', 'J012347','2015-07-24',NULL, 'B003')
,('O', 'J012347','2015-07-31',NULL, 'B003')
,('O', 'J012347','2015-07-27',NULL, 'B004')
,('O', 'J012348','2015-07-21',NULL, 'B004')
,('O', 'J012349','2015-07-31',NULL, NULL)
,('O', 'J012350','2015-08-07',NULL, NULL)
,('O', 'J012351','2015-08-14',NULL, NULL)
,('O', 'J012362','2015-08-28',NULL, 'B004')
,('O', 'J012363','2015-07-22',NULL, 'B005')
,('O', 'J012364','2015-07-24',NULL, NULL)
,('O', 'J012365','2015-07-31',NULL, NULL)
,('O', 'J012366','2015-08-21',NULL, 'B006')
,('O', 'J012372','2015-07-27',NULL, 'B007')
,('O', 'J012378','2015-07-29',NULL, 'B008')
,('O', 'J012367','2015-08-11',NULL, NULL)
,('O', 'J012367','2015-07-30',NULL, NULL)
,('O', 'J012367','2015-09-18',NULL, 'B006')
;
SELECT
J.DT_ID
,JobNumber
,J.JobStatus /*O-Open, S-Shipped, I-Invoiced*/
,J.ExpectedDate 'Requested Delivery Date'
,J.LastShippedDate
, BatchNumber
,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 MONDAY */
WHEN j.JobStatus <>'S' AND DATEDIFF(dd,0,ExpectedDate)/7 <= DATEDIFF(dd,0,@Date) / 7 THEN
DATEADD(DAY,((DATEDIFF(dd,0,@Date)/7) * 7) + 7,0)
/* FUTURE SHIPMENTS */
--ELSE CAST(J.ExpectedDate AS DATE)
ELSE DATEADD(DAY,((DATEDIFF(dd,0,J.ExpectedDate)/7) * 7) + 7,0)
END AS [Scheduled Ship Date],
CASE
/* SHIPPED - SHOW LastShippedDate */
WHEN j.JobStatus ='S' OR j.JobStatus ='I' OR j.JobStatus ='V' THEN 'LastShippedDate'
/* MISSED SHIPMENT SET TO NEXT MONDAY */
WHEN j.JobStatus <>'S' AND DATEDIFF(dd,0,ExpectedDate)/7 <= DATEDIFF(dd,0,@Date) / 7 THEN 'DATEADD(DAY,((DATEDIFF(dd,0,@Date)/7) * 7) + 7,0)'
/* FUTURE SHIPMENTS */
ELSE 'ExpectedDate'
END AS CASE_DECISION_BASIS,
DATENAME(dw, J.ExpectedDate) AS ExpectedDateDayName,
DATENAME(dw, CASE
WHEN j.JobStatus ='S' OR j.JobStatus ='I' OR j.JobStatus ='V' THEN Cast(j.LastShippedDate as DATE)
WHEN j.JobStatus <>'S' AND DATEDIFF(dd,0,ExpectedDate)/7 <= DATEDIFF(dd,0,@Date) / 7 THEN
DATEADD(DAY,((DATEDIFF(dd,0,@Date)/7) * 7) + 7,0)
--ELSE CAST(J.ExpectedDate AS DATE)
ELSE DATEADD(DAY,((DATEDIFF(dd,0,J.ExpectedDate)/7) * 7) + 7,0)
END) AS ScheduledShipDateDayName
FROM @TEST_DATA AS J
ORDER BY [Requested Delivery Date]
Here's the modified code:
DECLARE @Date datetime;
SET @Date = GETDATE();
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
,BatchNumber VARCHAR(10)NULL
);
INSERT INTO @TEST_DATA (JobStatus, JobNumber,ExpectedDate,LastShippedDate, BatchNumber)
VALUES
('S', 'J012345','2015-07-10','2015-07-10', 'B001')
,('S', 'J012346','2015-07-15','2015-07-17', 'B001')
,('S', 'J012346','2015-07-17','2015-07-15', 'B002')
,('S', 'J012346','2015-07-08','2015-07-10', 'B002')
,('O', 'J012347','2015-08-04',NULL, NULL)
,('O', 'J012347','2015-07-24',NULL, 'B003')
,('O', 'J012347','2015-07-31',NULL, 'B003')
,('O', 'J012347','2015-07-27',NULL, 'B004')
,('O', 'J012348','2015-07-21',NULL, 'B004')
,('O', 'J012349','2015-07-31',NULL, NULL)
,('O', 'J012350','2015-08-07',NULL, NULL)
,('O', 'J012351','2015-08-14',NULL, NULL)
,('O', 'J012362','2015-08-28',NULL, 'B004')
,('O', 'J012363','2015-07-22',NULL, 'B005')
,('O', 'J012364','2015-07-24',NULL, NULL)
,('O', 'J012365','2015-07-31',NULL, NULL)
,('O', 'J012366','2015-08-21',NULL, 'B006')
,('O', 'J012372','2015-07-27',NULL, 'B007')
,('O', 'J012378','2015-07-29',NULL, 'B008')
,('O', 'J012367','2015-08-11',NULL, NULL)
,('O', 'J012367','2015-07-30',NULL, NULL)
,('O', 'J012367','2015-09-18',NULL, 'B006')
;
SELECT J.DT_ID
,JobNumber
,J.JobStatus /*O-Open, S-Shipped, I-Invoiced*/
,J.BatchNumber
,J.ExpectedDate 'Requested Delivery Date'
,J.LastShippedDate
,CASE
/* SHIPPED - SHOW LastShippedDate */
WHEN j.JobStatus IN ('S', 'I', 'V') THEN Cast(j.LastShippedDate as DATE)
/* MISSED SHIPMENT SET TO NEXT MONDAY */
WHEN j.JobStatus <>'S' AND DATEDIFF(dd, 0, ExpectedDate)/7 <= DATEDIFF(dd, 0, @Date)/7 THEN
CASE
WHEN J.BatchNumber IS NULL THEN DATEADD(DAY,((DATEDIFF(dd, 0, J.ExpectedDate)/7) * 7), 0)
ELSE DATEADD(day,((DATEDIFF(dd, 0, J.ExpectedDate)/7) * 7) + 7, 0)
END
/* FUTURE SHIPMENTS */
ELSE
CASE
WHEN J.BatchNumber IS NULL THEN DATEADD(DAY,((DATEDIFF(dd, 0, J.ExpectedDate)/7) * 7), 0)
ELSE DATEADD(day,((DATEDIFF(dd, 0, J.ExpectedDate)/7) * 7) + 7, 0)
END
END AS [Scheduled Ship Date],
CASE
/* SHIPPED - SHOW LastShippedDate */
WHEN J.JobStatus IN ('S', 'I', 'V') THEN 'LastShippedDate'
/* MISSED SHIPMENT SET TO NEXT MONDAY */
WHEN J.JobStatus <> 'S' AND DATEDIFF(dd, 0, J.ExpectedDate)/7 <= DATEDIFF(dd, 0, @Date)/7 THEN
CASE
WHEN J.BatchNumber IS NULL THEN 'NS_BN(NULL) DATEADD(DAY,((DATEDIFF(dd, 0, J.ExpectedDate)/7) * 7), 0)'
ELSE 'NS_BN(EXISTS) DATEADD(day,((DATEDIFF(dd, 0, J.ExpectedDate)/7) * 7) + 7, 0)'
END
/* FUTURE SHIPMENTS */
ELSE
CASE
WHEN J.BatchNumber IS NULL THEN 'ELSE_BN(NULL) DATEADD(DAY,((DATEDIFF(dd, 0, J.ExpectedDate)/7) * 7), 0)'
ELSE 'ELSE_BN(EXISTS) DATEADD(day,((DATEDIFF(dd, 0, J.ExpectedDate)/7) * 7) + 7, 0)'
END
END AS CASE_DECISION_BASIS,
DATENAME(dw, J.ExpectedDate) AS ExpectedDateDayName,
DATENAME(dw, CASE
WHEN j.JobStatus IN ('S', 'I', 'V') THEN CAST(j.LastShippedDate AS date)
WHEN j.JobStatus <> 'S' AND DATEDIFF(dd, 0, J.ExpectedDate)/7 <= DATEDIFF(dd, 0, @Date)/7 THEN
CASE
WHEN J.BatchNumber IS NULL THEN DATEADD(DAY,((DATEDIFF(dd, 0, J.ExpectedDate)/7) * 7), 0)
ELSE DATEADD(day,((DATEDIFF(dd, 0, J.ExpectedDate)/7) * 7) + 7, 0)
END
ELSE
CASE
WHEN J.BatchNumber IS NULL THEN DATEADD(DAY,((DATEDIFF(dd, 0, J.ExpectedDate)/7) * 7)+ 7, 0)
ELSE DATEADD(day,((DATEDIFF(dd, 0, J.ExpectedDate)/7) * 7) + 7, 0)
END
END) AS ScheduledShipDateDayName
FROM @TEST_DATA AS J
ORDER BY [Requested Delivery Date]
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply