J'ai 4 millions de lignes dans ma table avec une colonne vide appelée Réservations annulées divisé en 3 ans 2010, 2011 et 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
Ce que je dois faire est de créer un code où je peux changer le% d'annulations pour l'année que je veux mettre à jour:
Donc, pour 2010, j'ai besoin de ce qui suit
– Réservations annulées–
.
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
Pouvez-vous aider?
Merci
Wayne
Que diriez-vous de quelque chose comme ce qui suit? Ce n'est pas très joli donc cela laissera cela comme un exercice pour vous, mais fondamentalement, c'est le long des lignes de calcul du nombre de chaque type et ensuite appliquer les règles contre le numéro de ligne …
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