| Author |
 Topic  |
|
|
wafw1971
Yak Posting Veteran
68 Posts |
 Posted - 02/12/2013 : 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
United Kingdom
3560 Posts |
Posted - 02/12/2013 : 11:21:55
|
Take out the @StartDate and @EndDate from your case as you are doing nothing with these and it will work.
|
 |
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 02/12/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/12/2013 : 11:25:10
|
whats the purpose of those subqueries?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 02/12/2013 : 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 |
|
|
|
RickD
Slow But Sure Yak Herding Master
United Kingdom
3560 Posts |
Posted - 02/12/2013 : 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... |
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 02/12/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/12/2013 : 12:14:04
|
how do you get those date values?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1481 Posts |
Posted - 02/12/2013 : 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? |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 02/12/2013 : 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 |
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 02/12/2013 : 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 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1481 Posts |
Posted - 02/12/2013 : 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 |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 02/12/2013 : 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 |
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 02/13/2013 : 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 |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 02/13/2013 : 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. |
Edited by - Lamprey on 02/13/2013 15:03:10 |
|
|
| |
Topic |
|
Case Statement T-SQL
