如何动态更改行？

Question

我正在设法动态地更改最早日期字段的日期，因此如果TotalShipped = TotalOrdered，那么我想取两个字段不相等的最早日期。

这是数据当前样子的一个例子。

CREATE TABLE mytable (
  fsono INT
  , NAME VARCHAR(30)    
  , TS int
  , [TO] int   
  , [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');

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

这就是我想要的结果

CREATE TABLE myresults (
  fsono INT 
  , NAME VARCHAR(30)
  , TS int  
  , [TO] int   
  , [Date] date  
  , Earliest_Date date 
);

INSERT INTO myresults (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');

Answer 1

最后，我使用了一个公共表表达式并在该表上加入了该表。

;with cte1 as (
SELECT Distinct 
sorels.fsono,
min(sorels.fduedate ) Over(Partition by sorels.fsono, Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then 1 else 0 end ) as EarliestDate 
-----------

FROM m2mdata35.dbo.sorels     
INNER JOIN somast on sorels.fsono=somast.fsono  

WHERE LEFT(somast.fstatus,1)<>'C' 
and fsocoord<>'IFP' and fsocoord<>'711' 
and fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty
)

select sorels.fsono, sorels.finumber, sorels.frelease,
Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then 'T' else 'F' end as BackOrd,
sorels.fduedate, cte1.EarliestDate, 
--/*
sorels.fjoqty ,
sorels.fbqty,
fshipbook+fshipbuy+fshipmake as totalshipped,
sorels.fjoqty + sorels.fbook + sorels.fbqty as totalordered,
inwork.fcpro_id,
inwork.fdept,
soitem.fsource as 'soitem.fsource',
sorels.funetprice,
sorels.fshptoaddr as 'sorels.fshptoaddr',
somast.fshptoaddr as 'somast.fshptoaddr',
syaddr.fccompany,
sorels.fstatus ,
jomast.fjobno,
jomast.fstatus AS 'jomast.fstatus', 
soitem.fenumber,
soitem.fquantity,
sorels.forderqty,   
somast.fcustno,
soitem.fac,
soitem.fpartno,
soitem.fpartrev,
incros.fcrosspart,
somast.fcompany,
slcdpmx.fcusrchr1,
left(somast.fcfname,1) as 'somast.fcfname',
somast.fcontact,
syphon.fctitle,
soitem.fgroup as 'soitem.fgroup',
soitem.fprodcl as 'soitem.fprodcl',
soitem.manualplan,
--inmast.fsource as 'soitem.fsource',
inmast.fgroup AS 'inmast.fgroup',
inmast.fbuyer as 'inmast.fbuyer' ,
inmast.fprodcl as 'inmast.fpodcl',
inmast.fnusrcur1 as 'inmast.fnusrcur1' ,
inmast.fproqty as 'inmast.fproqty',
inmast.fbook as 'inmast.fbook',
inmast.fonhand 'inmast.fonhand',
inmast.frevdt 'inmast.frevdt',   
somast.fshipvia,
cspopup.fcpoptext,
somast.fstatus AS 'somast.fstatus',
somast.fsocoord,
somast.fordername,
somast.fcustpono,
somast.fcusrchr1 AS 'somast.fcusrchr1',
somast.fcusrchr2,
somast.fcusrchr3,
somast.forderdate,
sorels.fpostatus,   
sorels.fshipbook,
sorels.fshipbuy,
sorels.fshipmake,
sorels.fshpbefdue,
sorels.fsplitshp,
soitem.fshipitem,
soitem.fmultiple,
sorels.fstatus as 'sorels.fstatus',
sorels.fshipbook + sorels.fshipbuy + sorels.fshipmake AS 'qtyshipped',
sorels.forderqty - sorels.fshipbook + sorels.fshipbuy + sorels.fshipmake AS 'qtyship', 
jodrtg.fnqty_comp - sorels.fshipbook + sorels.fshipbuy + sorels.fshipmake - jodrtg.fnqty_move AS 'availtoship'
--*/

from sorels 
LEFT JOIN cte1 on sorels.fsono=cte1.fsono 
INNER JOIN soitem on sorels.fsono+sorels.finumber=soitem.fsono+soitem.finumber  
INNER JOIN somast on sorels.fsono=somast.fsono  

--/*
LEFT JOIN cspopup ON 'SHIPVIA             '+somast.fshipvia=cspopup.fcpopkey+cspopup.fcpopval
LEFT JOIN inmast on soitem.fpartno+soitem.fpartrev=inmast.fpartno+inmast.frev 
LEFT JOIN incros on inmast.fpartno+inmast.frev=incros.fpartno+incros.fcpartrev
LEFT JOIN jomast on sorels.fstatus=jomast.fjobno 
LEFT JOIN joitem on jomast.fjobno=joitem.fjobno
LEFT JOIN jodrtg on jomast.fjobno=jodrtg.fjobno 
LEFT JOIN inwork on jodrtg.fpro_id=inwork.fcpro_id
LEFT JOIN slcdpmx on somast.fcustno=slcdpmx.fcustno  
LEFT JOIN syphon ON somast.fcontact=syphon.fcontact AND somast.fcustno=syphon.fcsourceid 
LEFT JOIN syaddr ON 'SLCDPM    '+somast.fcustno+'S'+sorels.fshptoaddr=syaddr.fcalias+syaddr.fcaliaskey+syaddr.fcaddrtype+syaddr.fcaddrkey 
--*/

WHERE LEFT(somast.fstatus,1)<>'C' 
and fsocoord<>'IFP' and fsocoord<>'711' 

Order by cte1.EarliestDate, sorels.fsono, BackOrd, sorels.finumber, sorels.frelease

Answer 2

这里有一种方法。

Select  AllData.*,
        earliest.mismatch
from    #mytable    AllData
    left join
        (Select NAME, 
                min(date) mismatch
        from    #mytable
        where   [ts] <> [TO]
        group by
                NAME) earliest         on AllData.Name = earliest.Name