我如何返回每周的最新结果？

Question

我有两个表要处理，一个表有一个日期列表，它们对应的一周是属于的，另一个表的日期是一个人进行了8个测试中的任何一个(每个测试都有一行)的日期。无论考试是什么时候进行的，我都希望能够显示一年中每周的每一次测试的最新日期。这是我试图实现的输出的一个例子：

每周一次个人测试1测试2 |:-----------|:---------:|:----------:|-----------:|电话2019-01-06收1收2019-01-04收2019-01-12-15收点2019-01-13收1收2019-01-04收2019-01-11收2019- 012019-01-11 .每周一次.2019-01年

到目前为止，我已经能够(我想)弄清楚每周是否对每个人都有测试。

每周一次个人测试1测试2 |:-----------|:---------:|:----------:|-----------:| 2019-01-06收1收2019-01-04收空2019-01-13收1收空2019-01-11收获2019-0120 \x{##**$$}209-01-18\x{e76f}\x{e76f}209-01-2019-01*

为了达到这个目的，我有下面的查询。

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
)

我尝试过使用滞后来计算条目和语句之间的空数，其中空条目的数量是要返回的行数。

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 <=周末的自联接和联接，但我不认为这些方法有效。特别是这里的解决方案：Fetch the rows which have the Max value for a column for each distinct value of another column

所以我的问题是：

1. 是我想要的输出吗？
2. 如果是的话，到底有什么正确的方法才能把它输出出来？

我尝试在SQLFiddle中建立一个活生生的例子，因为这已经变得非常长和复杂，但这也不是很顺利。这是我第一次在谷歌上找不到答案，我一整天都在用谷歌搜索。救命啊！！

(为表格格式进行编辑，但似乎仍然不起作用.)

Answer 1

我已经回答了我自己的问题。如果我再把自己弄进这个洞里，把这贴给后人看。联系的职位包括了大部分的答案。左加入测试表到日期表中，以便对满足条件的每个测试复制dates表。下面我把测试放到一个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