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)