各位,难道请教:
我想在查询后结结果集中合理的更新某一个字段值。比如以下面的数据为例:在BuildDate靠前的那一行,也就是第二行,它的BuildStatus 是Ongoing.(Ongoing的条件就是RemaingQty>0)。我想实现的是:当日期靠前的buildstatus 还是ongoing 的情况下,靠后日期的ExcessQty和RemainingQty自动写null.可是现在我写的代码没有考虑日期排序。全部算出来了。所以是不准确的。
BU Assy# Plan Qty Build Date Material BPDemand OnlineInventory WIPDemand ExcessQty RemaingQty IssuedQty BuildStatus AddWho AddTime Re
N7K 68-2516-18 54 2011-08-24 800-29096-07 54.000 129.000 56.000 73.000 -19.000 0.000 Close Hanson 2011-08-23 00:00:00 NULL
N7K 68-2516-18 100 2011-08-20 800-29096-07 100.000 129.000 56.000 73.000 27.000 0.000 Ongoing Hanson 2011-08-23 00:00:00 NULL
我有一段代码如下:请大家帮忙看看,分析一下怎么改可以实现我所说的那种效果。谢谢! SELECT a.BU,
a.Assy#,
a.[Plan Qty],
a.[Build Date],
b.[Component P/N] AS Material,
ISNULL(b.[Qty Per] * a.[Plan Qty], 0.000) AS BPDemand,
ISNULL(SUM(c.Unrestricted), 0.000) AS OnlineInventory,
ISNULL(b.[Qty Per] * d.WIP_Qty, 0.000) AS WIPDemand,
ISNULL(SUM(c.Unrestricted) - b.[Qty Per] * d.WIP_Qty, 0.000) AS ExcessQty,
ISNULL((b.[Qty Per] * a.[Plan Qty]) - (SUM(c.Unrestricted) - (b.[Qty Per] * d.WIP_Qty)),ISNULL(b.[Qty Per] * a.[Plan Qty], 0.000)) AS RemaingQty,
ISNULL(SUM(e.Qty), 0.000) AS IssuedQty
--BuildStatus ='',[AddWho]=@AddWho,[AddTime]=@AddTime
FROM dbo.Sup_BuildPlan AS a left JOIN
dbo.Sup_BOM AS b ON a.[Assy#] = b.[Parent P/N] left JOIN
dbo.Sup_OnlineInventory AS c ON c.Material = b.[Component P/N] inner JOIN
dbo.Sup_WIP AS d ON d.[Assy#] = a.[Assy#] AND d.[Assy#] = b.[Parent P/N] AND
d.BU = a.BU left JOIN
dbo.SUP_PullListHistory AS e ON e.[Assy#] = a.[Assy#] AND e.BU = a.BU AND e.[Plan Qty] = a.[Plan Qty] AND
e.PartNum = b.[Component P/N] AND e.PickFlag = 1 AND c.SLoc = e.Sloc
GROUP BY a.BU, a.Assy#, a.[Build Date], a.[Plan Qty], b.[Component P/N], b.[Qty Per],d.WIP_Qty
ORDER BY a.[Build Date], Material
我想在查询后结结果集中合理的更新某一个字段值。比如以下面的数据为例:在BuildDate靠前的那一行,也就是第二行,它的BuildStatus 是Ongoing.(Ongoing的条件就是RemaingQty>0)。我想实现的是:当日期靠前的buildstatus 还是ongoing 的情况下,靠后日期的ExcessQty和RemainingQty自动写null.可是现在我写的代码没有考虑日期排序。全部算出来了。所以是不准确的。
BU Assy# Plan Qty Build Date Material BPDemand OnlineInventory WIPDemand ExcessQty RemaingQty IssuedQty BuildStatus AddWho AddTime Re
N7K 68-2516-18 54 2011-08-24 800-29096-07 54.000 129.000 56.000 73.000 -19.000 0.000 Close Hanson 2011-08-23 00:00:00 NULL
N7K 68-2516-18 100 2011-08-20 800-29096-07 100.000 129.000 56.000 73.000 27.000 0.000 Ongoing Hanson 2011-08-23 00:00:00 NULL
我有一段代码如下:请大家帮忙看看,分析一下怎么改可以实现我所说的那种效果。谢谢! SELECT a.BU,
a.Assy#,
a.[Plan Qty],
a.[Build Date],
b.[Component P/N] AS Material,
ISNULL(b.[Qty Per] * a.[Plan Qty], 0.000) AS BPDemand,
ISNULL(SUM(c.Unrestricted), 0.000) AS OnlineInventory,
ISNULL(b.[Qty Per] * d.WIP_Qty, 0.000) AS WIPDemand,
ISNULL(SUM(c.Unrestricted) - b.[Qty Per] * d.WIP_Qty, 0.000) AS ExcessQty,
ISNULL((b.[Qty Per] * a.[Plan Qty]) - (SUM(c.Unrestricted) - (b.[Qty Per] * d.WIP_Qty)),ISNULL(b.[Qty Per] * a.[Plan Qty], 0.000)) AS RemaingQty,
ISNULL(SUM(e.Qty), 0.000) AS IssuedQty
--BuildStatus ='',[AddWho]=@AddWho,[AddTime]=@AddTime
FROM dbo.Sup_BuildPlan AS a left JOIN
dbo.Sup_BOM AS b ON a.[Assy#] = b.[Parent P/N] left JOIN
dbo.Sup_OnlineInventory AS c ON c.Material = b.[Component P/N] inner JOIN
dbo.Sup_WIP AS d ON d.[Assy#] = a.[Assy#] AND d.[Assy#] = b.[Parent P/N] AND
d.BU = a.BU left JOIN
dbo.SUP_PullListHistory AS e ON e.[Assy#] = a.[Assy#] AND e.BU = a.BU AND e.[Plan Qty] = a.[Plan Qty] AND
e.PartNum = b.[Component P/N] AND e.PickFlag = 1 AND c.SLoc = e.Sloc
GROUP BY a.BU, a.Assy#, a.[Build Date], a.[Plan Qty], b.[Component P/N], b.[Qty Per],d.WIP_Qty
ORDER BY a.[Build Date], Material
(case when exists(select 1 from tb where bu = t.bu and builddate < t.builddate and buildstatus = 'ongoing')
then null else excessqty end) excessqty,
(case when exists(select 1 from tb where bu = t.bu and builddate < t.builddate and buildstatus = 'ongoing')
then null else RemainingQty end) RemainingQty
from tb t
...
我的意思是将查询出来的结果按时间排序
时间靠前的按我写的公式算,靠后的就写NULL。代码怎么写?