SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Adding a GreaterThan Statement		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wafw1971
Yak Posting Veteran

68 Posts
Posted - 02/21/2013 :  05:35:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1430 Posts
Posted - 02/21/2013 :  08:31:12  Show Profile  Reply with Quote
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
Go to Top of Page

wafw1971
Yak Posting Veteran

68 Posts
Posted - 02/21/2013 :  09:19:27  Show Profile  Reply with Quote
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
Go to Top of Page

wafw1971
Yak Posting Veteran

68 Posts
Posted - 02/21/2013 :  09:48:26  Show Profile  Reply with Quote
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)
Edited by - wafw1971 on 02/21/2013 09:49:19
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000