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.

not really sure what you mean. It's good you've posted sample data as DDL+DML, but if you could [edit] your question with a deterministic sample data and expected result it would help understand what you want to accomplish. Also, you should include what you've tried so far. — Zohar Peled, Jul 16 '19 at 15:14
With random numbers and/or lotteries, there is no such thing as HOT numbers or Overdue numbers. If the sample is truly random, each draw is independent of the prior, — John Cappelletti, Jul 16 '19 at 17:15
hot and cold is just a term, it's used in roulette and other games. just means frequency over universe. — Hell.Bent, Jul 17 '19 at 13:34

Lukasz Szozda · Accepted Answer · 2019-07-19T18:18:32.790

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

SQL: Most Overdue pair of numbers?

1 Answers1