SQL DB data source: Need to pull data by the previous calendar month

Michele Adamski
Michele Adamski Altair Community Member
edited April 2021 in Community Q&A

Hi all, I'm new to developing in Monarch and need a little help. 

I need to create a report, pulling data from a SQL DB.

I’ve connected to the DB, located the correct tables and columns, and created the sort and saved the workspace.

However, I need to pull data by the previous calendar month and I don’t see an option to set a date by a variable. Is this something that can happen?

 image

Tagged:

Best Answer

  • CPorthouse
    CPorthouse
    Altair Employee
    edited April 2021 Answer ✓

    Are you looking to pull data from the previous 30 days or dates from the previous month?  For instance, if you take today's date 4/21/2021, do you need the data from 3/22-4/21/2021 or all days from March 2021?

    Either case, you will most likely need to use a query and a WHERE clause to get the data you are looking for.  To get the last 30 days you could add this to the end of the query statement that DPS generates:

    WHERE CreatedDateTime >= DATEADD(DAY,-30, GETDATE())

    The above says to get any row where CreatedDateTime is within the last 30 days.

Answers

  • Baba_Majekodunmi_703
    Baba_Majekodunmi_703
    Altair Employee
    edited April 2021

    Hi Michele,

     

    Welcome to Monarch and Congrats for taking a dive right into it!

    Are you familiar with writing SQL Scripts? Seems like that might be a good option here. Most of the time I've personally used scripts, but let me test and see on my end how to simulate this.

     

    Do you need detail data or just a summary from SQL? Will you be joining it to another data set?

  • CPorthouse
    CPorthouse
    Altair Employee
    edited April 2021 Answer ✓

    Are you looking to pull data from the previous 30 days or dates from the previous month?  For instance, if you take today's date 4/21/2021, do you need the data from 3/22-4/21/2021 or all days from March 2021?

    Either case, you will most likely need to use a query and a WHERE clause to get the data you are looking for.  To get the last 30 days you could add this to the end of the query statement that DPS generates:

    WHERE CreatedDateTime >= DATEADD(DAY,-30, GETDATE())

    The above says to get any row where CreatedDateTime is within the last 30 days.

  • Michele Adamski
    Michele Adamski Altair Community Member
    edited April 2021

    Hi Baba and Chris - 

    No, I'm not SQL savvy but I'm trying to get there. 

     

    I'm looking run the job on the last day of the calendar month and pull all data for the previous month. Even if I get an extra day or two - depending on the month - that's okay. We can manually modify the data that we get. It currently takes 4+ hours to pull this data now. 

     

    I'll give your suggestion a try, Chris! (However, any other input is greatly appreciated!)

     

    Thanks to both of you for your input! 

  • Baba_Majekodunmi_703
    Baba_Majekodunmi_703
    Altair Employee
    edited April 2021
    Michele Adamski said:

    Hi Baba and Chris - 

    No, I'm not SQL savvy but I'm trying to get there. 

     

    I'm looking run the job on the last day of the calendar month and pull all data for the previous month. Even if I get an extra day or two - depending on the month - that's okay. We can manually modify the data that we get. It currently takes 4+ hours to pull this data now. 

     

    I'll give your suggestion a try, Chris! (However, any other input is greatly appreciated!)

     

    Thanks to both of you for your input! 

    Hi Michele,

     

    I have a few options for you, and I actually recommend you try all three and see which one works best. I will try and share a video recording as well that covers each of these.

     

    1. Data Prep Studio: Using the 'Equal to' date filter, then duplicating that changing the date to Greater than, and then appending those two. Here's the finished result.

    image

    2. Data Prep Studio: Import the whole table and use the filtering options to narrow it down to what you need. This might not be reasonable depending upon the size of the database table, but it's still an option. You could also limit the number of rows.

    image

    3. Classic: Last but not least you can also import the data from SQL in Classic, and Classic has some native Import filters, but it does require you learn how to write the expression in Monarch filtering language, which we can help you with.

     

    image

  • Baba_Majekodunmi_703
    Baba_Majekodunmi_703
    Altair Employee
    edited April 2021
    Michele Adamski said:

    Hi Baba and Chris - 

    No, I'm not SQL savvy but I'm trying to get there. 

     

    I'm looking run the job on the last day of the calendar month and pull all data for the previous month. Even if I get an extra day or two - depending on the month - that's okay. We can manually modify the data that we get. It currently takes 4+ hours to pull this data now. 

     

    I'll give your suggestion a try, Chris! (However, any other input is greatly appreciated!)

     

    Thanks to both of you for your input! 

    Here is the video

     
  • Michele Adamski
    Michele Adamski Altair Community Member
    edited April 2021

    Yes, that worked perfectly! Thank you!

     

    Now I have another question - Will support only provide support when something is broken? Or, will they help when you don't know what you're doing? 

     

    I've pulled all the info that I need from the DB and saved it into a workspace with sorts and groups. When I view it in "prepare", it all looks good. If I view it in "preview", the sorts and groups are stripped away and all the data is there. I'm not quite sure what's wrong or how to fix it. 

  • Baba_Majekodunmi_703
    Baba_Majekodunmi_703
    Altair Employee
    edited April 2021
    Michele Adamski said:

    Yes, that worked perfectly! Thank you!

     

    Now I have another question - Will support only provide support when something is broken? Or, will they help when you don't know what you're doing? 

     

    I've pulled all the info that I need from the DB and saved it into a workspace with sorts and groups. When I view it in "prepare", it all looks good. If I view it in "preview", the sorts and groups are stripped away and all the data is there. I'm not quite sure what's wrong or how to fix it. 

    Hi Michele,

    Which answer was helpful and worked perfectly for you, for our reference? Importing the whole database and filtering afterwards?

    Yes: Support only provides assistance if something is not working as intended, which are long winded words for your word; broken :-).

    Not sure if we've had a chance to meet, I believe you've meet Rebecca, but I'm the Senior Manager of our Customer Success team, we can help provide you with a little more guidance and proactive support. Let me know if you want to discuss further.

    Yes the 'Preview' stage is just for previewing and assessing the quality of the data from your structured data source (excel, csv, databases). So nothing is wrong with that. You want to stay in the 'Prepare' stage, that includes the 'Load Plan' that shows all of your data sources and the connections, joins, and appends between them.

    • Does that help?
    • What are you looking to do?
    • Do an append for a month over month view?
    • Do a monthly variance by month?
    • How come this used to take 4 hours?
    • What business use case are you addressing with this process?
  • Michele Adamski
    Michele Adamski Altair Community Member
    edited April 2021
    Baba Majekodunmi_21731 said:

    Hi Michele,

    Which answer was helpful and worked perfectly for you, for our reference? Importing the whole database and filtering afterwards?

    Yes: Support only provides assistance if something is not working as intended, which are long winded words for your word; broken :-).

    Not sure if we've had a chance to meet, I believe you've meet Rebecca, but I'm the Senior Manager of our Customer Success team, we can help provide you with a little more guidance and proactive support. Let me know if you want to discuss further.

    Yes the 'Preview' stage is just for previewing and assessing the quality of the data from your structured data source (excel, csv, databases). So nothing is wrong with that. You want to stay in the 'Prepare' stage, that includes the 'Load Plan' that shows all of your data sources and the connections, joins, and appends between them.

    • Does that help?
    • What are you looking to do?
    • Do an append for a month over month view?
    • Do a monthly variance by month?
    • How come this used to take 4 hours?
    • What business use case are you addressing with this process?

    Good morning! 

    Firstly, the SQL query that Chris gave me helped me to pull the last 30 days. When I was in "Edit Table Info", under "Tables & Views", it looked as if I needed to give it a static date and time. So, editing the SQL query solved that part of my problem. 

    I marked that as the correct answer to my initial question. 

     

    Now that I've seen your newest reply, I'm going to create something new because I've got a sort and a groupby in one, single workspace and I don't think that's going to give us what we need. 

     

    It used to take four hours to pull this information together because it's coming from our ticketing system and was manually extracted and organized into Excel every month. 

     

    This is to garner monthly metrics of all incidents opened by a specific team, then I also need that same information grouped by priority, average number of days and hours the incident remained opened until resolved. 

Categories