March 5, 2015 at 7:17 am
Hello All,
I've a SSRS report with the details in regards to shipping information. Currently in my report the 'Scheduled Ship Date' is set to following Monday, if we miss the 'Requested Delivery Date'. However, I need to change this report to set shipment date('Scheduled Ship Date') to Tuesday and Friday for the shipment having 'Requested Delivery Date' for this week or next week(current week and next week). For example- Jobs that have shipment date between 3/1/2015 - 3/7/2015 will have 'Scheduled Ship Date' as Tuesday and Friday of current week. If we miss the shipment for this week it should be scheduled for next Tuesday(3/9/2015) and Friday (3/13/2015). I need to follow this for current week(3/1/2015-3/8/2015) and following week(3/9/2015-3/15/2015) and after that all the following weeks will have 'Scheduled Ship Date' as Tuesdays.
Can someone please help?
Below is the SQL:
USE tempdb;
GO
DECLARE @Date datetime;
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','2015-01-23','2015-01-23')
,('S', 'J012346','2015-02-05','2015-02-09')
,('S', 'J012346','2015-03-02','2015-03-03')
,('O', 'J012347','2015-02-19',NULL)
,('O', 'J012347','2015-02-23',NULL)
,('O', 'J012347','2015-02-23',NULL)
,('O', 'J012347','2015-02-24',NULL)
,('O', 'J012348','2015-02-05',NULL)
,('O', 'J012349','2015-02-13',NULL)
,('O', 'J012350','2015-02-13',NULL)
,('O', 'J012351','2015-02-13',NULL)
,('O', 'J012362','2015-02-21',NULL)
,('O', 'J012363','2015-02-21',NULL)
,('O', 'J012364','2015-02-21',NULL)
,('O', 'J012365','2015-03-02',NULL)
,('O', 'J012366','2015-03-06',NULL)
,('O', 'J012372','2015-03-06',NULL)
,('O', 'J012378','2015-03-19',NULL)
,('O', 'J012367','2015-03-19',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
March 5, 2015 at 8:14 am
Can somebody please give me a hand on this?
March 5, 2015 at 8:58 am
Can you post what you would want the query output to look like?
To me. your 'Tuesday or Friday' thing is a bit unclear. I mean, you could say, if it is the current week, you do tuesday if the date is pre-tuesday or friday if its pre-friday. But you are still saying 'Tuesday or Friday' for missed shipments. When would you ever ship next friday? If its next week couldn't you always do Tuesday?
March 5, 2015 at 9:47 am
Nevyn,
Yes you're right. If the shipment is due pre-tuesday it will Scheduled ship date as Tuesday and for some reason if certain jobs don't ship on Tuesday, they will show scheduled ship date as friday. Sorry for the confusion due to long post.
Attached is the output.
Thanks
March 5, 2015 at 11:07 am
Ok,
1) You changed the test data
2) Can you explain why the last 5 rows should pick tuesday the 24th instead of Friday the 20th?
March 5, 2015 at 11:25 am
Hi Nevyn,
2) Can you explain why the last 5 rows should pick tuesday the 24th instead of Friday the 20th?
That's because starting March 16 we will be shipping only on Tuesdays(one day a week) and not 2 days a week like we are doing this week and next week.
I hope that helps
March 5, 2015 at 11:29 am
Ok, well I started to do a sample for you keeping things roughly the same as you had them (using date math to pick the next one), but if you have ever shifting requirements like this it doesn't make much sense to do things that way.
I will reply soon with a calendar table solution.
March 5, 2015 at 11:37 am
Nevyn- I appreciate your help. Thanks.
March 5, 2015 at 12:23 pm
Ok, the following assumes you have a calendar table . If you don't have one and are unfamiliar with how to make one, look here[/url]
I essentially created the Calendar table he did in the article.
Then we make the following table
CREATE TABLE ShippingDateRules
(
EffectiveDate DATE,
ExpiryDate DATE ,
WeekDayNumber TINYINT
)
GO
And put your business rules data in it
INSERT ShippingDateRules (EffectiveDate,ExpiryDate,WeekDayNumber)
SELECT '2010-01-01','2015-02-28',2
UNION ALL
SELECT '2015-03-01','2015-03-13',3
UNION ALL
SELECT '2015-03-01','2015-03-13',6
UNION ALL
SELECT '2015-03-14','9999-01-01',3
Now, we use a view to basically have a table of shipping dates
CREATE VIEW CalendarWithShippingDates
AS
SELECT [date] ShippingDate FROM sandbox.dbo.calendar c
JOIN ShippingDateRules r
ON c.[date] BETWEEN r.EffectiveDate AND r.ExpiryDate
AND c.WkDNo = r.WeekDayNumber
Then, finally our answer with your modified sample data
USE tempdb;
GO
DECLARE @Date datetime;
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','2015-01-23','2015-01-23')
,('S', 'J012346','2015-02-05','2015-02-09')
,('S', 'J012346','2015-03-02','2015-03-03')
,('O', 'J012347','2015-02-19',NULL)
,('O', 'J012347','2015-02-23',NULL)
,('O', 'J012347','2015-02-23',NULL)
,('O', 'J012347','2015-02-24',NULL)
,('O', 'J012348','2015-02-05',NULL)
,('O', 'J012362','2015-02-21',NULL)
,('O', 'J012365','2015-03-02',NULL)
,('O', 'J012366','2015-03-06',NULL)
,('O', 'J012372','2015-03-09',NULL)
,('O', 'J012372','2015-03-10',NULL)
,('O', 'J012372','2015-03-11',NULL)
,('O', 'J012372','2015-03-11',NULL)
,('O', 'J012372','2015-03-16',NULL)
,('O', 'J012372','2015-03-18',NULL)
,('O', 'J012372','2015-03-19',NULL)
,('O', 'J012372','2015-03-19',NULL)
,('O', 'J012378','2015-03-19',NULL)
,('O', 'J012367','2015-03-19',NULL)
;
SELECT
J.DT_ID
,J.JobNumber
,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 OR FUTURE SHIPMENT SET TO NEXT AVAILABLE TUESDAY OR FRIDAY */
WHEN j.JobStatus <>'S' THEN (SELECT MIN(ShippingDate) FROM CalendarWithShippingDates WHERE ShippingDate >= j.ExpectedDate AND ShippingDate >=@date )
END
FROM @TEST_DATA J
This way, whenever they change the rules for you on when they have shipping dates, you just need to add a new rule in the rules table, and the same query will keep on working.
Alternatively, if the shipping dates are really fluid (change every week), you could just have a table with dates in it in place of the view and calendar table, and just add new ship dates as you get them, and then do the same thing with it (select the soonest one with a date higher than today and higher than the expected date)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply