February 13, 2013 at 4:50 am
Hi Gazareth
Thanks for your help, I should have known to change the start date to the date I wanted, I have done this now
CASE
WHEN YEAR(@StartDate) = 2010 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 1 THEN '01 May 2010'
WHEN YEAR(@StartDate) = 2011 AND (SELECT SiteWeighting FROM Site WHERE Site_Skey=@Site_Skey) = 1 THEN '01 May 2011'
However I am now getting a another error message: Msg 206, Level 16, State 2, Line 32
Operand type clash: int is incompatible with date
DO you think this it is something to do with 2010, 2011 in the WHEN statement?
Thanks
Wayne
February 13, 2013 at 5:27 am
Hi,
Don't think it's that - things to check are all dates surrounded by single quotes ' (best practice would be to use ISO format - 'yyyy-mm-dd') and the column datatypes of Capacity2 & the column order you're inserting.
Cheers
Gaz
February 13, 2013 at 7:37 am
You really don't need all those subselects at all. How about something like this?
SELECT
case Site_Key
when 1 then
CASE YEAR(@StartDate)
WHEN 2010 THEN '1 May 2010'
WHEN 2011 THEN 'StartDate 2011'
WHEN 2012 THEN 'StartDate 2012'
WHEN 2013 THEN 'StartDate 2013'
WHEN 2014 THEN 'StartDate 2014'
WHEN 2015 THEN 'StartDate 2015'
END
WHEN 2 then
CASE YEAR(@StartDate)
WHEN 2010 THEN '30 Sep 2010'
WHEN 2011 THEN 'StartDate 2011'
WHEN 2012 THEN 'StartDate 2012'
WHEN 2013 THEN 'StartDate 2013'
WHEN 2014 THEN 'StartDate 2014'
WHEN 2015 THEN 'StartDate 2015'
END
WHEN 3 then
CASE YEAR(@StartDate)
WHEN 2010 THEN '1 Nov 2010'
WHEN 2011 THEN 'StartDate 2011'
WHEN 2012 THEN 'StartDate 2012'
WHEN 2013 THEN 'StartDate 2013'
WHEN 2014 THEN 'StartDate 2014'
WHEN 2015 THEN 'StartDate 2015'
END
WHEN 4 then
CASE YEAR(@StartDate)
WHEN 2010 THEN '1 Dec 2010'
WHEN 2011 THEN 'StartDate 2011'
WHEN 2012 THEN 'StartDate 2012'
WHEN 2013 THEN 'StartDate 2013'
WHEN 2014 THEN 'StartDate 2014'
WHEN 2015 THEN 'StartDate 2015'
END
ELSE NULL
END as StartDate,
case Site_Key
when 1 then
CASE YEAR(@StartDate)
WHEN 2010 THEN '1 May 2010'
WHEN 2011 THEN 'StartDate 2011'
WHEN 2012 THEN 'StartDate 2012'
WHEN 2013 THEN 'StartDate 2013'
WHEN 2014 THEN 'StartDate 2014'
WHEN 2015 THEN 'StartDate 2015'
END
WHEN 2 then
CASE YEAR(@StartDate)
WHEN 2010 THEN '30 Sep 2010'
WHEN 2011 THEN 'StartDate 2011'
WHEN 2012 THEN 'StartDate 2012'
WHEN 2013 THEN 'StartDate 2013'
WHEN 2014 THEN 'StartDate 2014'
WHEN 2015 THEN 'StartDate 2015'
END
WHEN 3 then
CASE YEAR(@StartDate)
WHEN 2010 THEN '1 Nov 2010'
WHEN 2011 THEN 'StartDate 2011'
WHEN 2012 THEN 'StartDate 2012'
WHEN 2013 THEN 'StartDate 2013'
WHEN 2014 THEN 'StartDate 2014'
WHEN 2015 THEN 'StartDate 2015'
END
WHEN 4 then
CASE YEAR(@StartDate)
WHEN 2010 THEN '1 Dec 2010'
WHEN 2011 THEN 'StartDate 2011'
WHEN 2012 THEN 'StartDate 2012'
WHEN 2013 THEN 'StartDate 2013'
WHEN 2014 THEN 'StartDate 2014'
WHEN 2015 THEN 'StartDate 2015'
END
ELSE NULL
END,
ROUND(@Capacity,0) as Capacity,
@PitchType_Skey, @Site_Skey
FROM Site
WHERE Site_Skey=@Site_Skey
Now if we really want to simplify this and make it faster lets get rid of the loop.
_______________________________________________________________
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 13, 2013 at 7:40 am
wafw1971 (2/12/2013)
Site TableSite_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.
Please please please do yourself a favor and take 5 minutes to read the article in my signature about best practices. Ask yourself if you could write a query against what you just posted. Remember we are all volunteers around here and taking time to turn this into a consumable format (create tables with insert statements) takes away from the time we have to spend working on your issue.
_______________________________________________________________
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 13, 2013 at 8:16 am
Gazareth (2/13/2013)
Hi,Don't think it's that - things to check are all dates surrounded by single quotes ' (best practice would be to use ISO format - 'yyyy-mm-dd') and the column datatypes of Capacity2 & the column order you're inserting.
Cheers
Gaz
Better would be to use 'yyyymmdd' instead of 'yyyy-mm-dd' as the later can still be affected by the setting of dateformat.
February 13, 2013 at 8:53 am
Lynn Pettis (2/13/2013)
Gazareth (2/13/2013)
Hi,Don't think it's that - things to check are all dates surrounded by single quotes ' (best practice would be to use ISO format - 'yyyy-mm-dd') and the column datatypes of Capacity2 & the column order you're inserting.
Cheers
Gaz
Better would be to use 'yyyymmdd' instead of 'yyyy-mm-dd' as the later can still be affected by the setting of dateformat.
Fair point - I was still thinking about @StartDate & using the date datatype, which these aren't necessarily of course 🙂
February 13, 2013 at 8:59 am
Thanks everyone all sorted now.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply