SQL: Most Overdue pair of numbers?

Question

We have a this table and random data load:

CREATE TABLE [dbo].[webscrape](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [date] [date] NULL,
    [value1] [int] NULL,
    [value2] [int] NULL,
    [value3] [int] NULL,
    [value4] [int] NULL,
    [value5] [int] NULL,
    [sumnumbers] AS ([value1]+[value2]+[value3]+[value4]+[value5])
) ON [PRIMARY]


declare @date date = '1990-01-01',
@endDate date = Getdate()

while @date<=@enddate
begin
insert into [dbo].[webscrape](date,value1,value2,value3,value4,value5)
SELECT @date date,FLOOR(RAND()*(36-1)+1) value1,
FLOOR(RAND()*(36-1)+1) value2,
FLOOR(RAND()*(36-1)+1) value3,
FLOOR(RAND()*(36-1)+1) value4,
FLOOR(RAND()*(36-1)+1) value5

set @date = DATEADD(day,1,@date)
end

select * from [dbo].[webscrape] 

In SQL how can we return pair of values that have gone the longest without occurring on a given date?

And (if you happen to know) in Power BI Q&A NLP, how do we map so that so we can ask in natural language "when have the most overdue pairs occurred?"

Overdue being the pair of numbers with the longest stretch of time since occurring as of the given date.

UPDATE: I am trying this very ugly code. Any ideas:

  select *
    from (
      select date,value1 number1,value2 number2 from webscrape union all  
      select date,value1,value3 from webscrape union all
      select date,value1,value4 from webscrape union all
      select date,value1,value5 from webscrape union all
      select date,value2,value3 from webscrape union all
      select date,value2,value4 from webscrape union all
      select date,value2,value5 from webscrape union all
      select date,value3,value4 from webscrape union all
      select date,value3,value5 from webscrape union all
      select date,value4,value5 from webscrape 

    ) t order by date


    ----------------------------------

    select t.number1,t.number2, count(*)
     as counter
    from (
      select value1 number1,value2 number2 from webscrape union all  
      select value1,value3 from webscrape union all
      select value1,value4  from webscrape union all
      select value1,value5 from webscrape union all
      select value2,value3 from webscrape union all
      select value2,value4  from webscrape union all
      select value2,value5 from webscrape union all
      select value3,value4  from webscrape union all
      select value3,value5 from webscrape union all
      select value4,value5 from webscrape 
    ) t

group by t.number1,number2
order by counter

Thanks for any help.

Answer 1

If I get your point correctly you could use:

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY c1, c2 ORDER BY date DESC) AS rn
  FROM webscrape
  CROSS APPLY (
       SELECT c1 = IIF(c1 < c2, c1, c2), c2 = IIF(c1 > c2, c1, c2) 
       FROM (VALUES (value1, value2),
                    (value1, value3),
                    (value1, value4),
                    (value1, value5),
                    (value2, value3),
                    (value2, value4),
                    (value2, value5),
                    (value3, value4),
                    (value3, value5),
                    (value4, value5)) s(c1, c2)
       ) sub
) 
SELECT * 
FROM cte
WHERE rn = 1
ORDER BY date;

db<>fiddle demo

How it works:

1) CROSS APPLY is unpivot values to multiple rows + ordering them (c1,c2)

2) ROW_NUMBER partitioned by c1, c2 and ordered by date descending

3) Getting first occurence for each group and ordering by date

---

Quick check: The number of combination is n choose k:

36 choose 2 = 630