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

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Case Statement T-SQL

Author  Topic 

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-12 : 11:17:55
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)=1 THEN '01/05/2010'
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=2)=2 THEN '01/04/2010'
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=3)=3 THEN '01/04/2010'
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=4)=4 THEN '01/01/2010'
ELSE NULL

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

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

END
SET @PitchType_Skey = @PitchType_Skey + 1

END

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2013-02-12 : 11:21:55
Take out the @StartDate and @EndDate from your case as you are doing nothing with these and it will work.
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-12 : 11:24:34
Hi Rick

Im sorry I am new to SQL if I remove the @StartDate and @ EndDate how does the code know where to put the results?

Thanks

Wayne
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-12 : 11:25:10
whats the purpose of those subqueries?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-12 : 11:26:47
Hi Visakh

I have a Site Table that has a Site Weighting Column, each Site is weighted between 1 and 4, I am currently working on the Capacity table and want to use the weighting from the Site Table to determine the Start and End Date.

Thanks

Wayne
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2013-02-12 : 11:37:37
quote:
Originally posted by wafw1971

Hi Rick

Im sorry I am new to SQL if I remove the @StartDate and @ EndDate how does the code know where to put the results?

Thanks

Wayne


Currently they will go in the first two fields of the Capacity table. If you want them in particular fields other than these, you need to define the fields in the insert.

INSERT INTO Capacity (field1,field2,field3...)
SELECT
CASE WHEN...
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-12 : 11:47:24
Thanks for that Rick but it didn't populate my Table:

I am at a loss.

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

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

round(@Capacity,0) as Capacity,


END
SET @PitchType_Skey = @PitchType_Skey + 1

END
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-12 : 12:14:04
how do you get those date values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-12 : 12:18:24
Is this the actual code you are using? It has many syntax errors, so it cannot be if you were able to run it successfully even if it didn't insert any rows. Can you post the exact code that you ran?
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-12 : 12:27:00
Your insert statement has a bunch of issues, I tried to clean it up a bit, maybe it'll help:
INSERT 
Capacity
(
StartDate,
EndDate,
Capacity,
<ColumnName_PitchType_Skey>,
<ColumnName_Site_Skey>
)
SELECT
CASE
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=1)=1 THEN '01/05/2010'
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=2)=2 THEN '01/04/2010'
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=3)=3 THEN '01/04/2010'
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=4)=4 THEN '01/01/2010'
ELSE NULL
END,
CASE
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=1)=1 THEN '30/09/2010'
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=2)=2 THEN '01/11/2010'
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=3)=3 THEN '01/11/2010'
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=4)=4 THEN '01/12/2010'
ELSE NULL
END,
round(@Capacity,0) as Capacity,
@PitchType_Skey,
@Site_Skey
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-12 : 12:35:19
Hi Lamprey

It didn't work i'm afraid, the error message was about the < I then removed them and had another error message.

Thanks

Wayne
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-12 : 12:57:49
Lamprey meant for you to replace the two columns with whatever the actual column names in your Capacity table are. Also, the code you had in your query before the insert statement needs to be there:
INSERT 
Capacity
(
StartDate,
EndDate,
Capacity,
<ColumnName_PitchType_Skey>, -- replace these with your actual columns.
<ColumnName_Site_Skey>

)
SELECT
CASE
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=1)=1 THEN '01/05/2010'
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=2)=2 THEN '01/04/2010'
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=3)=3 THEN '01/04/2010'
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=4)=4 THEN '01/01/2010'
ELSE NULL
END,
CASE
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=1)=1 THEN '30/09/2010'
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=2)=2 THEN '01/11/2010'
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=3)=3 THEN '01/11/2010'
WHEN (SELECT SiteWeighting FROM Site WHERE SiteWeighting=4)=4 THEN '01/12/2010'
ELSE NULL
END,
round(@Capacity,0) as Capacity,
@PitchType_Skey,
@Site_Skey
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-02-12 : 15:16:19
I'll go for the easy answer first, see if that works

SELECT CASE WHEN SiteWeighting = 1 THEN '01/05/2010'
WHEN SiteWeighting = in (2,3) THEN '01/04/2010'
WHEN SiteWeighting = 4 THEN '01/01/2010'
ELSE NULL
END
FROM Site

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-13 : 03:52:21
Thanks everyone, I have sorted it now with your help (See below for final code), however I now need to populate the table with 4 more years worth of dates Each Site has a different start date for 2011,2012,2013,2014 and 2015. So based on my current code would you know how I would do this.

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
WHEN (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 NULL
END as StartDate,

CASE
WHEN (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 NULL
END,

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

END
SET @PitchType_Skey = @PitchType_Skey + 1

END
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-13 : 12:24:27
NOt usre if it helps, but if you store the Site Weighting in a variable you can avoid some calls to the database. For example:
DECLARE @PitchType_Skey INT
DECLARE @Site_Skey INT
DECLARE @Capacity INT
DECLARE @SiteWeighting 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 @SiteWeighting = (SELECT SiteWeighting From Site Where Site_Skey = @Site_Skey)
Set @Capacity = @SiteWeighting * (SELECT PitchTypeWeighting From PitchType Where PitchType_Skey = @PitchType_Skey)
Set @Capacity = @Capacity * ((10*(100+((RAND()*40)-20)))*0.01)

INSERT INTO Capacity
SELECT
CASE
WHEN @SiteWeighting = 1 THEN '1 May 2010'
WHEN @SiteWeighting IN (2, 3) THEN '1 Apr 2010'
WHEN @SiteWeighting = 4 THEN '1 Jan 2010'

ELSE NULL
END as StartDate,

CASE
WHEN @SiteWeighting = 1 THEN '30 Sep 2010'
WHEN @SiteWeighting IN (2, 3) THEN '1 Nov 2010'
WHEN @SiteWeighting = 4 THEN '1 Dec 2010'

ELSE NULL
END,

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

END
SET @PitchType_Skey = @PitchType_Skey + 1

END
EDIT: I forgot to mention if you are storing dates you should do so as date datatype not strings. If you are storing the date values as in a date datatype, you should format the date string as an unambigious format (ISO8601) and (i'd suggest) explicity casting the string to a date datatype.
Go to Top of Page
   

- Advertisement -