在更新字段时进行计算

Question

Origin  Dest  Date        Amount 50% Due 
92509  0021  2013-07-30 00:00:00.000   5.37  0.00 
92509  0021  2013-07-30 00:00:00.000   5.37  0.00 
92509  0021  2013-07-30 00:00:00.000   5.37  0.00 
92509  0021  2013-07-31 00:00:00.000   5.37  2.69 
92509  0021  2013-07-31 00:00:00.000   5.37  2.69 
92509  0021  2013-07-31 00:00:00.000   5.37  2.69 
92509  0021  2013-08-01 00:00:00.000   5.37  2.69 
92509  0021  2013-08-01 00:00:00.000   5.37  2.69 
42101  0029  2013-03-06 00:00:00.000   6.06  0.00 
42101  0029  2013-03-06 00:00:00.000   6.06  0.00 
42101  0029  2013-03-07 00:00:00.000   6.06  3.03 
42101  0029  2013-03-07 00:00:00.000   6.06  3.03       
42101  0030  2013-03-06 00:00:00.000   6.06  0.00 
42101  0030  2013-03-06 00:00:00.000   6.06  0.00 
42101  0030  2013-03-07 00:00:00.000   6.06  3.03 
42101  0030  2013-03-07 00:00:00.000   6.06  3.03 

所以我有一个类似于上面显示的东西的表。现在，50％到期字段是空的。我需要用上面显示的值填充该字段。

50％截止字段应填充“金额”字段中存在的一半的值。但是，在初始日期（2013年7月30日00:00:00:00）之前，它应该填写零，并且在连续的日期内它应该填写“金额”字段中的一半。

我有很多这样的行需要更新。还有一些行具有不同的来源和目的地。

我正在处理一些货运包裹。数据描述连续几天从同一来源发送到同一目的地的地块。连续几天发送的包裹本身可能已经在初始日期一起发送。因此，我正试图为连续几天从同一来源发送到相同目的地的那些包裹生成索赔。而50％的到期将是索赔！

我对SQL相当陌生！这对我来说似乎很复杂。请帮忙。

Answer 1

如果要更新对前一天的条目都起源/目标组合，那么你可以这样做：

update t 
set [50% due]=coalesce(cast(p.amount/2.0 as smallmoney),0.00) 
from [table] t 
left join [table] p 
    on t.origin=p.origin and t.dest=p.dest 
    and dateadd(D,-1,cast(t.[date] as date))=cast(p.[date] as date) 

或使用CTE和LAG在SQL 2012和更高版本：

;with previous as 
(
    select origin,dest,[date] 
    ,LAG(cast([date] as date),1,cast([date] as date)) OVER (PARTITION BY origin,dest ORDER BY cast([date] as date)) as previous 
    from (select distinct origin, dest, [date] 
      from [table]) a 
) 
update t 
set [50% Due]=case when dateadd(D,-1,cast(t.[date] as date))<>cte.previous then 0.00 else cast(t.[amount]/2.0 as smallmoney) end 
from [table] t 
join previous cte 
    on cte.origin=t.origin and cte.dest=t.dest and cte.[date]=t.[date] 

如果要更新基于最早的一天，发生了原点/目标组合的所有记录，那么这将在SQL Server的工作：

;with earliest as 
(
    select origin,dest,min(cast([date] as date)) earliest 
    from [table] 
    group by origin,dest 
) 
update t 
set [50% Due]=case when cast(t.[date] as date)=cte.earliest then 0.00 else cast(t.[amount]/2.0 as smallmoney) end 
from [table] t 
join earliest cte 
    on cte.origin=t.origin and cte.dest=t.dest 

如果您想仅根据表中最早的日期更新所有记录，并且您不关心orgin/dest组合，那么您不需要cte将最早的日期分组，而只需执行在更新中进行比较。

UPDATE t 
set [50% Due]=case when cast(t.[date] as date)=(select min(cast(t.date as date))) then 0.00 else cast(t.[amount]/2.0 as smallmoney) end