Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

  • Shuwyyyy's avatar
    Shuwyyyy
    6 years ago

    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

3 Replies

  • Shuwyyyy's avatar
    Shuwyyyy
    Advocate I
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you for your help Shuwyyy, this works perfectly!

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    6 years ago

    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.