magnify
Home Topic BETL

BETL

This topic contains 3 replies, has 2 voices, and was last updated by  Manny 2 years, 5 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #630

    Jamie Scott
    Keymaster

    BETL Topics go here

    #823

    Manny
    Participant

    Use of the DateAdd function to calculate End of Month date.

    The DateAdd function can be used to calculate the last day in a month based on the first day in that month.

    See attached ETL for an example and the below document which comes from the LinkedIn discussion available here:
    http://www.linkedin.com/groupItem?view=&gid=5169388&type=member&item=5923682114997346306&commentID=5924731538158673920&goback=%2Egmp_5169388&report%2Esuccess=8ULbKyXO6NDvmoK7o030UNOYGZKrvdhBhypZ_w8EpQrrQI-BBjkmxwkEOwBjLE28YyDIxcyEO7_TA_giuRN#commentID_5924731538158673920

    If you have a column of dates called [MONTH_START_DATE] that has the first day of the month in it, then the formula:
    DateAdd([MONTH_START_DATE], -1, 1, 0)

    will return the correct End Date for that month BUT only for the months that do NOT have 31 days in them (except August and January), i.e. March, May, July, October and December will incorrectly calculate the date as either the 28th or 29th (for March) or the 30th for May, July, October and December.

    Why?

    Because the formula seems to work by processing the days first, then the months and then presumably the years and after that it seems to perform an internal calculation where it checks the validity of the date produced and keeps subtracting 1 day until it gets a valid date. See some examples below that may help explain this:
    DateAdd([MONTH_START_DATE], -1, 1, 0)

    e.g.1 MONTH_START_DATE = 1/4/2014) (Works)
    Step 1: Subtract 1 day = 31/3/2014
    Step 2: Add 1 month = 31/4/2014
    Step 3: Keep subtracting a day until you get a valid date = 30/4/2014

    e.g.2 MONTH_START_DATE = 1/8/2014) (Works)
    Step 1: Subtract 1 day = 31/7/2014
    Step 2: Add 1 month = 31/8/2014
    Step 3: No need to subtract any days as 31/8/2014 is valid

    e.g.3 MONTH_START_DATE = 1/5/2014) (DOES NOT WORK CORRECTLY)
    Step 1: Subtract 1 day = 30/4/2014
    Step 2: Add 1 month = 30/5/2014
    Step 3: No need to subtract any days as 30/5/2014 is valid BUT is not the last day in May

    How to resolve:

    The formula to calculate the last day of a month based on the first day of the month (this will work regardless of whether the year is a leap year) requires nesting two DateAdd functions, with the inner function adding the month first and the outer function removing one day.

    With nested formulas, the inner function evaluates first and then the outer function. This will obviously be slower than the formula above as it calls the DateAdd function twice, but it will yield the correct results:

    e.g. MONTH_START_DATE = 1/5/2014) (THIS WORKS CORRECTLY)
    DateAdd(DateAdd([MONTH_START_DATE], 0, 1, 0), -1, 0, 0)

    Explanation:
    Remember the DateAdd syntax is:
    DateAdd(Date From, no. of days, no. of months, no. of years).

    Note 1:
    A positive value in the no. of days, no. of months, no. of years parameters will go to the future and vice versa for a negative.

    Note 2: If you enter a non-integer in these parameters the number entered will be truncated (or rounded down) to the nearest integer. i.e. if you say add 1.6 years to 1/5/2014 you will get the answer 1/5/2015, the 0.6 is ignored)

    So in the above the inner function is:
    DateAdd([MONTH_START_DATE], 0, 1, 0) and using 1st May 2014 it translates to:
    DateAdd(ToDate(‘1/5/2014’), 0, 1, 0)
    which states calculate the date that is 1 month greater than 1st May 2014.
    The answer is: 1st June 2014.

    The outer function then works off this result so the outer function becomes:
    DateAdd(ToDate(‘1/6/2014’), -1, 0, 0)
    which says take 1 day away from the 1st June 2014 and the calculation correctly returns 31st May 2014.

    Hope that helps.

    Cheers,

    Manny

    #824

    Manny
    Participant

    Sorry upload was rejected due to security reasons, trying again as zip

    Attachments:
    You must be logged in to view attached files.
    #885

    Manny
    Participant

    ETL to look through ETL log files and record which process generated the log file

    If you run a lot of ETLs (we run over 3000 a month) then it can be hard to find the logfile you are looking for if the entry has been deleted from (All/User) DP job monitor . Also log files are generically named as “RunETLProcess{incrementing number}.log”, but without opening the file you can’t tell which ETL generated that log file.

    The attached ETL (and warehouse / warehouse table) picks up the ETL log files within the log directory (and optionally all subdirectories) for log files beginning with RunETLProcess. It then extracts pertinent information such as the filename, user who ran the ETL, the ETL process that generated the log and can then go and send this information to an Excel spreadsheet or warehouse table.

    A secondary ETL could then be built based on this data to move log files based on process name, or add the process name to the logfile name so that you can find them more easily in your log directory.

    If you decide to use it, I’ll be happy to take any feedback.

    Cheers,

    Manny

    Attachments:
    You must be logged in to view attached files.
Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.