Skip to Content (Press Enter)
2022-03-29 16-12-55
Gonçalo Aguiar
189Views
14Comments
Solved
SQL - Closest date to current date
Question
SQL

Hi all,

Current my table Info is as follow:

Id | ContactId |Date            | 

1 |          1         | 2022/12/12

2|           1         |2021/01/01 

3|           2         |2017/09/27 

4|           2         |2023/09/27 

5|           2         |2023/01/01 

So I want to apply a filter to return the date closest to the <= current date and show it by contact but without grouping.

The result should be as follow 

Id | ContactId |Date            | 

1 |          1         | 2022/12/12

5|           2         |2023/01/01 

What is the best way to get this result?

Thanks

1
0
02 Jan 2023
Copy Link
2021-10-04 07-48-45
Sebastian Krempel
Champion
Solution

Hi Gonçalo,

if you only need the latest date per contact and do not need the Ids, a GROUP BY is the best solution and can be done in an aggregate:

  1. Add your entity (here: YourTable) as source
  2. Add the Filter: YourTable.Date <= CurrDate()
  3. Group by ContactId
  4. Right click the Date column and add the Max of Date

If you also need the Id you need a sub-select. This can be done with the following SQL query:

  1. Add your entity or a suitable structure as output structure
  2. Use the following SQL statement:
    SELECT {YourTable}.*
    FROM (
        SELECT
            {YourTable}.[ContactId],
            MAX({YourTable}.[Date]) AS [Date]
        FROM {YourTable}
        WHERE {YourTable}.[Date] <= GETDATE()
        GROUP BY {YourTable}.[ContactId]
        ) AS LATEST
    INNER JOIN {YourTable} ON
        {YourTable}.[ContactId] = LATEST.[ContactId] AND
        {YourTable}.[Date] = LATEST.[Date]

This will work if each date only appears once per contact. Otherwise, you can also group the result by ContactId and Date and select the MIN or MAX Id:

  1. Replace {YourTable}.* with MIN({YourTable}.[Id]), {YourTable}.[ContactId], {YourTable}.[Date]
  2. Add the last line GROUP BY {YourTable}.[ContactId], {YourTable}.[Date]

I hope that I helped you and would be pleased if you mark my answer as solution.

See solution in context
2
0
03 Jan 2023
Copy Link
2022-03-29 16-12-55
Gonçalo Aguiar

I tested the 2nd solution with OS sample entities and worked fine and when using filters also worked. 

When I test with my large DB in the filters it gets a timeout  using

