T-sql help to change ship date on the report

  • Hello All,

    I've a report that manages Scheduled Ship Date based on the Requested Delivery Date. We normally ship every Monday of the week so I've Scheduled Ship Date set to Monday. But for some of the dates this does not work. For instance when the REquested Delivery Date is 2015-07-29, the Scheduled Ship Date does not show following Monday i.e 2015-08-03.

    Here is my DDL:

    USE tempdb;

    GO

    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

    );

    INSERT INTO @TEST_DATA (JobStatus, JobNumber,ExpectedDate,LastShippedDate)

    VALUES

    ('S', 'J012345','2015-07-10','2015-07-10')

    ,('S', 'J012346','2015-07-15','2015-07-17')

    ,('S', 'J012346','2015-07-17','2015-07-15')

    ,('S', 'J012346','2015-07-08','2015-07-10')

    ,('O', 'J012347','2015-08-04',NULL)

    ,('O', 'J012347','2015-07-24',NULL)

    ,('O', 'J012347','2015-07-31',NULL)

    ,('O', 'J012347','2015-07-27',NULL)

    ,('O', 'J012348','2015-07-21',NULL)

    ,('O', 'J012349','2015-07-31',NULL)

    ,('O', 'J012350','2015-08-07',NULL)

    ,('O', 'J012351','2015-08-14',NULL)

    ,('O', 'J012362','2015-08-28',NULL)

    ,('O', 'J012363','2015-07-22',NULL)

    ,('O', 'J012364','2015-07-24',NULL)

    ,('O', 'J012365','2015-07-31',NULL)

    ,('O', 'J012366','2015-08-21',NULL)

    ,('O', 'J012372','2015-07-27',NULL)

    ,('O', 'J012378','2015-07-29',NULL)

    ,('O', 'J012367','2015-08-11',NULL)

    ,('O', 'J012367','2015-07-30',NULL)

    ,('O', 'J012367','2015-09-18',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

    order by [Requested Delivery Date]

  • can you amend this

    http://www.sqlservercentral.com/Forums/FindPost1671071.aspx

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • J Livingston SQL (7/23/2015)


    can you amend this

    http://www.sqlservercentral.com/Forums/FindPost1671071.aspx

    hmmmmmmmmmmmmm

    Didn't like the answer the first time, I guess.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • SSRS Newbie (7/23/2015)


    Hello All,

    I've a report that manages Scheduled Ship Date based on the Requested Delivery Date. We normally ship every Monday of the week so I've Scheduled Ship Date set to Monday. But for some of the dates this does not work. For instance when the REquested Delivery Date is 2015-07-29, the Scheduled Ship Date does not show following Monday i.e 2015-08-03.

    Here is my DDL:

    USE tempdb;

    GO

    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

    );

    INSERT INTO @TEST_DATA (JobStatus, JobNumber,ExpectedDate,LastShippedDate)

    VALUES

    ('S', 'J012345','2015-07-10','2015-07-10')

    ,('S', 'J012346','2015-07-15','2015-07-17')

    ,('S', 'J012346','2015-07-17','2015-07-15')

    ,('S', 'J012346','2015-07-08','2015-07-10')

    ,('O', 'J012347','2015-08-04',NULL)

    ,('O', 'J012347','2015-07-24',NULL)

    ,('O', 'J012347','2015-07-31',NULL)

    ,('O', 'J012347','2015-07-27',NULL)

    ,('O', 'J012348','2015-07-21',NULL)

    ,('O', 'J012349','2015-07-31',NULL)

    ,('O', 'J012350','2015-08-07',NULL)

    ,('O', 'J012351','2015-08-14',NULL)

    ,('O', 'J012362','2015-08-28',NULL)

    ,('O', 'J012363','2015-07-22',NULL)

    ,('O', 'J012364','2015-07-24',NULL)

    ,('O', 'J012365','2015-07-31',NULL)

    ,('O', 'J012366','2015-08-21',NULL)

    ,('O', 'J012372','2015-07-27',NULL)

    ,('O', 'J012378','2015-07-29',NULL)

    ,('O', 'J012367','2015-08-11',NULL)

    ,('O', 'J012367','2015-07-30',NULL)

    ,('O', 'J012367','2015-09-18',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

    order by [Requested Delivery Date]

    Take a look at the following, which I added some extra fields to, to help illustrate what's taking place:

    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

    );

    INSERT INTO @TEST_DATA (JobStatus, JobNumber,ExpectedDate,LastShippedDate)

    VALUES

    ('S', 'J012345','2015-07-10','2015-07-10')

    ,('S', 'J012346','2015-07-15','2015-07-17')

    ,('S', 'J012346','2015-07-17','2015-07-15')

    ,('S', 'J012346','2015-07-08','2015-07-10')

    ,('O', 'J012347','2015-08-04',NULL)

    ,('O', 'J012347','2015-07-24',NULL)

    ,('O', 'J012347','2015-07-31',NULL)

    ,('O', 'J012347','2015-07-27',NULL)

    ,('O', 'J012348','2015-07-21',NULL)

    ,('O', 'J012349','2015-07-31',NULL)

    ,('O', 'J012350','2015-08-07',NULL)

    ,('O', 'J012351','2015-08-14',NULL)

    ,('O', 'J012362','2015-08-28',NULL)

    ,('O', 'J012363','2015-07-22',NULL)

    ,('O', 'J012364','2015-07-24',NULL)

    ,('O', 'J012365','2015-07-31',NULL)

    ,('O', 'J012366','2015-08-21',NULL)

    ,('O', 'J012372','2015-07-27',NULL)

    ,('O', 'J012378','2015-07-29',NULL)

    ,('O', 'J012367','2015-08-11',NULL)

    ,('O', 'J012367','2015-07-30',NULL)

    ,('O', 'J012367','2015-09-18',NULL)

    ;

    SELECT

    J.DT_ID

    ,J.JobStatus /*O-Open, S-Shipped, I-Invoiced*/

    ,J.ExpectedDate 'Requested Delivery Date'

    ,J.LastShippedDate

    ,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]

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson,

    Thank you for your response. The code you suggested works. I need to assign a Scheduled Ship Date to the closest Monday. For example- if the Requested Delivery Date is 2015-08-04, I need to show Scheduled Ship Date as 2015-08-03. Can we do this using T-SQL? Thanks.

  • SSRS Newbie (7/23/2015)


    sgmunson,

    Thank you for your response. The code you suggested works. I need to assign a Scheduled Ship Date to the closest Monday. For example- if the Requested Delivery Date is 2015-08-04, I need to show Scheduled Ship Date as 2015-08-03. Can we do this using T-SQL? Thanks.

    what are your rules for "closest Monday".....say for instance requested date was 2015-08-06...do you want to return 2015-08-03 or 2015-08-10?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Yes, if the requested date was 2015-08-06 then the return date will be 2015-08-10. Thank you for your prompt response.

  • SSRS Newbie (7/23/2015)


    Yes, if the requested date was 2015-08-06 then the return date will be 2015-08-10. Thank you for your prompt response.

    can you clarify your rules please.....do we exclude weekends (Sat/Sun) ...what about holidays (in Uk we have several "bank holidays" on a Monday)...??

    am thinking you may need to consider a "shipping date" calendar to assist you with this.

    your thoughts?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • SSRS Newbie (7/23/2015)


    sgmunson,

    Thank you for your response. The code you suggested works. I need to assign a Scheduled Ship Date to the closest Monday. For example- if the Requested Delivery Date is 2015-08-04, I need to show Scheduled Ship Date as 2015-08-03. Can we do this using T-SQL? Thanks.

    Yes, we can do it with T-SQL, but as J Livingston SQL indicated, we need to know EXACTLY HOW to decide which of the two Mondays is the best choice for any given Expected Date. If we have to take holidays into consideration, then you're going to need a holiday table, and while it's not rocket science, it will require a fair amount of thought go into how that should work. You have to worry about how it will get updated, and you'll have to know for certain whether or not human decisions by the company can affect the holiday dates. You should also consider that chances are, there may well be other IT-related reporting or processing that would benefit from knowing about holidays, so don't just worry about Monday holidays. Yes, I realize that will significantly broaden the scope of this problem, but it's unavoidable. Let us know...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • 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]

  • 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)

Viewing 11 posts - 1 through 10 (of 10 total)

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