/*
--原数据
ID fldMonth0 fldmonth1 fldmonth2 fldmonth3 fldmonth4 fldmonth5 fldMOQ
---- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 20 3144 5100 0 4800 8800 2000 --希望得到的结果
ID fldMonth0 fldmonth1 fldmonth2 fldmonth3 fldmonth4 fldmonth5
---- ----------- ----------- ----------- ----------- ----------- -----------
1 2000 2000 6000 0 4000 8000
*/说明:一般下订单都会有个MOQ(最小定单数量),假设现在最小订单数量为2000,第一个月为了20,要买2000,最二个月需要3144,但因为第一个月多订的1980,需求变成1164,所以只要订2000,以此类推。
希望使用SQL2000可用的简单语句实行。谢谢。
case when fldMPQ > 0 then case when (fldmonth0 + fldmonth1 - (case when fldmonth0 > 0 then (floor(fldmonth0 / fldMPQ) + 1) * fldMPQ else 0 end)) > 0
then (floor((fldmonth0 + fldmonth1 - (case when fldmonth0 > 0 then (floor(fldmonth0/ fldMPQ) + 1) * fldMPQ else 0 end)) / fldMPQ) + 1 ) * fldMPQ else 0 end else fldmonth1 end as Month1,
case when fldMPQ > 0 then case when (fldmonth0 + fldmonth1 + fldMonth2 - (case when fldmonth0 + fldMonth1 > 0 then (floor((fldmonth0 + fldMonth1)/ fldMPQ) + 1) * fldMPQ else 0 end)) > 0
then (floor((fldmonth0 + fldmonth1 + fldMonth2 - (case when fldmonth0 + fldMonth1 > 0 then (floor((fldmonth0 + fldMonth1) / fldMPQ) + 1) * fldMPQ else 0 end)) / fldMPQ) + 1 ) * fldMPQ else 0 end else fldmonth2 end as Month2,
case when fldMPQ > 0 then case when (fldmonth0 + fldmonth1 + fldMonth2 + fldMonth3 - (case when fldmonth0 + fldMonth1 + fldMonth2 > 0 then (floor((fldmonth0 + fldMonth1 + fldMonth2)/ fldMPQ) + 1) * fldMPQ else 0 end)) > 0
then (floor((fldmonth0 + fldmonth1 + fldMonth2 + fldMonth3 - (case when fldmonth0 + fldMonth1 + fldMonth2 > 0 then (floor((fldmonth0 + fldMonth1 + fldMonth2) / fldMPQ) + 1) * fldMPQ else 0 end)) / fldMPQ) + 1 ) * fldMPQ else 0 end else fldmonth3 end as Month3,
case when fldMPQ > 0 then case when (fldmonth0 + fldmonth1 + fldMonth2 + fldMonth3 + fldMonth4 - (case when fldmonth0 + fldMonth1 + fldMonth2 + fldMonth3 > 0 then (floor((fldmonth0 + fldMonth1 + fldMonth2 + fldMonth3)/ fldMPQ) + 1) * fldMPQ else 0 end)) > 0
then (floor((fldmonth0 + fldmonth1 + fldMonth2 + fldMonth3 + fldMonth4 - (case when fldmonth0 + fldMonth1 + fldMonth2 + fldMonth3 > 0 then (floor((fldmonth0 + fldMonth1 + fldMonth2 + fldMonth3) / fldMPQ) + 1) * fldMPQ else 0 end)) / fldMPQ) + 1 ) * fldMPQ else 0 end else fldmonth4 end as Month4,
case when fldMPQ > 0 then case when (fldmonth0 + fldmonth1 + fldMonth2 + fldMonth3 + fldMonth4 + fldMonth5 - (case when fldmonth0 + fldMonth1 + fldMonth2 + fldMonth3 + fldMonth4 > 0 then (floor((fldmonth0 + fldMonth1 + fldMonth2 + fldMonth3 + fldMonth4)/ fldMPQ) + 1) * fldMPQ else 0 end)) > 0
then (floor((fldmonth0 + fldmonth1 + fldMonth2 + fldMonth3 + fldMonth4 + fldMonth5 - (case when fldmonth0 + fldMonth1 + fldMonth2 + fldMonth3 + fldMonth4 > 0 then (floor((fldmonth0 + fldMonth1 + fldMonth2 + fldMonth3 + fldMonth4) / fldMPQ) + 1) * fldMPQ else 0 end)) / fldMPQ) + 1 ) * fldMPQ else 0 end else fldmonth5 end as Month5
from #temp1
自己做出来了