Matt J (6/2/2014)
I'm writing a view to check record counts in a table that has numerous datasets and therefore various "Activity Dates". Is it possible as part of the SQL statement to have a CASE statement for example so that it can identify the field to use as the activity date?
The field to use is being identified using a seperate table so at the moment I have CASE WHEN FieldToUse = '2' THEN MapCol ELSE '[Activity_Date]' END, where FieldToUse = '2' identifies the date field to use and the MapCol data is the field name to be used as the activity date.
Is this even possible?
you would have to switch to dynamic SQL.
Only with dynamic SQL (whiere you build a string and execute it) can you convert a value in a table (the [MapCol] column) to be the metadata name of a column name.
It would be much better to change the logic of your query to something like this:
CASE WHEN FieldToUse = '2' AND MapCol = 'CreatedDate' THEN CreatedDate
WHEN FieldToUse = '2' AND MapCol = 'UpdatedDate' THEN UpdatedDate
WHEN FieldToUse = '2' AND MapCol = 'PostedDate' THEN PostedDate
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!