trying to get ActivityEvents from PowerBI admin for more than one day using TimeXtender REST API

“this doesn't work” OK, so what happens? Any error messages? No results? Wrong results? Too many results?

Are BDAT and EDAT in the exact format the endpoint is expecting?

Hi,

if you turn on debug logging you will be able to see what call URI gets generated which will help with tuning the query you are using. With respect to the query: I would rewrite that query to be a bit better - if you are on SQL 2022+ you can use generate series:

WITH d AS 
(
    SELECT DATEADD(DAY, -s.value, CAST(SYSDATETIME() AS date)) AS d
    FROM GENERATE_SERIES(0, 4) AS s
)
SELECT
    CAST(d AS datetime2(0)) AS [startTime],
    DATEADD(SECOND, -1, DATEADD(DAY, 1, CAST(d AS datetime2(0)))) AS [endTime]
FROM d
ORDER BY [startTime]

For SQL 2019 and older:

WITH n(i) AS 
(
    SELECT i FROM (VALUES (0),(1),(2),(3),(4)) AS v(i)
),
d AS 
(
    SELECT DATEADD(DAY, -i, CAST(SYSDATETIME() AS date)) AS d
    FROM n
)
SELECT
    CAST(d AS datetime2(0)) AS [startTime],
    DATEADD(SECOND, -1, DATEADD(DAY, 1, CAST(d AS datetime2(0)))) AS [endTime]
FROM d
ORDER BY [startTime]

You can use a tally table if you need more than a handful of days and use the FORMAT() function to tune the output string representation of the timestamps.

I get data in the ODX but just for one day, the current date. (@i = 0)

Probably because your piece of SQL effectively generates five queries and only the first one is actually used. You should change that so your query returns five rows in one result set.

Something like this. There are better ways to do it ;)

with x as (select 0 as x union select 1 union select 2 union select 3 union select 4)
select CONCAT(FORMAT(dateadd(day,-x.x,GETDATE()), 'yyyy-MM-dd'),'T00%3A05%3A00.000') as [BDAT],
    CONCAT(FORMAT(dateadd(day,-x.x,GETDATE()), 'yyyy-MM-dd'),'T23%3A55%3A00.000') as [EDAT]
from x

Hi ​@HansZZV 

Have you tried to get the dates in a single query instead of loop, something like this?

WITH dates AS (
    SELECT 0 AS i
    UNION ALL
    SELECT i - 1
    FROM dates
    WHERE i > -4
)
SELECT
    CONCAT(FORMAT(DATEADD(DAY, i, GETDATE()), 'yyyy-MM-dd'), 'T00%3A05%3A00.000') AS BDAT,
    CONCAT(FORMAT(DATEADD(DAY, i, GETDATE()), 'yyyy-MM-dd'), 'T23%3A55%3A00.000') AS EDAT
FROM dates
OPTION (MAXRECURSION 10);

Hi,

I posted a long reply but it is waiting for moderation (perhaps because I included code snippets). I would turn on debug logging so you can see what the output of your SQL query is and whether that call would actually work. I would also rewrite your SQL query to avoid a while loop and simply subtract a second or millisecond from the todate to avoid all the formatting stuff.

My reply including a suggestion for a different query is being held for moderation too. 

Your SQL generates five different querys (and five result sets) instead of just one so only the first one is actually used.

Holding replies for moderation is a good way to stop users from trying to come op with solutions.

Hi ​@RLB ​@rory.smith 

Sorry the automatic spam detection mistakenly removed the replies, I have marked them as not spam now, we sincerely apologize for this. Thank you very much for your input