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 INTDECLARE @Site_Skey INTDECLARE @Capacity INTDECLARE @StartDate DATEDECLARE @EndDate DATESET @PitchType_Skey = 1SET @Site_Skey = 1SET @Capacity = 0WHILE (@Site_Skey < 127)BEGIN IF @PitchType_Skey = 8 BEGINSET @PitchType_Skey = 1SET @Site_Skey = @Site_Skey + 1END IF (@Site_Skey < 127)BEGINSet @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_SkeyEND SET @PitchType_Skey = @PitchType_Skey + 1END |
|
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. |
|
|
wafw1971
Yak Posting Veteran
75 Posts |
Posted - 2013-02-12 : 11:24:34
|
Hi RickIm sorry I am new to SQL if I remove the @StartDate and @ EndDate how does the code know where to put the results?ThanksWayne |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-12 : 11:25:10
|
whats the purpose of those subqueries?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
wafw1971
Yak Posting Veteran
75 Posts |
Posted - 2013-02-12 : 11:26:47
|
Hi VisakhI 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
3608 Posts |
Posted - 2013-02-12 : 11:37:37
|
quote: Originally posted by wafw1971 Hi RickIm sorry I am new to SQL if I remove the @StartDate and @ EndDate how does the code know where to put the results?ThanksWayne
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...)SELECTCASE WHEN... |
|
|
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 INTDECLARE @Site_Skey INTDECLARE @Capacity INTDECLARE @StartDate DATEDECLARE @EndDate DATESET @PitchType_Skey = 1SET @Site_Skey = 1SET @Capacity = 0WHILE (@Site_Skey < 127)BEGIN IF @PitchType_Skey = 8 BEGINSET @PitchType_Skey = 1SET @Site_Skey = @Site_Skey + 1END IF (@Site_Skey < 127)BEGINSet @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
52326 Posts |
Posted - 2013-02-12 : 12:14:04
|
how do you get those date values?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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? |
|
|
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 |
|
|
wafw1971
Yak Posting Veteran
75 Posts |
Posted - 2013-02-12 : 12:35:19
|
Hi LampreyIt didn't work i'm afraid, the error message was about the < I then removed them and had another error message.ThanksWayne |
|
|
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 |
|
|
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 worksSELECT 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 ENDFROM SiteJimEveryday I learn something that somebody else already knew |
|
|
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 INTDECLARE @Site_Skey INTDECLARE @Capacity INTDECLARE @StartDate DATEDECLARE @EndDate DATE SET @PitchType_Skey = 1SET @Site_Skey = 1SET @Capacity = 0 WHILE (@Site_Skey < 127) BEGIN IF @PitchType_Skey = 8 BEGIN SET @PitchType_Skey = 1SET @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
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 INTDECLARE @Site_Skey INTDECLARE @Capacity INTDECLARE @SiteWeighting INTDECLARE @StartDate DATEDECLARE @EndDate DATESET @PitchType_Skey = 1SET @Site_Skey = 1SET @Capacity = 0WHILE (@Site_Skey < 127)BEGINIF @PitchType_Skey = 8BEGINSET @PitchType_Skey = 1SET @Site_Skey = @Site_Skey + 1ENDIF (@Site_Skey < 127)BEGINSet @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 CapacitySELECTCASEWHEN @SiteWeighting = 1 THEN '1 May 2010'WHEN @SiteWeighting IN (2, 3) THEN '1 Apr 2010'WHEN @SiteWeighting = 4 THEN '1 Jan 2010'ELSE NULLEND as StartDate,CASEWHEN @SiteWeighting = 1 THEN '30 Sep 2010'WHEN @SiteWeighting IN (2, 3) THEN '1 Nov 2010'WHEN @SiteWeighting = 4 THEN '1 Dec 2010'ELSE NULLEND,round(@Capacity,0) as Capacity,@PitchType_Skey, @Site_SkeyENDSET @PitchType_Skey = @PitchType_Skey + 1END 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. |
|
|
|