0
votes

Let's add some context: So, I was asked to calculate days aged of emails sent to a particular email address. The source I'm using is SAP HANA and made a Live connection to it in Tableau. The field I have to work with is called Created At. This field holds the data in yyyymmddhhmmss string format.

The challenge: Convert the original date in Created At field to a valid Working day and time (Monday-Friday and between 7:00am to 5:00pm). This is what I came up with:
CASE DATENAME('weekday',DATETIME([Created At]))
WHEN '7' THEN //item received on Saturday
DATEADD('day',2,DATEADD('hour',7,DATETRUNC('day',DATETIME([Created At]))))
    //first, I cut off the original time from Created At with DATETRUNC.
    //Next, I added 7 hours to get Saturday 7:00 am
    //finally, I add 2 days to change the date from a Saturday to a Monday

WHEN '1' THEN //item received on Sunday
DATEADD('day',2,DATEADD('hour',7,DATETRUNC('day',DATETIME([Created At]))))
    //first, I cut off the original time from Created At with DATETRUNC.
    //Next, I added 7 hours to get Sunday 7:00 am
    //finally, I add 1 day to change the date from a Sunday to a Monday

WHEN '6' THEN //item received on Friday
    //item received before 7:00am (7hrs)
    IF DATETIME([Created At]) < DATEADD('hour',7,DATETRUNC('day',DATETIME([Created At]))) THEN
        //first I cut off the original time from Created At with DATETRUNC.
        //Next, I added 7 hours to date to get the current date at 7:00 am
        DATEADD('hour',7,DATETRUNC('day',DATETIME([Created At])))

    //item received after 4:00pm (16hrs) 
    ELSEIF DATETIME([Created At]) > DATEADD('hour',16,DATETRUNC('day',DATETIME([Created At]))) THEN
        DATEADD('day',3,DATEADD('hour',7,DATETRUNC('day',DATETIME([Created At]))))
        //first I cut off the original time from Created At with DATETRUNC.
        //Next, I added 7 hours to date to get the current date at 7:00 am
        //finally, I add 3 days to change from Friday to Monday at 7:00 am

    //item received between 7:00am and 4:00pm (7-16hrs)
    ELSE DATETIME([Created At]) END

ELSE //received any other day of the week
    //item received before 7:00am (7hrs)
    IF DATETIME([Created At]) < DATEADD('hour',7,DATETRUNC('day',DATETIME([Created At]))) THEN
        //first I cut off the original time from Created At with DATETRUNC.
        //Next, I added 7 hours to date to get the current date at 7:00 am
        DATEADD('hour',7,DATETRUNC('day',DATETIME([Created At])))

    //item received after 4:00pm (16hrs) 
    ELSEIF DATETIME([Created At]) > DATEADD('hour',16,DATETRUNC('day',DATETIME([Created At]))) THEN
        DATEADD('day',1,DATEADD('hour',7,DATETRUNC('day',DATETIME([Created At]))))
        //first I cut off the original time from Created At with DATETRUNC.
        //Next, I added 7 hours to date to get the current date at 7:00 am
        //finally, I add 1 day to get next date forward at 7:00 am

    //item received between 7:00am and 4:00pm (7-16hrs)
    ELSE DATETIME([Created At]) END
END

Let me know if you have a better way to do this or let me know if you found it useful. Hope this helps!

this seems to be an answer in a question. You can always answer your own question - Penguin
Tableau has fairly sophisticated data calculation functions, it seems like overkill to attempt to do all the conversions in SQL. Just convert the string in Tableau and use the built-in calculations. - matt_black
What's the question? - Andy TAR Sols
It's more of a comment rather than a question. It's just that I could not get the results I wanted using built-in calculations. Of course, I'm open to suggestions. Please post them here. - Jorge Bustos Torres