none
How to dynamically change a date field? RRS feed

  • Question

  • I'm trying to find a way to dynamically change the dates of the Earliest Date field so if the TotalShipped = TotalOrdered then I want to take the earliest date where the two fields don't equal each other. This is an example of what I'm trying to do.

    +----+----------+-----+-----------+----------+-----------
    |    | NAME     | TS  | TO        | Date     |Earliest Date  |
    +----+----------+-----+-----------+----------+-----------
    |  1 | 039069   |  1  | 1         |   3-19   |3-20      |
    |  2 | 039069   |  1  | 1         |  3-19    |3-20      |
    |  3 | 039069   |  1  | 0         |  3-20    |3-20      |
    |  4 | 039069   |  1  | 0         |  3-21    |3-20      |
    ---------------------------------------------------------

    SELECT 
    sorels.fsono,
    sorels.finumber,
    sorels.frelease,
    fshipbook+fshipbuy+fshipmake as totalshipped,
    fjoqty + fbook + fbqty as totalordered,
    sorels.fduedate
    
    FROM m2mdata35.dbo.sorels   
     
    INNER JOIN somast on sorels.fsono=somast.fsono   
    INNER JOIN soitem on sorels.fsono=soitem.fsono AND sorels.finumber=soitem.finumber   
    LEFT JOIN inmastx on soitem.fpartno=inmastx.fpartno and soitem.fpartrev=inmastx.frev  
    LEFT JOIN jomast on sorels.fstatus=jomast.fjobno 
    LEFT JOIN slcdpmx on somast.fcustno=slcdpmx.fcustno  
    LEFT JOIN syphon ON somast.fcontact=syphon.fcontact AND somast.fcustno=syphon.fcsourceid 
     
    WHERE LEFT(somast.fstatus,1)<>'C' and fsocoord<>'IFP' and fsocoord<>'711' 
    and somast.fsono='039069' 
    
    Order By sorels.fsono, sorels.fduedate, sorels.finumber, sorels.frelease


    • Edited by David9501 Monday, March 25, 2019 2:10 PM
    Monday, March 25, 2019 2:09 PM

All replies

  • Not clear what you mean here, please post more details like an example table with data as DML statement and the expected result.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, March 25, 2019 4:26 PM
  • CREATE TABLE mytable(
       fsono             INT 
      ,NAME          VARCHAR(30)
      ,TS            BIT  
      ,[TO]            BIT   
      ,[Date]         date  
      ,Earliest_Date date 
      
    );
    INSERT INTO mytable(fsono,NAME,TS,[TO],[Date],Earliest_Date ) VALUES
      (1,'039069',1,1,'2019-3-19','2019-3-20' )
    ,( 2,'039069',1,1,'2019-3-19','2019-3-20' )
    ,( 3,'039069',1,0,'2019-3-20','2019-3-20' )
    ,( 4,'039069',1,0,'2019-3-21','2019-3-20' );
    ;with mycte as (
    select *, SUM(Case when TS=[TO]  then 0 else 1 end) Over(Partition by NAME Order by Date,fsono )  chk
    from mytable)
    
    Select fsono,NAME,TS,[TO],[Date]
    , Max(Case when chk=1 then [Date] else null end) Over(Partition by NAME) EarliestDate 
    from mycte
    
    
    
    drop table mytable

    Monday, March 25, 2019 5:48 PM
    Moderator
  • CREATE TABLE mytable(
       fsono             INT 
      ,NAME          VARCHAR(30)
      ,TS            BIT  
      ,[TO]            BIT   
      ,[Date]         date  
    
      
    );
    INSERT INTO mytable(fsono,NAME,TS,[TO],[Date]) VALUES
      (1,'039069',1,1,'2019-3-19')
    ,( 2,'039069',1,1,'2019-3-19')
    ,( 3,'039069',1,0,'2019-3-20')
    ,( 4,'039069',1,0,'2019-3-21')
    ;
    This is how the data currently looks. I want when whenever TS = TO to take the earliest date of the next row where TS != TO. So it looks like this. Once the data is updated for row 3 and TS = TO the Earliest Date will change the next earliest date 3-21.
    +----+----------+-----+-----------+----------+-----------
    |    | NAME     | TS  | TO        | Date     |Earliest Date  |
    +----+----------+-----+-----------+----------+-----------
    |  1 | 039069   |  1  | 1         |   3-19   |3-20      |
    |  2 | 039069   |  1  | 1         |  3-19    |3-20      |
    |  3 | 039069   |  1  | 0         |  3-20    |3-20      |
    |  4 | 039069   |  1  | 0         |  3-21    |3-20      |
    ---------------------------------------------------------


    • Edited by David9501 Monday, March 25, 2019 7:10 PM
    Monday, March 25, 2019 7:03 PM
  • CREATE TABLE mytable(
       fsono             INT 
      ,NAME          VARCHAR(30)
      ,TS            BIT  
      ,[TO]            BIT   
      ,[Date]         date  
    
      
    );
    INSERT INTO mytable(fsono,NAME,TS,[TO],[Date]) VALUES
      (1,'039069',1,1,'2019-3-19')
    ,( 2,'039069',1,1,'2019-3-19')
    ,( 3,'039069',1,1,'2019-3-20')
    ,( 4,'039069',1,0,'2019-3-21')
    ;
    
    
    ;with mycte as (
    select *, SUM(Case when TS=[TO]  then 0 else 1 end) Over(Partition by NAME Order by Date,fsono )  chk
    from mytable)
    
    Select fsono,NAME,TS,[TO],[Date]
    , Max(Case when chk=1 then [Date] else null end) Over(Partition by NAME) EarliestDate 
    from mycte
    
    drop table mytable

    Monday, March 25, 2019 8:03 PM
    Moderator
  • Hi David9501,

     

    Please try following script to see if it satisfies your requirement.

    If Object_ID('mytable','U') Is Not Null Drop Table mytable
    go
    CREATE TABLE mytable(
    fsono INT 
    ,NAME VARCHAR(30)
    ,TS BIT  
    ,[TO] BIT   
    ,[Date] date   
    );
    INSERT INTO mytable(fsono,NAME,TS,[TO],[Date]) VALUES
      (1,'039069',1,1,'2019-3-19')
    ,( 2,'039069',1,1,'2019-3-19')
    ,( 3,'039069',1,0,'2019-3-20')
    ,( 4,'039069',1,0,'2019-3-21');
    
    ;with cte as (
    select name,min(Date) as [Earliest Date]  
    from mytable 
    where TS<>[TO] 
    group by name 
    )
    select b.fsono,b.NAME,b.TS,b.[TO],
    convert(varchar(5),b.Date,110) as Date ,
    convert(varchar(5),a.[Earliest Date] ,110) as [Earliest Date]
    from cte a join mytable b 
    on a.NAME=b.NAME
    /*
    fsono       NAME                           TS    TO    Date  Earliest Date
    ----------- ------------------------------ ----- ----- ----- -------------
    1           039069                         1     1     03-19 03-20
    2           039069                         1     1     03-19 03-20
    3           039069                         1     0     03-20 03-20
    4           039069                         1     0     03-21 03-20
    */
    


     

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 26, 2019 6:36 AM