Forum Discussion

jader3rd's avatar
jader3rd
Microsoft Employee
9 years ago
Solved

Applying filtered rows to the source

I'm connecting to a SQL view which has a lot of rows. Most of the time when we view the data we're going to be most interested in the last week or twos worth of data. I created StartTime and EndTime ...
  • Anonymous's avatar
    Anonymous
    9 years ago

    jader3rd,

    Change your codes to the following.

    let
    SQLSource = (StartTime as datetime, EndTime as datetime) => 
let
    Source = Sql.Database("MF-APDM-Shared1AlwaysOnVIP.OBDStripeDedicated-Prod-Bn2.Bn2.ap.gbl,89", "MDP_EXOMI", [Query="SELECT CONVERT(DATE, [START_TIME]) AS Day, [ACTION_REASON], [CHECKFAILED], COUNT(*) AS Cnt FROM [dbo].[HAOpBcsFailed] WHERE [START_TIME] >= '"& DateTime.ToText(StartTime) & "' AND [START_TIME] <= '"& DateTime.ToText(EndTime)&"' GROUP BY CONVERT(DATE, [START_TIME]), [ACTION_REASON], [CHECKFAILED]"]) 
in
    Source
in 
    SQLSource


    Regards,
    Lydia

Anonymous's avatar
Anonymous
Not applicable
9 years ago

jader3rd,

Change your codes to the following.

let
    SQLSource = (StartTime as datetime, EndTime as datetime) => 
let
    Source = Sql.Database("MF-APDM-Shared1AlwaysOnVIP.OBDStripeDedicated-Prod-Bn2.Bn2.ap.gbl,89", "MDP_EXOMI", [Query="SELECT CONVERT(DATE, [START_TIME]) AS Day, [ACTION_REASON], [CHECKFAILED], COUNT(*) AS Cnt FROM [dbo].[HAOpBcsFailed] WHERE [START_TIME] >= '"& DateTime.ToText(StartTime) & "' AND [START_TIME] <= '"& DateTime.ToText(EndTime)&"' GROUP BY CONVERT(DATE, [START_TIME]), [ACTION_REASON], [CHECKFAILED]"]) 
in
    Source
in 
    SQLSource


Regards,
Lydia

TDwivedi's avatar
TDwivedi
Frequent Visitor
7 years ago

Anonymous 

Hi, 

In my case I need to get first parameter from Database itself which I can't have as inner query due to some Cosmos limitation. 

SQL 1 - First query is to get the max of column value  - select max(column) from table

SQL 2 - select * from table where id = Value received from first query

Thank you in advance