Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script To Specify The Date Field To Use For "Activity Date" Expand / Collapse
Author
Message
Posted Monday, June 2, 2014 3:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 4:42 AM
Points: 8, Visits: 73
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
Post #1576502
Posted Monday, June 2, 2014 5:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:20 PM
Points: 13,252, Visits: 10,133
Is it possible to give some sample input and desired output?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1576518
Posted Monday, June 2, 2014 5:24 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 12,877, Visits: 31,792
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1576523
Posted Monday, June 2, 2014 7:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 4:42 AM
Points: 8, Visits: 73
Hi,

Thanks for the replies.

The solution works perfectly, thank you so much


Matt
Post #1576566
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse