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]