我如何返回每周最近的结果?

Question

我有两个表要工作，其中一个表有日期列表及其所属的相应周，另一个表有一个人进行了一组8个测试中的任何一个测试的日期(每个测试一行)。我希望能够显示最近的日期，每个测试已经采取了每一周的一年，无论何时采取的测试。这是我试图实现的输出示例:

| Weekending | Personkey | Test 1 | Test 2|:-----------|:---------:|:----------:|-----------:|| 2019-01-06 | 1 | 2019-01-04 | 2018-12-15 || 2019-01-13 | 1 | 2019-01-04 | 2019-01-11 || 2019-01-20 | 1 | 2019-01-18 | 2019-01-11 |…|周末| Personkey |测试1 |测试2|:-----------|:---------:|:----------:|-----------:|| 2020-10-25 | 1 | 2019-01-18 | 2019-01-11 || 2020-11-01 | 1 | 2020-10-30 | 2019-01-11 || 2020-11-07 | 1 | 2020-10-30 | 2019-01-11 |

到目前为止，我已经能够(我认为)得到每个人在那个星期是否有一个测试，每周。

| Weekending | Personkey | Test 1 | Test 2|:-----------|:---------:|:----------:|-----------:|| 2019-01-06 | 1 | 2019-01-04 | null || 2019-01-13 | 1 | null | 2019-01-11 || 2019-01-20 | 1 | 2019-01-18 | null |…|周末| Personkey |测试1 |测试2|:-----------|:---------:|:----------:|-----------:|| 2020-10-25 | 1 | null | null| 2020-11-01 | 1 | 2020-10-30 | null || 2020-11-07 | 1 | null | null |

我有以下查询到这里。

with wkref  as (
Select distinct 
d.[DateKey]
,   d.FirstDayOfWeek
from Dates_table    d with(nolock)
where   d.CalendarYear  between 2018 and YEAR(getdate())
)
, checks as (
Select 
Dateadd(d, 6, w.FirstDayOfWeek) 'WeekEnding'
,   t.PersonKey
,   MAX(case 
when    t.Measurement   =   'Test1' then    t.EventDateKey
else    null 
end) 'Test1_Date'
,   MAX(case 
when    t.Measurement   =   'Test2' then    t.EventDateKey
else    null 
end) 'Test2_Date'
from    wkref w with(nolock)
left    join    Tests_table t with(nolock)
on  t.EventDateKey  =   w.DateKey
)

我已经尝试使用LAG计算条目和语句之间的空数，其中空条目的数量是延迟到的行数。

Select 
c.WeekEnding
,   c.PersonKey
,   c.partn
,   c.test1_Date
,   LAG(c.test1_date,partn-1,c.test1_Date) over(order by weekending) 'LatestTest1'
from (
Select 
c.WeekEnding
,   c.PersonKey
,   c.Test1_Date
,   ROW_NUMBER() over(partition by c.personkey, c.test1_date order by c.weekending asc) 'partn'
from checks c
) c

虽然这没有工作。我对ROW_NUMBER()的使用并不是返回非空值之间的行数，而是返回非空值的总数。然后，它不会填充所有非空行，只是填充已经有值的行-所以我知道我离正确答案还很远。

我尝试过更简单的选项，如自连接和基于testdate <= weekending的连接，我认为这些都不起作用。特别是这里的解决方案:从另一列的不同值中获取某一列的最大值

我的问题是:

1. 我想要的输出可能吗?
2. 如果是这样，到底什么才是正确的方法来解决这个问题?

我尝试在SQLFiddle中设置一个实时示例，因为这已经变得相当长且复杂，但也不是很顺利。这是我第一次无法在谷歌上找到答案，我已经用了一整天了。请帮助! !

(编辑表格格式，这似乎仍然不工作…)

Answer 1

我已经回答了自己的问题。把这个贴出来是为了后人，如果我再被关进这个洞的话。链接的帖子包含了大部分答案。将测试表左联接到日期表中，以便为满足条件的每个测试复制日期表。下面我将测试放入CTE中，以便只通过我感兴趣的测试。

with tests_cte as (
t.id
,  t.eventdate
from tests_table t
where t.testtype = 'test in question'
and 
t.eventdate between 'desired start' and 'desired end'
)
Select
d.FirstDayofWeek
,   count(distinct t1.id) 'People'
from dates_table d

left join tests_cte t
on t.eventdate between dateadd(d,-366,d.firstdayofweek) and d.firstdayofweek
where d.firstdayofweek between 'desired start date' and 'desired end date'
group by d.firstdayofweek