(@IsCheck = 0 OR ({Table}.[Id) IN  (..solution provided..)

0
0
04 Jan 2023
Copy Link
2021-10-04 07-48-45
Sebastian Krempel
Champion

Replying to Gonçalo Aguiar's comment on 04 Jan 2023 23:27:25

It's hard to analyse where there is the performance issue without knowing the hole query. Have you tried

  1. only fetching what's really necessary,
  2. reducing joins,
  3. filtering out rows not needed in ON conditions of the JOINs instead of the WHERE clause,
  4. splitting things up into separate queries (utilizing common table expressions),
  5. using indexes on relevant columns?
0
0
05 Jan 2023
Copy Link
2021-09-06 15-09-53
Dorine Boudry
 
MVP

Why without grouping ?????  That seems to me to be the exact thing you should be doing

1
0
02 Jan 2023
Copy Link
2022-03-29 16-12-55
Gonçalo Aguiar

Hi @Dorine Boudry with grouping how would it look like? Using a checkbox to show the date for each contact.

0
0
03 Jan 2023
Copy Link
2021-09-06 15-09-53
Dorine Boudry
 
MVP

Replying to Gonçalo Aguiar's comment on 03 Jan 2023 10:24:08

see @Sebastian Krempel for the full and complete explanation on when you can just use grouping (in that case no need for an SQL widget) and when you need a sub select in an SQL node.


0
0
04 Jan 2023
Copy Link
2018-12-10 12-16-21
Goncalo Duarte Almeida

Without a grouping, you can use the SELECT DISTINCT Statement

1
0
02 Jan 2023
Copy Link
2022-08-03 04-32-50
Ravi Punjwani

Hi Goncalo,

Without using GROUP BY clause, any solution would need a sub-query, which is not an optimal solution.

Do you really don't want to use GROUP BY?

1
0
02 Jan 2023
Copy Link
2022-08-03 04-32-50
Ravi Punjwani

Hi @Gonçalo Aguiar 

The GROUP BY query would look like this

SELECT {MyEntity}.[ContactId], min(abs(datediff(d, @CurrDate, {MyEntity}.[myDate] )))
FROM {MyEntity}
GROUP BY {MyEntity}.[ContactId]

I've modified your fieldname Date to myDate for the sake of understanding.

This query would select only unique ContactIDs and display the minimum date difference (be it less than or greater than current date).

I haven't tested this query, but you can use it and do the required modifications as per your actual table and field names

0
0
03 Jan 2023
Copy Link
2022-08-03 04-32-50
Ravi Punjwani

Replying to Ravi Punjwani's comment on 03 Jan 2023 10:33:11

Hi @Gonçalo Aguiar 

Did you try this solution? Wondering if you encountered any issues in this method.

0
0
05 Jan 2023
Copy Link
2022-12-12 06-07-07
Dhivyaraj Sivam

Hi Goncalo,

Try this SQL query. Filter the last 30 days' data.

SELECT {Test}.* FROM {Test} WHERE{Test}.[Date] BETWEEN  @StartDate AND @EndDate 



Regards,

Dhivyaraj Sivam

0
1
03 Jan 2023
Copy Link
2022-03-29 16-12-55
Gonçalo Aguiar

Hi  @Dhivyaraj Sivam thanks for the input, the user wants with only a checkbox to show the closest date to today for each contact so a range date I guess is not the best approach .

1
0
03 Jan 2023
Copy Link
2022-12-12 06-07-07
Dhivyaraj Sivam

Replying to Gonçalo Aguiar's comment on 03 Jan 2023 10:22:32


Hi Goncalo,

SELECT top (2) {Test}.* FROM {Test} ORDER BY ABS( DATEDIFF(day,{Test}.[Date], GETDATE()));


SELECT Top 2 {Test}.* FROM {Test} WHERE  {Test}.[Date] <= CONVERT(date, getdate()) ORDER BY {Test}.[Date] DESC

Please check this one.

Thanks,

0
0
03 Jan 2023
Copy Link
2024-03-15 07-22-55
Ashish Kumar Srivastava

Hi Goncalo,

Try this SQL query. 

Use Day DATEDIFF () and Order By Clause to find the closest.

SELECT Top (1) {Test}.[ContactId],{Test}.[Date], ABS(DATEDIFF(day,{Test}.[Date], GETDATE())) As daydiff FROM {Test} Order BY daydiff


Thanks

0
0
03 Jan 2023
Copy Link
2021-10-04 07-48-45
Sebastian Krempel
Champion
Solution

Hi Gonçalo,

if you only need the latest date per contact and do not need the Ids, a GROUP BY is the best solution and can be done in an aggregate:

  1. Add your entity (here: YourTable) as source
  2. Add the Filter: YourTable.Date <= CurrDate()
  3. Group by ContactId
  4. Right click the Date column and add the Max of Date

If you also need the Id you need a sub-select. This can be done with the following SQL query:

  1. Add your entity or a suitable structure as output structure
  2. Use the following SQL statement:
    SELECT {YourTable}.*
    FROM (
        SELECT
            {YourTable}.[ContactId],
            MAX({YourTable}.[Date]) AS [Date]
        FROM {YourTable}
        WHERE {YourTable}.[Date] <= GETDATE()
        GROUP BY {YourTable}.[ContactId]
        ) AS LATEST
    INNER JOIN {YourTable} ON
        {YourTable}.[ContactId] = LATEST.[ContactId] AND
        {YourTable}.[Date] = LATEST.[Date]

This will work if each date only appears once per contact. Otherwise, you can also group the result by ContactId and Date and select the MIN or MAX Id:

  1. Replace {YourTable}.* with MIN({YourTable}.[Id]), {YourTable}.[ContactId], {YourTable}.[Date]
  2. Add the last line GROUP BY {YourTable}.[ContactId], {YourTable}.[Date]

I hope that I helped you and would be pleased if you mark my answer as solution.

2
0
03 Jan 2023
Copy Link
2022-03-29 16-12-55
Gonçalo Aguiar

I tested the 2nd solution with OS sample entities and worked fine and when using filters also worked. 

When I test with my large DB in the filters it gets a timeout  using

(@IsCheck = 0 OR ({Table}.[Id) IN  (..solution provided..)

0
0
04 Jan 2023
Copy Link
2021-10-04 07-48-45
Sebastian Krempel
Champion

Replying to Gonçalo Aguiar's comment on 04 Jan 2023 23:27:25

It's hard to analyse where there is the performance issue without knowing the hole query. Have you tried

  1. only fetching what's really necessary,
  2. reducing joins,
  3. filtering out rows not needed in ON conditions of the JOINs instead of the WHERE clause,
  4. splitting things up into separate queries (utilizing common table expressions),
  5. using indexes on relevant columns?
0
0
05 Jan 2023
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.