Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Updating date in Query daily

I have the following Query, which has pulled historical data from a database for the last 7 months into Power BI.

 

Query="SET NOCOUNT ON#(lf)DECLARE @StartDate DateTime#(lf)DECLARE @EndDate DateTime#(lf)SET @StartDate = '20210514 07:00:00.000'#(lf)SET @EndDate = '20211214 07:00:00.000'#(lf)SET NOCOUNT OFF#(lf)SELECT  * FROM (#(lf)SELECT History.TagName, DateTime, Value, vValue, StartDateTime#(lf) FROM History#(lf) WHERE History.TagName IN ('S03_FT03_04_TOT01')#(lf) AND wwRetrievalMode = 'Delta'#(lf) AND wwVersion = 'Latest'#(lf) AND DateTime >= @StartDate#(lf) AND DateTime <= @EndDate) temp WHERE temp.StartDateTime >= @StartDate"])

 

 

I need the Query to refresh every day at 7am and update the report with the historical values over the past day. Is there a functionality for this in Power BI? I am not familiar with SQL so not sure how to modify this Query to achieve this.

Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    Anonymous  try this

    SET NOCOUNT ON

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = (
		SELECT CONVERT(DATETIME, CONCAT (
					CONVERT(VARCHAR(4), YEAR(GETDATE())-1)
					,'-'
					,CONVERT(VARCHAR(2), MONTH(GETDATE())-1)
					,'-'
					,CONVERT(VARCHAR(2), DAY(GETDATE())-1)
					,' 07:00:00.000'
					))
		)
SET @EndDate = (
		SELECT CONVERT(DATETIME, CONCAT (
					CONVERT(VARCHAR(4), YEAR(GETDATE()))
					,'-'
					,CONVERT(VARCHAR(2), MONTH(GETDATE()))
					,'-'
					,CONVERT(VARCHAR(2), DAY(GETDATE()))
					,' 07:00:00.000'
					))
		)
SET NOCOUNT OFF

5 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Anonymous  you can pass on a dynamic scalar value for the @EndDate

     

     

    --EndDate=GETDATE()

SET NOCOUNT ON

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '20210514 07:00:00.000'
SET @EndDate = (
		SELECT CONVERT(DATETIME, CONCAT (
					CONVERT(VARCHAR(4), YEAR(GETDATE()))
					,'-'
					,CONVERT(VARCHAR(2), MONTH(GETDATE()))
					,'-'
					,CONVERT(VARCHAR(2), DAY(GETDATE()))
					,' 07:00:00.000'
					))
		)
SET NOCOUNT OFF

     

     

     

     

    --EndDate=GETDATE()-1

SET NOCOUNT ON

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '20210514 07:00:00.000'
SET @EndDate = (
		SELECT CONVERT(DATETIME, CONCAT (
					CONVERT(VARCHAR(4), YEAR(GETDATE())-1)
					,'-'
					,CONVERT(VARCHAR(2), MONTH(GETDATE())-1)
					,'-'
					,CONVERT(VARCHAR(2), DAY(GETDATE())-1)
					,' 07:00:00.000'
					))
		)
SET NOCOUNT OFF

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      smpa01 

       

      Thanks smpa. Do you know how I can do the same for StartDate, i.e. set it as yesterday? I am not sure why you did GETDATE() -1 for the EndDate. Will this not set yesterday as the EndDate? 

      • smpa01's avatar
        smpa01
        Community Champion
        4 years ago

        Anonymous  if you are running the SQL query today, what should be the 

        StartDate

        and 

        EndDate