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)