Daterange on multiple columns

[hollowyourdream]

Hi there! I have a lot of columns with datetime that i want to see every count. I getting them from sql query to postgres and now i want to filter my summs with one filter on daterange. How can I do it? 
I've tried to do that with 2 datetime params but it doesnt work

let
    Start=DateTime.ToText(StartDate),
    End=DateTime.ToText(VEndDate),
    Source = PostgreSQL.Database("host", "dbName",
    [Query="select count(cv.""Date1"") as Date1Cnt
        , count(cv.""Date2"") as Date2Cnt
        , count(cv.""Date3"") as Date3Cnt
        from schemeName.""TableName"" as cv
        where (cv.""Date1"" >= TO_TIMESTAMP('" &Start& "', 'dd.mm.yyyy hh24:mi') and cv.""Date1"" < TO_TIMESTAMP('" &End& "', 'dd.mm.yyyy hh24:mi') or cv.""Date1"" is null)
        and (cv.""Date2"" >= TO_TIMESTAMP('" &Start& "', 'dd.mm.yyyy hh24:mi') and cv.""Date2"" < TO_TIMESTAMP('" &End& "', 'dd.mm.yyyy hh24:mi') or cv.""Date2"" is null)
        and (cv.""Date3"" >= TO_TIMESTAMP('" &Start& "', 'dd.mm.yyyy hh24:mi') and cv.""Date3"" < TO_TIMESTAMP('" &End& "', 'dd.mm.yyyy hh24:mi') or cv.""Date3"" is null)
"])
in
    Source

 And there is sample of filtering in sql 😃 

[CNENFRNL]

After you verify your sql works well in Postgres, you may want to try Value.NativeQuery() function; the advantage is straightforward, you can 100% copy the sql from Postgres. For your reference, here's an article on passing parameters to sql in Value.NativeQuery.

[hollowyourdream]

also i've tred to do from that example , but i have an error: Query (2,14) Parametr M "EndDate" undefined in model type or parametr M cannot be used (sry for translation, my English is bad) 

[v-lionel-msft]

Hi @hollowyourdream ,

Have you defined and referenced the ‘StartDate’ and'EndDate’ parameters correctly?

For example, the data type of the parameters?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hollowyourdream]

Yeah, i've seen this manual, but it is not work(

[v-lionel-msft]

Hi @hollowyourdream ,

Considerations and limitations 

It may be caused by this limitations.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hollowyourdream]

So, i've tried that with manual and it is not filtering(but i have bo error) 

let
Источник = PostgreSQL.Database("localhost:5432", "HrMetrics"),
hr_metrics_CvInfo = Источник{[Schema="hr_metrics",Item="CvInfo"]}[Data],
#"FilteredRows" = Table.SelectRows(hr_metrics_CvInfo, each [ApprovedCvDate] >= VoronkaStartDate and [ApprovedCvDate] <= VoronkaEndDate)
in
#"FilteredRows"

[hollowyourdream]

And have i any variants to do filter on multiple columns in postgres?

[hollowyourdream]

Okay, i really using postgres, but i heard that i can fake it as possible source. Is it true? And if it is, how can i do that?

[hollowyourdream]

How can i pass it throw filter or slice? I need user to select daterange