Jump to content
Sign in to follow this  
wafw1971

Repeating Case Statement

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...