Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Query Assistance Please!

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()))"])

1 ACCEPTED SOLUTION
Shuwyyyy
Advocate I
Advocate I

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

View solution in original post

3 REPLIES 3
Shuwyyyy
Advocate I
Advocate I

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

Anonymous
Not applicable

Thank you for your help Shuwyyy, this works perfectly!

sturlaws
Resident Rockstar
Resident Rockstar

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.
Query Assistance Please.png

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors