Change Ship date on SSRS report

Question

Post reply

Change Ship date on SSRS report

SSRS Newbie

Hall of Fame

Points: 3244
More actions
March 24, 2015 at 9:17 am

#296323

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

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

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

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.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SSRS Newbie Hall of Fame Points: 3244 More actions · Answer 3

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

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: -