Derived Column

Question

Post reply

Derived Column

nigelc

Old Hand

Points: 380
More actions
June 27, 2011 at 10:04 pm

#145577

Hi

I am trying to create a derived column using the following case statement, but it is not working for me

case DATEPART( month, GETDATE() ) when 12 then DATEPART( year, GETDATE() )-1 else DATEPART( yyyy, GETDATE() ) end

I originally did this as a select statement and it produced what i wanted i changed it to expression but i don't seem to have it right

Is any one able to offer any ideas what is wrong with my statement

Cheers

Nigel

Viewing 6 posts - 1 through 5 (of 5 total)

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

rockybhai SSCommitted Points: 1574 More actions · Answer 1

rockybhai

SSCommitted

Points: 1574

June 28, 2011 at 4:23 am

#1345051

Can you please be more specific on the logic that you want to implement with this case statement ...

rgtft Hall of Fame Points: 3518 More actions · Answer 2

nigelc (6/27/2011)case DATEPART( month, GETDATE() ) when 12 then DATEPART( year, GETDATE() )-1 else DATEPART( yyyy, GETDATE() ) end

Are you trying to get the current month and year, except when it's December then you get last year?

If your data source is (e.g. OLE DB Source) from a SQL database, you can keep your current T-SQL logic in your SELECT and add the column to the data flow that way.

nigelc Old Hand Points: 380 More actions · Answer 3

Hi

SSC-Enthusiastic you are correct in interpreting what i am trying to do I plan to run the package on the first day of the month.

the problem i have with with running the T-sql over the output is that this load will be used to insert into the same table each month, so i couldn't do an update to the whole table

Cheers

Nigel

LutzM SSC Guru Points: 107049 More actions · Answer 4

What values do you expect to receive when running a query against that column, let's say now and 6 month from now?

Your computed column definition is based on getdate() so I expect you're looking for a the result of that calculation at runtime. Why not add this query to the related SELECT statement? This statement seems to be completely independent of the data stored in the table.

I might also have completely understood it wrong... If you're trying to apply this calculation to the date the row was inserted (or updated), you'd need to reference a column of the table.

Example:

case DATEPART( month, myTable.DateInserted ) when 12 then DATEPART( year, myTable.DateInserted )-1 else DATEPART( yyyy, myTable.DateInserted ) end

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

nigelc Old Hand Points: 380 More actions · Answer 5

Thats a good solution. IE adding the date part as part of the select statement

This extract would be run on the first day of the month for a datawarehouse type load so i just want to indicate which year/Month that it relates to which is why i have used the getdate function. So in 6 months time when i run this i want it to reflect the year and Month-1 at that time

Thanks for your help

Nigel