March 24, 2015 at 9:17 am
Hello All,
I've a SSRS report that reports the 'ScheduledShipDate' based on 'Requested Delivery date'. Currently, on my report 'ScheduledShipDate' is set to Monday of each week since we ship every mondays. I need to change the 'ScheduledShipDate' since from now the ship date will be Wednesday, Thursday and Friday of each week. Here is my DDL. Any help is greatly appreciated. Thanks.
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-03-16','2015-03-16')
,('S', 'J012346','2015-03-17','2015-03-17')
,('S', 'J012346','2015-03-19','2015-03-19')
,('S', 'J012346','2015-03-20','2015-03-20')
,('O', 'J012347','2015-03-24',NULL)
,('O', 'J012347','2015-03-25',NULL)
,('O', 'J012347','2015-03-26',NULL)
,('O', 'J012347','2015-03-27',NULL)
,('O', 'J012348','2015-03-27',NULL)
,('O', 'J012349','2015-03-28',NULL)
,('O', 'J012350','2015-03-31',NULL)
,('O', 'J012351','2015-03-31',NULL)
,('O', 'J012362','2015-04-01',NULL)
,('O', 'J012363','2015-04-02',NULL)
,('O', 'J012364','2015-04-02',NULL)
,('O', 'J012365','2015-04-06',NULL)
,('O', 'J012366','2015-04-06',NULL)
,('O', 'J012372','2015-04-07',NULL)
,('O', 'J012378','2015-04-07',NULL)
,('O', 'J012367','2015-04-08',NULL)
,('O', 'J012367','2015-04-09',NULL)
,('O', 'J012367','2015-04-10',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 24, 2015 at 9:49 am
Something like this?
SELECT j.DT_ID,
j.JobStatus /*O-Open, S-Shipped, I-Invoiced*/,
j.ExpectedDate 'Requested Delivery Date',
j.LastShippedDate,
'New 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 */
WHEN j.JobStatus <> 'S'
/* WEDNESDAY */
AND ( DATEDIFF(dd, 2, ExpectedDate) / 7 ) <= ( DATEDIFF(dd, 2, @Date) / 7 )
AND DATEDIFF(dd, ExpectedDate, GETDATE()) >= 0
THEN DATEADD(DAY, ( ( DATEDIFF(dd, 2, @Date) / 7 ) * 7 ) + 7, 2)
WHEN j.JobStatus <> 'S'
/* THURSDAY */
AND ( DATEDIFF(dd, 3, ExpectedDate) / 7 ) <= ( DATEDIFF(dd, 3, @Date) / 7 )
AND DATEDIFF(dd, ExpectedDate, GETDATE()) >= 0
THEN DATEADD(DAY, ( ( DATEDIFF(dd, 3, @Date) / 7 ) * 7 ) + 7, 3)
WHEN j.JobStatus <> 'S'
/* FRIDAY */
AND ( DATEDIFF(dd, 4, ExpectedDate) / 7 ) <= ( DATEDIFF(dd, 4, @Date) / 7 )
AND DATEDIFF(dd, ExpectedDate, GETDATE()) >= 0
THEN DATEADD(DAY, ( ( DATEDIFF(dd, 4, @Date) / 7 ) * 7 ) + 7, 4)
/* FUTURE SHIPMENTS */
ELSE CAST(j.ExpectedDate AS DATE)
END,
'Old 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;
Returns: -
DT_ID JobStatus Requested Delivery Date LastShippedDate New Scheduled Ship Date Old Scheduled Ship Date
----------- --------- ----------------------- --------------- ----------------------- -----------------------
1 S 2015-03-16 2015-03-16 2015-03-16 00:00:00.000 2015-03-16 00:00:00.000
2 S 2015-03-17 2015-03-17 2015-03-17 00:00:00.000 2015-03-17 00:00:00.000
3 S 2015-03-19 2015-03-19 2015-03-19 00:00:00.000 2015-03-19 00:00:00.000
4 S 2015-03-20 2015-03-20 2015-03-20 00:00:00.000 2015-03-20 00:00:00.000
5 O 2015-03-24 NULL 2015-03-25 00:00:00.000 2015-03-30 00:00:00.000
6 O 2015-03-25 NULL 2015-03-25 00:00:00.000 2015-03-30 00:00:00.000
7 O 2015-03-26 NULL 2015-03-26 00:00:00.000 2015-03-30 00:00:00.000
8 O 2015-03-27 NULL 2015-03-27 00:00:00.000 2015-03-30 00:00:00.000
9 O 2015-03-27 NULL 2015-03-27 00:00:00.000 2015-03-30 00:00:00.000
10 O 2015-03-28 NULL 2015-03-28 00:00:00.000 2015-03-30 00:00:00.000
11 O 2015-03-31 NULL 2015-03-31 00:00:00.000 2015-03-31 00:00:00.000
12 O 2015-03-31 NULL 2015-03-31 00:00:00.000 2015-03-31 00:00:00.000
13 O 2015-04-01 NULL 2015-04-01 00:00:00.000 2015-04-01 00:00:00.000
14 O 2015-04-02 NULL 2015-04-02 00:00:00.000 2015-04-02 00:00:00.000
15 O 2015-04-02 NULL 2015-04-02 00:00:00.000 2015-04-02 00:00:00.000
16 O 2015-04-06 NULL 2015-04-06 00:00:00.000 2015-04-06 00:00:00.000
17 O 2015-04-06 NULL 2015-04-06 00:00:00.000 2015-04-06 00:00:00.000
18 O 2015-04-07 NULL 2015-04-07 00:00:00.000 2015-04-07 00:00:00.000
19 O 2015-04-07 NULL 2015-04-07 00:00:00.000 2015-04-07 00:00:00.000
20 O 2015-04-08 NULL 2015-04-08 00:00:00.000 2015-04-08 00:00:00.000
21 O 2015-04-09 NULL 2015-04-09 00:00:00.000 2015-04-09 00:00:00.000
22 O 2015-04-10 NULL 2015-04-10 00:00:00.000 2015-04-10 00:00:00.000
March 24, 2015 at 9:53 am
Please note that I included some extra logic in there to stop your case statement from affecting rows that are "future". Could probably be cleaned up if someone wants to do some fancy datetime calculations.
March 24, 2015 at 10:06 am
Hi Cadavre,
I appreciate your response. This is exactly what I need but the future dates to show the same schedule Wednesday, Thursday and Friday. How can this be acheived? For example, if the requested delivery date is 2015-03-28, the New scheduled shipdate should show 2015-04-01 and so on. Any help is appreciated. Thanks again
March 24, 2015 at 10:36 am
SSRS Newbie (3/24/2015)
Hi Cadavre,I appreciate your response. This is exactly what I need but the future dates to show the same schedule Wednesday, Thursday and Friday. How can this be acheived? For example, if the requested delivery date is 2015-03-28, the New scheduled shipdate should show 2015-04-01 and so on. Any help is appreciated. Thanks again
I see. I think you're after something like this: -
SELECT j.DT_ID,
j.JobStatus /*O-Open, S-Shipped, I-Invoiced*/,
j.ExpectedDate 'Requested Delivery Date',
j.LastShippedDate,
'New 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 */
WHEN j.JobStatus <> 'S'
/* WEDNESDAY */
AND (
( DATEDIFF(dd, 1, ExpectedDate) / 7 ) <= ( DATEDIFF(dd, 0, @Date) / 7 ) OR
( DATEDIFF(dd, 2, ExpectedDate) / 7 ) <= ( DATEDIFF(dd, 1, @Date) / 7 ) OR
( DATEDIFF(dd, 6, ExpectedDate) / 7 ) <= ( DATEDIFF(dd, 5, @Date) / 7 ) OR
( DATEDIFF(dd, 7, ExpectedDate) / 7 ) <= ( DATEDIFF(dd, 6, @Date) / 7 )
)
THEN DATEADD(day, ((DATEDIFF (day, 3, ExpectedDate) / 7) * 7) + 7, 2)
WHEN j.JobStatus <> 'S'
/* THURSDAY */
AND ( DATEDIFF(dd, 4, ExpectedDate) / 7 ) <= ( DATEDIFF(dd, 3, @Date) / 7 )
THEN DATEADD(DAY, ( ( DATEDIFF(dd, 4, ExpectedDate) / 7 ) * 7 ) + 7, 3)
WHEN j.JobStatus <> 'S'
/* FRIDAY */
AND ( DATEDIFF(dd, 5, ExpectedDate) / 7 ) <= ( DATEDIFF(dd, 4, @Date) / 7 )
THEN DATEADD(DAY, ( ( DATEDIFF(dd, 5, ExpectedDate) / 7 ) * 7 ) + 7, 4)
/* FUTURE SHIPMENTS */
ELSE CAST(j.ExpectedDate AS DATE)
END,
'Old 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;
Which results in: -
DT_ID JobStatus Requested Delivery Date LastShippedDate New Scheduled Ship Date Old Scheduled Ship Date
----------- --------- ----------------------- --------------- ----------------------- -----------------------
1 S 2015-03-16 2015-03-16 2015-03-16 00:00:00.000 2015-03-16 00:00:00.000
2 S 2015-03-17 2015-03-17 2015-03-17 00:00:00.000 2015-03-17 00:00:00.000
3 S 2015-03-19 2015-03-19 2015-03-19 00:00:00.000 2015-03-19 00:00:00.000
4 S 2015-03-20 2015-03-20 2015-03-20 00:00:00.000 2015-03-20 00:00:00.000
5 O 2015-03-24 NULL 2015-03-25 00:00:00.000 2015-03-30 00:00:00.000
6 O 2015-03-25 NULL 2015-03-25 00:00:00.000 2015-03-30 00:00:00.000
7 O 2015-03-26 NULL 2015-04-01 00:00:00.000 2015-03-30 00:00:00.000
8 O 2015-03-27 NULL 2015-04-01 00:00:00.000 2015-03-30 00:00:00.000
9 O 2015-03-27 NULL 2015-04-01 00:00:00.000 2015-03-30 00:00:00.000
10 O 2015-03-28 NULL 2015-04-01 00:00:00.000 2015-03-30 00:00:00.000
11 O 2015-03-31 NULL 2015-04-01 00:00:00.000 2015-03-31 00:00:00.000
12 O 2015-03-31 NULL 2015-04-01 00:00:00.000 2015-03-31 00:00:00.000
13 O 2015-04-01 NULL 2015-04-01 00:00:00.000 2015-04-01 00:00:00.000
14 O 2015-04-02 NULL 2015-04-02 00:00:00.000 2015-04-02 00:00:00.000
15 O 2015-04-02 NULL 2015-04-02 00:00:00.000 2015-04-02 00:00:00.000
16 O 2015-04-06 NULL 2015-04-06 00:00:00.000 2015-04-06 00:00:00.000
17 O 2015-04-06 NULL 2015-04-06 00:00:00.000 2015-04-06 00:00:00.000
18 O 2015-04-07 NULL 2015-04-07 00:00:00.000 2015-04-07 00:00:00.000
19 O 2015-04-07 NULL 2015-04-07 00:00:00.000 2015-04-07 00:00:00.000
20 O 2015-04-08 NULL 2015-04-08 00:00:00.000 2015-04-08 00:00:00.000
21 O 2015-04-09 NULL 2015-04-09 00:00:00.000 2015-04-09 00:00:00.000
22 O 2015-04-10 NULL 2015-04-10 00:00:00.000 2015-04-10 00:00:00.000
If not, can you knock up a quick SELECT query with the expected results? I'm about to head home, but I'll do my best to take a look either later tonight or tomorrow morning.
March 24, 2015 at 11:20 am
Hi Cadavre,
Below is the table for expected results and select query:
JobNumberRequestedDeliveryDateNewScheduledShipDate
J0123452015-03-162015-03-16
J0123462015-03-172015-03-17
J0123462015-03-192015-03-19
J0123462015-03-202015-03-20
J0123472015-03-242015-03-25
J0123472015-03-252015-03-25
J0123472015-03-262015-03-26
J0123472015-03-272015-03-27
J0123482015-03-272015-03-27
J0123492015-03-282015-04-01
J0123502015-03-312015-04-01
J0123512015-03-312015-04-01
J0123622015-04-012015-04-01
J0123632015-04-022015-04-02
J0123642015-04-022015-04-02
J0123742015-04-032015-04-03
J0123642015-04-042015-04-08
J0123652015-04-052015-04-08
J0123662015-04-062015-04-08
J0123722015-04-072015-04-08
J0123782015-04-072015-04-08
J0123672015-04-082015-04-08
J0123672015-04-092015-04-09
J0123672015-04-102015-04-10
March 25, 2015 at 2:20 am
SSRS Newbie (3/24/2015)
Hi Cadavre,Below is the table for expected results and select query:
Thanks.
First, just to get the admin out of the way, I meant that I could do with the expected data written as a select statement, e.g.
SELECT a.JobNumber,
a.RequestedDeliveryDate,
a.NewScheduledShipDate
FROM ( VALUES ( 'J012345', '2015-03-16', '2015-03-16'),
( 'J012346', '2015-03-17', '2015-03-17'),
( 'J012346', '2015-03-19', '2015-03-19'),
( 'J012346', '2015-03-20', '2015-03-20'),
( 'J012347', '2015-03-24', '2015-03-25'),
( 'J012347', '2015-03-25', '2015-03-25'),
( 'J012347', '2015-03-26', '2015-03-26'),
( 'J012347', '2015-03-27', '2015-03-27'),
( 'J012348', '2015-03-27', '2015-03-27'),
( 'J012349', '2015-03-28', '2015-04-01'),
( 'J012350', '2015-03-31', '2015-04-01'),
( 'J012351', '2015-03-31', '2015-04-01'),
( 'J012362', '2015-04-01', '2015-04-01'),
( 'J012363', '2015-04-02', '2015-04-02'),
( 'J012364', '2015-04-02', '2015-04-02'),
( 'J012374', '2015-04-03', '2015-04-03'),
( 'J012364', '2015-04-04', '2015-04-08'),
( 'J012365', '2015-04-05', '2015-04-08'),
( 'J012366', '2015-04-06', '2015-04-08'),
( 'J012372', '2015-04-07', '2015-04-08'),
( 'J012378', '2015-04-07', '2015-04-08'),
( 'J012367', '2015-04-08', '2015-04-08'),
( 'J012367', '2015-04-09', '2015-04-09'),
( 'J012367', '2015-04-10', '2015-04-10') ) a ( JobNumber, RequestedDeliveryDate, NewScheduledShipDate );
No biggie, but for next time. The reason I wanted this was so that I could compare with my result-set in SQL Server.
Here's the query: -
SELECT j.DT_ID,
j.JobStatus /*O-Open, S-Shipped, I-Invoiced*/,
j.ExpectedDate 'Requested Delivery Date',
j.LastShippedDate,
'New 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 */
WHEN j.JobStatus <> 'S' AND (j.ExpectedDate > @Date OR j.LastShippedDate IS NULL) AND 1 + DATEDIFF(DAY, 0, j.ExpectedDate) % 7 IN (0, 1, 2, 6)
THEN DATEADD(day, ((DATEDIFF (day, 2, ExpectedDate) / 7) * 7) + 7, 2)
ELSE CAST(j.ExpectedDate AS DATE)
END,
'Old 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;
Which results in: -
DT_ID JobStatus Requested Delivery Date LastShippedDate New Scheduled Ship Date Old Scheduled Ship Date
----------- --------- ----------------------- --------------- ----------------------- -----------------------
1 S 2015-03-16 2015-03-16 2015-03-16 00:00:00.000 2015-03-16 00:00:00.000
2 S 2015-03-17 2015-03-17 2015-03-17 00:00:00.000 2015-03-17 00:00:00.000
3 S 2015-03-19 2015-03-19 2015-03-19 00:00:00.000 2015-03-19 00:00:00.000
4 S 2015-03-20 2015-03-20 2015-03-20 00:00:00.000 2015-03-20 00:00:00.000
5 O 2015-03-24 NULL 2015-03-25 00:00:00.000 2015-03-30 00:00:00.000
6 O 2015-03-25 NULL 2015-03-25 00:00:00.000 2015-03-30 00:00:00.000
7 O 2015-03-26 NULL 2015-03-26 00:00:00.000 2015-03-30 00:00:00.000
8 O 2015-03-27 NULL 2015-03-27 00:00:00.000 2015-03-30 00:00:00.000
9 O 2015-03-27 NULL 2015-03-27 00:00:00.000 2015-03-30 00:00:00.000
10 O 2015-03-28 NULL 2015-04-01 00:00:00.000 2015-03-30 00:00:00.000
11 O 2015-03-31 NULL 2015-04-01 00:00:00.000 2015-03-31 00:00:00.000
12 O 2015-03-31 NULL 2015-04-01 00:00:00.000 2015-03-31 00:00:00.000
13 O 2015-04-01 NULL 2015-04-01 00:00:00.000 2015-04-01 00:00:00.000
14 O 2015-04-02 NULL 2015-04-02 00:00:00.000 2015-04-02 00:00:00.000
15 O 2015-04-02 NULL 2015-04-02 00:00:00.000 2015-04-02 00:00:00.000
16 O 2015-04-06 NULL 2015-04-08 00:00:00.000 2015-04-06 00:00:00.000
17 O 2015-04-06 NULL 2015-04-08 00:00:00.000 2015-04-06 00:00:00.000
18 O 2015-04-07 NULL 2015-04-08 00:00:00.000 2015-04-07 00:00:00.000
19 O 2015-04-07 NULL 2015-04-08 00:00:00.000 2015-04-07 00:00:00.000
20 O 2015-04-08 NULL 2015-04-08 00:00:00.000 2015-04-08 00:00:00.000
21 O 2015-04-09 NULL 2015-04-09 00:00:00.000 2015-04-09 00:00:00.000
22 O 2015-04-10 NULL 2015-04-10 00:00:00.000 2015-04-10 00:00:00.000
The reason this took so long to knock up was that I was making assumptions based on your "Scheduled Ship Date" from before. For example, for DT_ID 11, which is on a Tuesday in the future, your previous "Scheduled Ship Date" doesn't do anything to it despite it not being a Monday. For your new "Scheduled Ship Date", you want the same row shunted to a Wednesday. The expected result-set allowed me to see what you were actually expecting.
Thanks.
March 25, 2015 at 7:03 am
Cadavre- You're the best. Thanks again.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply