更新列细胞%
问题描述:
我有4个百万行我的表中有一个空白栏要求取消预订分为3个年份2010,2011和2012年更新列细胞%
Booking_Skey BookingNumber ArrivalDate DepartureDate BookingDate CancelledDate BookingValue PitchType_Skey Site_Skey
124532 B00124532 2010-12-31 2011-01-02 2010-12-31 NULL 10.00 7 2
我需要做的是建立一个什么代码,我可以改变取消指令的%,为一年,我想更新:
所以2010年我必须满足下列条件
--Cancelled Bookings--
- 在2010年取消总预订的8%,取消日期可以等于或少于抵达日期并等于或大于预订日期
- 8%的20%取消同一天,到达日期 的8%
- 20%的到达日期之前取消了一天 的8%
- 20%的到达日期
- 的取消的其余部分是前7天内取消随机在1至90天之间
。
USE Occupancy
SELECT ArrivalDate,
DATEADD(day,
CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0 and 0.92 THEN NULL ELSE
CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.92 and 0.94 THEN 0 ELSE
CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.94 and 0.96 THEN -1 ELSE
CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.96 and 0.98 THEN -7 ELSE
Round(Rand(CHECKSUM(NEWID())) * -90,0) END END END END, ArrivalDate) AS DaystoReduce
FROM Bookings
WHERE DATEPART(Year,ArrivalDate) = '2010' and CancelledDate BETWEEN ArrivalDate AND DepartureDate
你能帮忙吗?
感谢
韦恩
答
如何像下面这样?它不是很漂亮所以会留下,作为一个练习吧,但基本上它沿着计算每种类型的数量,然后将规则应用于对行号的行...
declare @shareCancelled float, @shareSameDay float, @sharePrevDay float, @shareSevenDays float
select @shareCancelled = 0.08, @shareSameDay = 0.20, @sharePrevDay = 0.20, @shareSevenDays = 0.20
declare @count int, @cancelled int, @sameDay int, @prevDay int, @sevenDays int
select @count = COUNT(*) from Bookings WHERE DATEPART(Year,ArrivalDate) = '2010' and CancelledDate BETWEEN ArrivalDate AND DepartureDate
select @cancelled = @count * @shareCancelled,
@sameDay = @count * @shareCancelled * @shareSameDay,
@prevDay = @count * @shareCancelled * @sharePrevDay,
@sevenDays = @count * @shareCancelled * @shareSevenDays
select ArrivalDate,
DATEADD(day,
CASE WHEN a.RowNum <= @sameDay THEN 0
WHEN a.RowNum <= @sameDay + @prevDay THEN -1
WHEN a.RowNum <= @sameDay + @prevDay + @sevenDays THEN -7
WHEN a.RowNum <= @cancelled THEN -(ABS(CAST(NEWID() AS binary(6)) %90) + 1)
ELSE NULL END
, GETDATE()) as DaystoReduce
from (
select *, ROW_NUMBER() OVER(ORDER BY NEWID()) as RowNum from Bookings WHERE DATEPART(Year,ArrivalDate) = '2010' and CancelledDate BETWEEN ArrivalDate AND DepartureDate
) as a
这是一个单关闭活动支持测试或类似? – chrisb 2013-02-21 16:47:42
嗨克里斯,是的。 – wafw1971 2013-02-21 16:51:19