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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wafw1971
Yak Posting Veteran

75 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
2242 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

75 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

75 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  
 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.04 seconds. Powered By: Snitz Forums 2000