Days since last Wednesday

colinr
colinr Member
in Magic ETL

Hello.  I'd like to make a calculated fiield to show datediff('end date','last thursday').  how do I represent 'last thursday' in that?  The 'end date' is a sate in my data.

Best Answer

  • colinr
    colinr Member
    Answer ✓

    Ended up using this to be the 'last Thursday' part.  Not sure if ther is a more elegant way for it but it does the job.

    case
    when DAYOFWEEK(CURRENT_DATE())=1 then CURRENT_DATE()-3
    when DAYOFWEEK(CURRENT_DATE())=2 then CURRENT_DATE()-4
    when DAYOFWEEK(CURRENT_DATE())=3 then CURRENT_DATE()-5
    when DAYOFWEEK(CURRENT_DATE())=4 then CURRENT_DATE()-6
    when DAYOFWEEK(CURRENT_DATE())=5 then CURRENT_DATE()-1
    when DAYOFWEEK(CURRENT_DATE())=6 then CURRENT_DATE()-2
    when DAYOFWEEK(CURRENT_DATE())=7 then CURRENT_DATE()-3
    end

Answers

  • colinr
    colinr Member
    Answer ✓

    Ended up using this to be the 'last Thursday' part.  Not sure if ther is a more elegant way for it but it does the job.

    case
    when DAYOFWEEK(CURRENT_DATE())=1 then CURRENT_DATE()-3
    when DAYOFWEEK(CURRENT_DATE())=2 then CURRENT_DATE()-4
    when DAYOFWEEK(CURRENT_DATE())=3 then CURRENT_DATE()-5
    when DAYOFWEEK(CURRENT_DATE())=4 then CURRENT_DATE()-6
    when DAYOFWEEK(CURRENT_DATE())=5 then CURRENT_DATE()-1
    when DAYOFWEEK(CURRENT_DATE())=6 then CURRENT_DATE()-2
    when DAYOFWEEK(CURRENT_DATE())=7 then CURRENT_DATE()-3
    end

Categories