Script To Specify The Date Field To Use For "Activity Date"

Question

Script To Specify The Date Field To Use For "Activity Date"

Matt J

SSC Journeyman

Points: 90
More actions
June 2, 2014 at 3:28 am

#292007

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

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

Lowell SSC Guru Points: 323504 More actions · Answer 2

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

--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!

Matt J SSC Journeyman Points: 90 More actions · Answer 3

Hi,

Thanks for the replies.

The solution works perfectly, thank you so much 😀

Matt