Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am using the below query to import the previous 7 days worth of data. I would like to change the query to pull a date range. Is this possible? I have tried every way I know, nothing works. I highlighted the part I think I need to change. My plan would be to open the query, and change the dates when I run it.
= Sql.Database("DCSQLPA01", "Operations", [Query="SELECT TOP (100) PERCENT RTRIM(r.Name) AS rName, pr.Type, pr.Sequence, d.ParkRideID, pr.RideName, DATEDIFF(minute, d.TimeDown, d.TimeUp) AS DownTimeMins, d.TimeDown, d.TimeUp, s.Name, RTRIM(d.Reason) AS Reason, #(lf) RTRIM(d.Resolution) AS Resolution, RTRIM(d.Comments) AS Comments, pr.ParkID, p.Name AS Expr1#(lf)FROM dbo.ParkRideDowntimes AS d INNER JOIN#(lf) dbo.ParkRides AS pr ON pr.ID = d.ParkRideID INNER JOIN#(lf) dbo.Parks AS p ON pr.ParkID = p.ID INNER JOIN#(lf) dbo.DowntimeSubtypes AS s ON d.DowntimeSubtypeID = s.ID INNER JOIN#(lf) dbo.Rides AS r ON pr.RideID = r.ID#(lf)WHERE (d.TimeDown >= DATEADD(day, - 7, GETDATE()))"])
Solved! Go to Solution.
Hi @dpattengale ,
There is 2 way you can get the data range into power bi:
step 01:Creata a calendar table and set the date range on your way
step 02: in the source query editor you could change your code like this:
SELECT TOP (100) PERCENT Rtrim(r.NAME) AS rName, pr.type, pr.sequence, d.parkrideid, pr.ridename, Datediff(minute, d.timedown, d.timeup) AS DownTimeMins, d.timedown, d.timeup, s.NAME, Rtrim(d.reason) AS Reason, Rtrim(d.resolution) AS Resolution, Rtrim(d.comments) AS Comments, pr.parkid, p.NAME AS Expr1# FROM dbo.parkridedowntimes AS d INNER JOIN dbo.parkrides AS pr ON pr.id = d.parkrideid INNER JOIN dbo.parks AS p ON pr.parkid = p.id INNER JOIN dbo.downtimesubtypes AS s ON d.downtimesubtypeid = s.id INNER JOIN dbo.rides AS r ON pr.rideid = r.id WHERE d.timedown >= Dateadd(day, -7, Getdate())
To look at a specific date range change the Where-statement to e.g.
WHERE d.timedown between '2019-01-01' and '2019-09-30'
Cheers,
Pratima
Hi @dpattengale ,
There is 2 way you can get the data range into power bi:
step 01:Creata a calendar table and set the date range on your way
step 02: in the source query editor you could change your code like this:
SELECT TOP (100) PERCENT Rtrim(r.NAME) AS rName, pr.type, pr.sequence, d.parkrideid, pr.ridename, Datediff(minute, d.timedown, d.timeup) AS DownTimeMins, d.timedown, d.timeup, s.NAME, Rtrim(d.reason) AS Reason, Rtrim(d.resolution) AS Resolution, Rtrim(d.comments) AS Comments, pr.parkid, p.NAME AS Expr1# FROM dbo.parkridedowntimes AS d INNER JOIN dbo.parkrides AS pr ON pr.id = d.parkrideid INNER JOIN dbo.parks AS p ON pr.parkid = p.id INNER JOIN dbo.downtimesubtypes AS s ON d.downtimesubtypeid = s.id INNER JOIN dbo.rides AS r ON pr.rideid = r.id WHERE d.timedown >= Dateadd(day, -7, Getdate())
To look at a specific date range change the Where-statement to e.g.
WHERE d.timedown between '2019-01-01' and '2019-09-30'
Cheers,
Pratima
Thank you for your help Shuwyyy, this works perfectly!
Hi @Anonymous ,
where did you get that string from? And what have you tried? Have you recived any error messages? And what range do you want to look at?
You could change your code like this:
1. In power query, for your table, double click on the Source-step.
2. Expand the Advanced options
3. You will see your current query in the SQL Statement box.
Your code in the SQL Statement-window should look something like this:
SELECT TOP (100) PERCENT Rtrim(r.NAME) AS rName, pr.type, pr.sequence, d.parkrideid, pr.ridename, Datediff(minute, d.timedown, d.timeup) AS DownTimeMins, d.timedown, d.timeup, s.NAME, Rtrim(d.reason) AS Reason, Rtrim(d.resolution) AS Resolution, Rtrim(d.comments) AS Comments, pr.parkid, p.NAME AS Expr1# FROM dbo.parkridedowntimes AS d INNER JOIN dbo.parkrides AS pr ON pr.id = d.parkrideid INNER JOIN dbo.parks AS p ON pr.parkid = p.id INNER JOIN dbo.downtimesubtypes AS s ON d.downtimesubtypeid = s.id INNER JOIN dbo.rides AS r ON pr.rideid = r.id WHERE d.timedown >= Dateadd(day, -7, Getdate())
To look at a specific date range change the Where-statement to e.g.
WHERE d.timedown >= Dateadd(day, -14, Getdate()) && d.timedown <= Dateadd(day, -7, Getdate())
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |