Блог

У меня есть таблица SQL Server следующим образом. Я хотел бы сгруппировать по имени и месту проведения теста, упорядочить по возрастанию даты как раздел на основе вышеупомянутой группировки.

теперь предоставляется настраиваемое окно, например: 4 дня. В таблице ниже, если дата первого теста — 01.02.2019 (1 февраля) — его оценка учитывается, и любая другая оценка теста, которая была пересдана в течение следующего 4-дневного окна, не учитывается. Если запись также попадает в 4-дневное окно уже исключенного элемента пример строки id — 4 , это также должно быть исключено.

Любые инструкции SQL для этой логики очень ценятся.

 CREATE TABLE test(  
    [recordid] int IDENTITY(1,1) PRIMARY KEY,
    [name] [nvarchar](25) NULL,
    [testcentre] [nvarchar](25) NULL,
    [testdate] [smalldatetime] NOT NULL,
    [testscore] [int],
    [Preferred_Output] [int],
    [Result] [nvarchar](75) NULL
)

GO
INSERT INTO test
           (
    [name],
    [testcentre],
    [testdate],
    [testscore],
    [Preferred_Output],
    [Result]  )
    VALUES
('George','bangalore',' 02/01/2019',1,1,'Selected as first item -grouped by name and location'),
('George','bangalore',' 02/02/2019',0,0,'ignore as within 4 days'),
('George','bangalore',' 02/04/2019',1,0,'ignore as within 4 days'),
('George','bangalore',' 02/06/2019',3,0,'ignore as within 4 days from already ignored item -04-02-2019'),
('George','bangalore',' 02/15/2019',2,2,'Selected as second item -grouped by name and location'),
('George','bangalore',' 02/18/2019',5,0,'ignore as within 4 days of previous'),
('George','Pune',' 02/15/2019',4,3,'Selected as third item'),
('George','Pune',' 02/18/2019',6,0,'ignore as within 4 days of previous'),
('George','Pune',' 02/19/2019',7,0,'ignore as within 4 days of previous'),
('George','Pune',' 02/20/2019',8,0,'ignore as within 4 days of previous')

GO
select * from test
GO



 ---------- -------- ------------ ------------ ----------- ------------------ 
| recordid |  name  | testcentre |  testdate  | testscore | Preferred_Output |
 ---------- -------- ------------ ------------ ----------- ------------------ 
|        1 | George | bangalore  | 02/01/2019 |         1 |                1 |
|        2 | George | bangalore  | 02/02/2019 |         0 |                0 |
|        3 | George | bangalore  | 02/04/2019 |         1 |                0 |
|        4 | George | bangalore  | 02/06/2019 |         3 |                0 |
|        5 | George | bangalore  | 02/15/2019 |         2 |                2 |
|        6 | George | bangalore  | 02/18/2019 |         5 |                0 |
|        7 | George | Pune       | 02/15/2019 |         4 |                3 |
|        8 | George | Pune       | 02/18/2019 |         6 |                0 |
|        9 | George | Pune       | 02/19/2019 |         7 |                0 |
|       10 | George | Pune       | 02/20/2019 |         8 |                0 |
 ---------- -------- ------------ ------------ ----------- ------------------ 
  

Я не думаю, что для этого требуется рекурсивный запрос. Вы хотите сравнить даты между последовательными записями, так что это своего рода проблема пробелов и островов, когда требуется определить начало каждого острова.

Оконные функции могут это сделать:

 select t.*,
    case when lag_testdate is null or testdate > dateadd(day, 4, lag_testdate)
        then testscore
        else 0
    end new_core
from (
    select t.*, lag(testdate) over(partition by name, testcentre order by testdate) lag_testdate
    from test t
) t
  

Демонстрация на скрипке DB