wafw1971 Posted February 13, 2013 Share Posted February 13, 2013 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted February 13, 2013 Share Posted February 13, 2013 You can either loop using a variable to keep track of the year if you're able to use that variable in the case statements, or else you can copy and paste the code and change the year. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now