SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Case Statement T-SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/12/2013 :  11:17:55  Show Profile  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 02/12/2013 :  11:21:55  Show Profile  Reply with Quote
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 - 02/12/2013 :  11:24:34  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/12/2013 :  11:25:10  Show Profile  Reply with Quote
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 - 02/12/2013 :  11:26:47  Show Profile  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 02/12/2013 :  11:37:37  Show Profile  Reply with Quote
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 - 02/12/2013 :  11:47:24  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/12/2013 :  12:14:04  Show Profile  Reply with Quote
how do you get those date values?

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

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 02/12/2013 :  12:18:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/12/2013 :  12:27:00  Show Profile  Reply with Quote
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 - 02/12/2013 :  12:35:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 02/12/2013 :  12:57:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 02/12/2013 :  15:16:19  Show Profile  Reply with Quote
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 - 02/13/2013 :  03:52:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/13/2013 :  12:24:27  Show Profile  Reply with Quote
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.

Edited by - Lamprey on 02/13/2013 15:03:10
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000