Set Shipment date to Monday instead of Thursday.

  • Hello All,

    In my below T-sql the shipment date is set to next Thursday, if the shipment is missed in current week. Now, I've to change the t-sql code to change the shipment dates to Monday instead of Thursday. Can somebody give me a hand on this please? Thanks.

    Regards

    USE tempdb;

    GO

    DECLARE @Date DATE;

    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','2014-10-23','2014-10-23')

    ,('O', 'J012346','2014-10-16',NULL)

    ,('O', 'J012347','2014-10-17',NULL)

    ,('O', 'J012348','2014-10-22',NULL)

    ,('O', 'J012349','2014-10-02',NULL)

    ,('O', 'J012350','2014-10-09',NULL)

    ,('O', 'J012351','2014-09-04',NULL)

    ,('O', 'J012352','2014-09-11',NULL)

    ,('O', 'J012353','2014-09-18',NULL)

    ,('O', 'J012354','2014-09-18',NULL)

    ,('O', 'J012355','2014-09-14',NULL)

    ,('O', 'J012356','2014-09-16',NULL)

    ,('O', 'J012357','2014-09-19',NULL)

    ,('O', 'J012358','2014-09-01',NULL)

    ,('O', 'J012359','2014-09-06',NULL)

    ,('O', 'J012360','2014-09-07',NULL)

    ,('O', 'J012361','2014-09-12',NULL)

    ,('O', 'J012362','2014-09-16',NULL);

    SELECT

    J.DT_ID

    ,J.JobStatus

    ,J.ExpectedDate

    ,J.LastShippedDate

    ,'New 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 THURSDAY */

    WHEN j.JobStatus <>'S' AND DATEDIFF(DAY,CAST(J.ExpectedDate AS DATE), @Date) >= 0 THEN

    CASE

    /* SET SHIPMENTS TO NEXT THURSDAY (NEED TO SET SHIPMENTS TO NEXT MONDAY)*/

    WHEN DATEDIFF(dd,0,@Date)%7 = 0THEN DATEADD(DAY,3,@Date)

    WHEN DATEDIFF(dd,0,@Date)%7 = 1THEN DATEADD(DAY,2,@Date)

    WHEN DATEDIFF(dd,0,@Date)%7 = 2THEN DATEADD(DAY,1,@Date)

    WHEN DATEDIFF(dd,0,@Date)%7 = 3THEN DATEADD(DAY,7,@Date)

    WHEN DATEDIFF(dd,0,@Date)%7 = 4THEN DATEADD(DAY,6,@Date)

    WHEN DATEDIFF(dd,0,@Date)%7 = 5THEN DATEADD(DAY,5,@Date)

    WHEN DATEDIFF(dd,0,@Date)%7 = 6THEN DATEADD(DAY,4,@Date)

    END

    /* FUTURE SHIPMENTS */

    ELSE CAST(J.ExpectedDate AS DATE)

    END

    FROM @TEST_DATA J

  • Please help guys.

  • Based on your test data is looks like this works:

    WHEN j.JobStatus <>'S' AND DATEDIFF(DAY,CAST(J.ExpectedDate AS DATE), @Date) >= 0 THEN

    CASE

    /* SET SHIPMENTS TO NEXT THURSDAY (NEED TO SET SHIPMENTS TO NEXT MONDAY)*/

    WHEN DATEDIFF(DAY,0,@Date)%7 = 0THEN DATEADD(DAY,7,@Date)

    WHEN DATEDIFF(DAY,0,@Date)%7 = 1THEN DATEADD(DAY,6,@Date)

    WHEN DATEDIFF(DAY,0,@Date)%7 = 2THEN DATEADD(DAY,5,@Date)

    WHEN DATEDIFF(DAY,0,@Date)%7 = 3THEN DATEADD(DAY,4,@Date)

    WHEN DATEDIFF(DAY,0,@Date)%7 = 4THEN DATEADD(DAY,3,@Date)

    WHEN DATEDIFF(DAY,0,@Date)%7 = 5THEN DATEADD(DAY,2,@Date)

    WHEN DATEDIFF(DAY,0,@Date)%7 = 6THEN DATEADD(DAY,1,@Date)

    END

  • I am a little bit scared that you have code that will set it to Thursday but you can't figure out how to modify the code so it returns a different day. I am guessing you don't understand the current code because the change should be pretty simple.

    Take a look at this blog and see it helps you. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean- Thank you for the link. I will go over it. I'm new to T-SQL

    Jack- Thanks.

  • Hi Jack,

    The T-SQL you suggested works fine. But when the ExpectedDate has (10/28/2014 or 10/29/2014 or 10/29/2014 or 10/30/2014) the date does not change to Monday (11/03/2014). Can you please help? Here is the T-SQL

    USE tempdb;

    GO

    DECLARE @Date DATE;

    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','2014-10-23','2014-10-23')

    ,('O', 'J012346','2014-10-16',NULL)

    ,('O', 'J012347','2014-10-17',NULL)

    ,('O', 'J012348','2014-10-22',NULL)

    ,('O', 'J012349','2014-10-02',NULL)

    ,('O', 'J012350','2014-10-09',NULL)

    ,('O', 'J012362','2014-10-28',NULL)

    ,('O', 'J012362','2014-10-29',NULL)

    ,('O', 'J012362','2014-10-30',NULL)

    ,('O', 'J012362','2014-10-31',NULL);

    SELECT

    J.DT_ID

    ,J.JobStatus

    ,J.ExpectedDate

    ,J.LastShippedDate

    ,'New 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 THURSDAY */

    WHEN j.JobStatus <>'S' AND DATEDIFF(DAY,CAST(J.ExpectedDate AS DATE), @Date) >= 0 THEN

    CASE

    /* SET SHIPMENTS TO NEXT THURSDAY (NEED TO SET SHIPMENTS TO NEXT MONDAY)*/

    WHEN DATEDIFF(dd,0,@Date)%7 = 0THEN DATEADD(DAY,7,@Date)

    WHEN DATEDIFF(dd,0,@Date)%7 = 1THEN DATEADD(DAY,6,@Date)

    WHEN DATEDIFF(dd,0,@Date)%7 = 2THEN DATEADD(DAY,5,@Date)

    WHEN DATEDIFF(dd,0,@Date)%7 = 3THEN DATEADD(DAY,4,@Date)

    WHEN DATEDIFF(dd,0,@Date)%7 = 4THEN DATEADD(DAY,3,@Date)

    WHEN DATEDIFF(dd,0,@Date)%7 = 5THEN DATEADD(DAY,2,@Date)

    WHEN DATEDIFF(dd,0,@Date)%7 = 6THEN DATEADD(DAY,1,@Date)

    END

    /* FUTURE SHIPMENTS */

    ELSE CAST(J.ExpectedDate AS DATE)

    END

    FROM @TEST_DATA J

  • The difference in days between today (2014-10-27) and the dates you have listed is not greater than or equal to 0, so the code will not change the expected ship date.

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

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