Import from ODBC based on date range to limit number of records imported

[KV943]

Hi,

I am working on updating an already functional query to import extra data.

The current code imports all data from our database for TODAY. So any records that have today's date associated with them are imported and then used to create visualizations and reports. I would like to edit the query so that when it refreshes it imports data for the whole month, not just Today.

The current code is:

Select a.*, b.team, c.FIRST_NAME,c.LAST_NAME
FROM MEP_ENG_EVENT_INTERVAL a, MEP_OD_STATION_TEAM b, MEP_OPERATOR c
where a.ABSN = b.station and
a.USER_ID = c.OPERATOR_ID and
a.event_date = (select max(event_date) from MEP_ENG_EVENT_INTERVAL) and
a.USER_ID <> 'RSSQL' and
a.DURATION < 3600

----------------------------------------------------------------------------------------------------------

a.event_date = (select max(event_date) from MEP_ENG_EVENT_INTERVAL) is the line that restricts the import to todays date.

How would I edit this line to make the query import the whole month?

Thank you!

[edhans]

The a.Duration line is your day - 3600 seconds. You cannot change it to a month because a month can be 28-31 days. But you can multiply that 3600 by the number of days you want, so 30 days would be 108,000. 

However, that is really a SQL question, not a Power Query question, so if I haven't answered your question, I'd recommend checking out a SQL forum for your product, including how to define months in the data.

[KV943]

3600 seconds is one hour not one day. We use that line to filter cycles that are less than 1 hour to accound for breaks and shift change.

The duration column in my data records the length of time it took the operator to complete their process. My dataset is from an assembly line where we track each stations cycle time for every process they complete.

User ID is the operators unique ID

Duration is how long the process took

event_date is the date the process took place.

Currently this code pulls all data from the current day. If i delete the line entirely
"a.event_date = (select max(event_date) from MEP_ENG_EVENT_INTERVAL)" then the query attempts to pull

all of the data from the database which is too many records to process. Its 20 years of data and milliions and millions of records.

It seems that the "Max(Event_date)" Is the portion of the code that needs to be changed to include a date filter but I cannot find the correct sytax that PowerBI will accept in that line.

Thanks

[v-yingjl · Accepted solution]

Hi @KV943 ,

The table name should be the whole name in the SQL statement when using Power Query ODBC connection since ODBC connector can only know your SQL_server name, not know the database name.

For example, if I only write like this, it would remind the error:

If I use the whole name of the table, like databasename.tablename, like this, it would get the result:

In this case, the statement provided by yours 'MEP_ENG_EVENT_INTERVAL' should be the whole name that you can try it.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.