Forum Discussion
Best way to retrieve, store data from SQL database for calendar Month.
I am looking for the best way to retrieve and store monthly data from a "non-local data stores" that are access via SQL.
Here is the SQL script that I am using.
SELECT h.DateTime, h.Tagname, h.value, q.QualityString as Quality -- Columns to get
FROM History h -- Tables to get from
INNER JOIN QualityMap q on h.QualityDetail = q.QualityDetail
WHERE Tagname like '%.TotalEnergy' -- Get only tags ending in .TotalEnergy
AND DateTime >= Dateadd(Month, -2, getdate()) -- start date
and wwRetrievalMode = 'Counter' -- Calculate the DELTA (Last Reading - Current Reading)
and wwResolution = 3600000 * 24 -- Make calculations over a day period over start/end time
I am wanting to change the start date, so that it is pulling the current Calendar year. I want it to start pulling the new years data once the new year hits.
I am going to set this up for incremental refresh, so I do not have to pull the prior months data each time.
Value.NativeQuery function accepts intact sql as parameter; above all, the query is excuted at the database side, thus the perfomance is guaranteed.
2 Replies
- CNENFRNLCommunity Champion
Value.NativeQuery function accepts intact sql as parameter; above all, the query is excuted at the database side, thus the perfomance is guaranteed.
- v-yingjlCommunity Support
Hi moltra ,
You can create parameters in power query and use it in the sql statement to change the date dynamically.
Here is a simliar issue and an article that you can refer:
- How do I pass parameters to my SQL statement?
- Power-query-m-passing-parameters-dynamically-to-a-sql-server-query
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.