CODEWITHSUNDEEP
sql-servert-sqlcase
wafw1971
wafw1971

Reputation: 361

Case Statement T-SQL

Everything was working fine with the script below until I tried to add a Case Statement with a SELECT Clause I had done this to auto populate the table with the correct dates can some let me know what have I done wrong?

DECLARE @PitchType_Skey INT
DECLARE @Site_Skey INT
DECLARE @Capacity INT
DECLARE @StartDate DATE
DECLARE @EndDate DATE

SET @PitchType_Skey = 1
SET @Site_Skey = 1
SET @Capacity = 0

WHILE (@Site_Skey < 127)

BEGIN
    IF @PitchType_Skey = 8

BEGIN

SET @PitchType_Skey = 1
SET @Site_Skey = @Site_Skey + 1

END

IF (@Site_Skey < 127)

BEGIN

Set @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 Capacity 
     SELECT
     CASE @StartDate
     WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting = 1 THEN 01/05/2010) ELSE,
     WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting = 2 THEN 01/04/2010) ELSE,
     WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting = 3 THEN 01/04/2010) ELSE,
     WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting = 4 THEN 01/01/2010) ELSE,

     CASE @EndDate
     WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting = 1 THEN 30/09/2010) ELSE,
     WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting = 2 THEN 01/11/2010) ELSE,
     WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting = 3 THEN 01/11/2010) ELSE,
     WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting = 4 THEN 01/12/2010) ELSE,

      round(@Capacity,0) as Capacity,
      @PitchType_Skey, @Site_Skey

END
  SET @PitchType_Skey = @PitchType_Skey + 1

END

Upvotes: 0

Views: 2751

Answers (1)

user1919238
user1919238

Reputation:

Well, you have your parentheses in the wrong place. THEN is part of the case statement, but you have it inside the subquery. You also only want one ELSE per case statement.

It's hard to know if there are more mistakes without knowing more about your table, but a syntactically correct case statement would look something like this:

CASE @StartDate
  WHEN (SELECT SiteWeighting FROM Site WHERE Site_SKey=@Site_SKey)=1 THEN '01/05/2010'
  WHEN (SELECT SiteWeighting FROM Site WHERE Site_SKey=@Site_SKey)=2 THEN '01/04/2010'
  ELSE NULL 
END

However, I strongly suggest that you don't use a case statement for this. Instead, use something else, like a lookup table with start and end dates that you can join based on SiteWeighting. It will make your life a lot easier.

Upvotes: 2

Related Questions