February 12, 2013 at 9:33 am
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
February 12, 2013 at 9:44 am
Hi,
What are you trying to do with @StartDate and @EndDate?
You can't have CASE statements in that format
It's either
CASE value
WHEN match THEN
or
CASE
WHEN (clause) THEN
You can't mix them up.
Cheers
Gaz
February 12, 2013 at 9:45 am
You have really messed up the case expressions. There is no need to check case of a value and you left off the end.
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
end,
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
end,
The bigger issue as I see it is that this whole looping construct should be done with a single insert statement. There is absolutely no need to loop here at all.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 12, 2013 at 9:48 am
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.
I am new to SQL so I don't understand what you meant about the two different types of CASE statements.
February 12, 2013 at 10:05 am
Hi Sean
Thank you for that, but it still doesn't populate my table. Any ideas why not because I haven't got the foggiest?
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
END,
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
END,
round(@Capacity,0) as Capacity,
@PitchType_Skey, @Site_Skey
END
SET @PitchType_Skey = @PitchType_Skey + 1
END
February 12, 2013 at 10:07 am
wafw1971 (2/12/2013)
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.I am new to SQL so I don't understand what you meant about the two different types of CASE statements.
Your case expressions were trying to evaluate the value of @StartDate and @EndDate and then some sort of subselect. I am pretty sure your subselects are close to what you want either. They will always evaluate to the same value because if there is a row in Site where SiteWeighting = 1 it will ALWAYS evaluate to 1.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 12, 2013 at 10:11 am
wafw1971 (2/12/2013)
Hi SeanThank you for that, but it still doesn't populate my table. Any ideas why not because I haven't got the foggiest?
Not without some ddl and sample data. Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 12, 2013 at 10:21 am
This is the complete code:
CREATE TABLE Capacity
(StartDate DATE not null,
EndDate DATE not null,
Quantity INT not null,
PitchType_Skey int not null,
Site_Skey int not null)
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
END,
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
END,
round(@Capacity,0) as Capacity,
@PitchType_Skey, @Site_Skey
END
SET @PitchType_Skey = @PitchType_Skey + 1
END
I hope this will help you help me.
Thanks
Wayne
February 12, 2013 at 10:44 am
Is it (the second version with @StartDate and @EndDate commented out in the case statement) not populating the table at all, or just not with the values you expect?
(And, as Sean said, some DDL & sample data for the Site & PitchType tables would really help us find the problem :-))
Thanks
February 12, 2013 at 10:51 am
Site Table
Site_SkeySiteCodeSiteNameBDMRegionSiteWeighting
16000AdgestoneRoger GreenEast1
26001Aldridge HillRoger GreenEast4
36002Alton The StarRoger GreenEast4
46003ArdgartenRoger GreenEast1
56004AshbourneRoger GreenEast1
66005AshurstRoger GreenEast1
76006BakewellRoger GreenEast2
86007BalaRoger GreenEast1
96008Barnard CastleRoger GreenEast3
106009Beadnell BayRoger GreenEast2
Site Table
PitchType_SkeyPitchTypeCodePitchDescriptionPitchTypeWeighting
11Grass4
22Grass With Electric3
33Hardstanding2
44Hardstanding With Electric4
55Self Catering1
66Pods/Dens1
77Seasonal1
It was in table form but it looks like its formatted wrongly, I hope this is ok.
February 12, 2013 at 11:08 am
Thanks
Still trying to tell what the CASE statements are intended to do. Given that data,
(SELECT SiteWeighting FROM Site WHERE SiteWeighting=1)=1 will always be true, so you'll always get 01/05/2010 and 30/09/2010 from them.
February 13, 2013 at 3:02 am
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
February 13, 2013 at 3:41 am
wafw1971 (2/13/2013)
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.
Cool, good to hear it's working. Those subselects in the case statements make more sense now 🙂
Not sure if this is what you need, but you can expand the when parts to something like:
WHEN YEAR(@StartDate) = 2010 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 1 THEN '1 May 2010'
WHEN YEAR(@StartDate) = 2011 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 1 THEN 'StartDate 2011'
WHEN YEAR(@StartDate) = 2012 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 1 THEN 'StartDate 2012'
...
WHEN YEAR(@StartDate) = 2015 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 4 THEN 'StartDate 2015'
February 13, 2013 at 4:13 am
Hi Gazareth
I am getting the following error message, Msg 206, Level 16, State 2, Line 32
Operand type clash: int is incompatible with date. Also as you can see from the top of the script my Site_Skey goes upto 126, will this repeat for the other years?
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 Capacity2
SELECT
CASE
WHEN YEAR(@StartDate) = 2010 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 1 THEN '1 May 2010'
WHEN YEAR(@StartDate) = 2011 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 1 THEN 'StartDate 2011'
WHEN YEAR(@StartDate) = 2012 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 1 THEN 'StartDate 2012'
WHEN YEAR(@StartDate) = 2013 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 1 THEN 'StartDate 2013'
WHEN YEAR(@StartDate) = 2014 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 1 THEN 'StartDate 2014'
WHEN YEAR(@StartDate) = 2015 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 1 THEN 'StartDate 2015'
WHEN YEAR(@StartDate) = 2010 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 2 THEN '1 Apr 2010'
WHEN YEAR(@StartDate) = 2011 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 2 THEN 'StartDate 2011'
WHEN YEAR(@StartDate) = 2012 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 2 THEN 'StartDate 2012'
WHEN YEAR(@StartDate) = 2013 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 2 THEN 'StartDate 2013'
WHEN YEAR(@StartDate) = 2014 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 2 THEN 'StartDate 2014'
WHEN YEAR(@StartDate) = 2015 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 2 THEN 'StartDate 2015'
WHEN YEAR(@StartDate) = 2010 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 3 THEN '1 Apr 2010'
WHEN YEAR(@StartDate) = 2011 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 3 THEN 'StartDate 2011'
WHEN YEAR(@StartDate) = 2012 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 3 THEN 'StartDate 2012'
WHEN YEAR(@StartDate) = 2013 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 3 THEN 'StartDate 2013'
WHEN YEAR(@StartDate) = 2014 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 3 THEN 'StartDate 2014'
WHEN YEAR(@StartDate) = 2015 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 3 THEN 'StartDate 2015'
WHEN YEAR(@StartDate) = 2010 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 4 THEN '1 January 2010'
WHEN YEAR(@StartDate) = 2011 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 4 THEN 'StartDate 2011'
WHEN YEAR(@StartDate) = 2012 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 4 THEN 'StartDate 2012'
WHEN YEAR(@StartDate) = 2013 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 4 THEN 'StartDate 2013'
WHEN YEAR(@StartDate) = 2014 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 4 THEN 'StartDate 2014'
WHEN YEAR(@StartDate) = 2015 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 4 THEN 'StartDate 2015'
ELSE NULL
END as StartDate,
CASE
WHEN YEAR(@StartDate) = 2010 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 1 THEN '1 May 2010'
WHEN YEAR(@StartDate) = 2011 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 1 THEN 'StartDate 2011'
WHEN YEAR(@StartDate) = 2012 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 1 THEN 'StartDate 2012'
WHEN YEAR(@StartDate) = 2013 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 1 THEN 'StartDate 2013'
WHEN YEAR(@StartDate) = 2014 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 1 THEN 'StartDate 2014'
WHEN YEAR(@StartDate) = 2015 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 1 THEN 'StartDate 2015'
WHEN YEAR(@StartDate) = 2010 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 2 THEN '30 Sep 2010'
WHEN YEAR(@StartDate) = 2011 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 2 THEN 'StartDate 2011'
WHEN YEAR(@StartDate) = 2012 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 2 THEN 'StartDate 2012'
WHEN YEAR(@StartDate) = 2013 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 2 THEN 'StartDate 2013'
WHEN YEAR(@StartDate) = 2014 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 2 THEN 'StartDate 2014'
WHEN YEAR(@StartDate) = 2015 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 2 THEN 'StartDate 2015'
WHEN YEAR(@StartDate) = 2010 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 3 THEN '1 Nov 2010'
WHEN YEAR(@StartDate) = 2011 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 3 THEN 'StartDate 2011'
WHEN YEAR(@StartDate) = 2012 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 3 THEN 'StartDate 2012'
WHEN YEAR(@StartDate) = 2013 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 3 THEN 'StartDate 2013'
WHEN YEAR(@StartDate) = 2014 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 3 THEN 'StartDate 2014'
WHEN YEAR(@StartDate) = 2015 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 3 THEN 'StartDate 2015'
WHEN YEAR(@StartDate) = 2010 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 4 THEN '1 Dec 2010'
WHEN YEAR(@StartDate) = 2011 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 4 THEN 'StartDate 2011'
WHEN YEAR(@StartDate) = 2012 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 4 THEN 'StartDate 2012'
WHEN YEAR(@StartDate) = 2013 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 4 THEN 'StartDate 2013'
WHEN YEAR(@StartDate) = 2014 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 4 THEN 'StartDate 2014'
WHEN YEAR(@StartDate) = 2015 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 4 THEN 'StartDate 2015'
ELSE NULL
END,
ROUND(@Capacity,0) as Capacity,
@PitchType_Skey, @Site_Skey
END
SET @PitchType_Skey = @PitchType_Skey + 1
END
February 13, 2013 at 4:45 am
It's the 'StartDate 2011' bits - I intended that you'd put in the actual start/end dates for 2011, 2012 etc. Sorry if that wasn't clear.
Note I'm also assuming on your behalf that you're determining the year from the @StartDate parameter, no idea if that's a correct assumption or not.
Also @StartDate is never changed, so it'll always be one particular year for any one run of the code.
As far as I can tell from the current code, you'll be fine up to 127 as long as SiteWeighting values are only 1-4.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply