Matt J (6/2/2014)
Hi,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?
Cheers
Matt
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
ELSE [Activity_Date]
END
Lowell