Adding a GreaterThan Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Adding a GreaterThan Statement

Author

Topic

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-21 : 05:35:45

I have 1.35 million lines of data, I now need to make 15% of them cancelled by inserting a random Cancelled Date. However the cancelled date must be =>Booking Date and <=Arrival Date.

I have completed the random section but I now need to know how to add greater than and less than part to the query:

Can you help?

SELECT ArrivalDate,
DATEADD(day,
CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0 and 0.85 THEN NULL ELSE
CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.85 and 0.88 THEN 0 ELSE
CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.88 and 0.92 THEN -1 ELSE
CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.92 and 0.97 THEN -7 ELSE
Round(Rand(CHECKSUM(NEWID())) * -90,0) END END END END,ArrivalDate) AS DaystoReduce
FROM Bookings

Thanks

Wayne

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-21 : 08:31:12

This is the code for cancelledDate in between Arrival and Departure Dates


 DECLARE @bookings TABLE(Booking_Skey INT, BookingNumber INT, ArrivalDate DATE, DepartureDate DATE,BookingDate DATE,CancelledDate DATE,BookingValue INT, PitchType_Skey INT,Site_Skey INT)
insert into @bookings
SELECT 1313258, NULL, '02/01/2010', '04/01/2010', NULL, NULL, NULL, 3, 2 union all
SELECT 1313259, NULL, '02/01/2010', '07/01/2010', NULL, NULL, NULL, 3, 2 union all
SELECT 1313260, NULL, '02/01/2010', '15/01/2010', NULL, NULL, NULL, 3, 2 union all
SELECT 1313261, NULL, '02/01/2010', '28/01/2010', NULL, NULL, NULL, 3, 2 union all
SELECT 1313262, NULL, '02/01/2010', '03/01/2010', NULL, NULL, NULL, 3, 2

UPDATE @bookings
SET CancelledDate = DATEADD ( DD, RAND(CHECKSUM(NEWID()))* DATEDIFF(DD, ArrivalDate, DepartureDate), ArrivalDate);
SELECT * FROM @bookings

--
Chandu

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-21 : 09:19:27

Hi Chandu

I don't understand why I need all that code, only 15% of 2010 bookings are to be cancelled

Does your code just cancel 15% in 2010?

UPDATE @bookings
SET CancelledDate = DATEADD ( DD, RAND(CHECKSUM(NEWID()))* DATEDIFF(DD, ArrivalDate, DepartureDate), ArrivalDate);
SELECT * FROM @bookings

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-21 : 09:48:26

All sorted now, thanks everyone.

SELECT Bookings.Booking_Skey, DATEDIFF(DAY, Bookings.ArrivalDate, Bookings.DepartureDate) * PitchValues.Value AS BookingValue, PitchValues.PitchType_Skey
FROM Bookings
INNER JOIN PitchValues ON Bookings.PitchType_Skey = PitchValues.PitchType_Skey
AND Bookings.ArrivalDate BETWEEN PitchValues.StartDate AND PitchValues.EndDate
WHERE (Bookings.Booking_Skey = 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources