June 2, 2014 at 3:28 am
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
June 2, 2014 at 5:06 am
Is it possible to give some sample input and desired output?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 2, 2014 at 5:24 am
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
June 2, 2014 at 7:03 am
Hi,
Thanks for the replies.
The solution works perfectly, thank you so much 😀
Matt
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply