Post reply

Derived Column

  • 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

  • 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 ...

  • June 28, 2011 at 6:28 am

    #1345120

    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.

  • June 28, 2011 at 1:12 pm

    #1345439

    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

  • June 28, 2011 at 1:45 pm

    #1345466

    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]

  • June 28, 2011 at 1:53 pm

    #1345478

    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

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

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