Record count for prior month or prior quarter at start of the year

Randyb
Randyb Member
in Magic ETL

I have a Beast Mode that I've been using that was working fine up until January 1st. The issue is that I have a number of Beast Modes that are calculating record counts for Prior Month, Prior Quarter and Prior 2 Quarters past.

They all seem to have trouble getting past the 2018 component, which I know I've coded in the Beast Mode because otherwise it was pulling quarter and month info for numerous years instead of the current year.

I assume that somehow I need to build in a case statment that checks to see what the year month is and based on that will alter the Beast Mode.

Here is a sample of what I'm using for Prior Month Beast Mode, the current Quarter -1 and Current Quarter -2 are very similar.

Let me know what I should do or if this is possible.

thanks

Randy

 

Sum(CASE WHEN `hireDate` <= DATE_ADD(CONCAT(MONTH(CURRENT_DATE()),'-1-',YEAR(CURRENT_DATE())), interval -1 day ) AND `dateOfTermination`IS NULL THEN 1 ELSE 0 END
+
CASE WHEN `hireDate` <= DATE_ADD(CONCAT(MONTH(CURRENT_DATE()),'-1-',YEAR(CURRENT_DATE())), interval -1 day ) AND `dateOfTermination` >= DATE_ADD(CONCAT(MONTH(CURRENT_DATE()),'-1-',YEAR(CURRENT_DATE())), interval -1 day )
Then 1 else 0
End)

Best Answer

  • RGranada
    RGranada Contributor
    Answer ✓

    Hi,

     

    Can you try this one out:

     

    Sum(CASE WHEN `hireDate` <= DATE_SUB(LAST_DAY(CURDATE()), interval 1 MONTH)  AND `dateOfTermination`IS NULL THEN 1 ELSE 0 END
    +
    CASE WHEN `hireDate` <= DATE_SUB(LAST_DAY(CURDATE()), interval 1 MONTH) AND `dateOfTermination` >= DATE_SUB(LAST_DAY(CURDATE()), interval 1 MONTH)
    THEN 1 ELSE 0 END)

     

    This code: DATE_SUB(LAST_DAY(CURDATE()), interval -1 MONTH)  will give you the last day of the previous month.

     

    Tell me how it goes.

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.

Answers

  • RGranada
    RGranada Contributor
    Answer ✓

    Hi,

     

    Can you try this one out:

     

    Sum(CASE WHEN `hireDate` <= DATE_SUB(LAST_DAY(CURDATE()), interval 1 MONTH)  AND `dateOfTermination`IS NULL THEN 1 ELSE 0 END
    +
    CASE WHEN `hireDate` <= DATE_SUB(LAST_DAY(CURDATE()), interval 1 MONTH) AND `dateOfTermination` >= DATE_SUB(LAST_DAY(CURDATE()), interval 1 MONTH)
    THEN 1 ELSE 0 END)

     

    This code: DATE_SUB(LAST_DAY(CURDATE()), interval -1 MONTH)  will give you the last day of the previous month.

     

    Tell me how it goes.

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • Randyb
    Randyb Member

    I tried it but it still didn't work, it produce the same values as the current month and current quarter which tells me its still not breaking out of 2018.

    That is the issue I'm having is that right now everything is returning 01 2018 as the month year and won't go back to 12 2017 for the prior month and won't go back 10 2017 etc for the prior Quarters.

    Once I'm far enough into 2018 everything would be fine again but for the next 6 months my current approach isn't going to work.

    Let me know if you have any other ideas.

    Thanks for you help

    Randy

  • RGranada
    RGranada Contributor

    Hi

     

    Maybe your problem is not on the BeastMode I tried that in my account and it's returning the date 2017/12/31 for the current date.

     

    Are your "hiredate" and "dateOfTermination" DateTime fields?

     

    Regards,

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • Randyb
    Randyb Member

    The date of termination is a date time field however the time value doesn't change it is always 18:00

    I'm digging into the results detail and doing a comparison using hard coded dates to validate one more time, it may actually be working.

    Stay tuned.

    Randy

     

     

  • Randyb
    Randyb Member

    I've got a bouple of other issues going on that I discovered but I think this is working, thanks for your help

    Randy

  • user055174
    user055174 Member

    Hi, I have tested the proposed solution

    DATE_SUB(LAST_DAY(CURDATE()), interval -1 MONTH)

    and it breaks for February, it says 29th of January is no longer in the "last month", but in the "current month".

    So I am using this formula, instead

    CASE 

    WHEN DATE(`Payment Date`) > SUBDATE(CURRENT_DATE(), interval DAYOFMONTH(CURRENT_DATE()) DAY) THEN 'This Month' 

      WHEN DATE(`Payment Date`) < SUBDATE(CURRENT_DATE(), interval DAYOFMONTH(CURRENT_DATE()-1) DAY) 

       AND DATE(`Payment Date`) > SUBDATE(SUBDATE(CURRENT_DATE(), interval DAYOFMONTH(CURRENT_DATE()) DAY), interval DAYOFMONTH(SUBDATE(CURRENT_DATE(), interval DAYOFMONTH(CURRENT_DATE()) DAY)) DAY) THEN 'Last Month' 

      ELSE 'Not current or previous month'

    END

  • GrantSmith
    GrantSmith Coach

    Hi @user055174

    You could also utilize the last_day function to make it a bit simpler. LAST_DAY just returns the last day in the month for a given date.


    CASE 
WHEN LAST_DAY(`dt`) = LAST_DAY(CURRENT_DATE()) THEN 'This Month'
WHEN LAST_DAY(`dt`) = LAST_DAY(DATE_SUB(CURRENT_DATE(), interval 1 month)) THEN 'Last Month'
WHEN LAST_DAY(`dt`) = LAST_DAY(DATE_ADD(CURRENT_DATE(), interval 1 month)) THEN 'Next Month'
ELSE 'Other Month'
END


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories