Jump to content

wafw1971

Members
  • Posts

    3
  • Joined

  • Last visited

Everything posted by wafw1971

  1. wafw1971

    Date Randomise

    I have a Arrival date of 01/01/2012 the Departure date can be any date between 02/01/2012 and 29/01/2012 and needs to added to the database either via an Update/Insert Statement. Can you help with the code.SELECT ArrivalDate, DATEADD(day, RAND(checksum(NEWID()))* LengthOfStay.LengthofStay, ArrivalDate) AS DepartureDate FROM Bookings, LengthOfStayORDER BY ArrivalDateBut the Departure date cannot be the same as the arrival date.Thanks
  2. I have created a T-SQL code, this code has created a Capacity Table for the year 2010 (See Below), what I now need it to do is repeat this for the following years 2011,2012,2013,2014 and 2015 all in the same table but I cannot figure out how to do this. This code uses other tables to provide it the results. Thanks in Advance.DECLARE @PitchType_Skey INTDECLARE @Site_Skey INTDECLARE @Capacity INTDECLARE @StartDate DATEDECLARE @EndDate DATESET @PitchType_Skey = 1SET @Site_Skey = 1SET @Capacity = 0WHILE (@Site_Skey < 127)BEGINIF @PitchType_Skey = 8BEGINSET @PitchType_Skey = 1SET @Site_Skey = @Site_Skey + 1ENDIF (@Site_Skey < 127)BEGINSet @Capacity = (SELECT SiteWeighting From Site Where Site_Skey = @Site_Skey)Set @Capacity = @Capacity * (SELECT PitchTypeWeighting From PitchType Where PitchType_Skey = @PitchType_Skey)Set @Capacity = @Capacity * ((10*(100+((RAND()*40)-20)))*0.01)INSERT INTO CapacitySELECTCASEWHEN (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 1 THEN '1 May 2010'WHEN (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey)=2 THEN '1 Apr 2010'WHEN (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey)=3 THEN '1 Apr 2010'WHEN (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey)=4 THEN '1 Jan 2010'ELSE NULLEND as StartDate,CASEWHEN (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey)=1 THEN '30 Sep 2010'WHEN (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey)=2 THEN '1 Nov 2010'WHEN (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey)=3 THEN '1 Nov 2010'WHEN (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey)=4 THEN '1 Dec 2010'ELSE NULLEND,round(@Capacity,0) as Capacity,@PitchType_Skey, @Site_SkeyENDSET @PitchType_Skey = @PitchType_Skey + 1END
  3. Morning Everyone, I have just started training on SQL and was wondering if you could help with a problem I have encountered. I have populated a column with a date via the CREATE TABLE FunctionUSE OccupancyCREATE TABLE Time(Date date not null) DECLARE @StartDate Date, @EndDate Date SET @StartDate = '01 Jan 2010'SET @EndDate= '31 March 2015'WHILE @StartDate <= @EndDate BEGININSERT INTO TimeVALUES (@StartDate)SET @StartDate = Dateadd(dd,1,@StartDate)ENDBut what I would like to do now is create 4 Extra columns called CalendarYear, CalendarMonth, FinancialYear and finally FinancialMonth, I know I should use the Datepart function but not matter how many time I have written it, it doesn't seem to work.All help is much appreciated.ThanksWayne
×
×
  • Create New...