从多个表中增加多个case函数的SQL语句

Question

我正在尝试增加SQL生成的列"Counter"通过一个案例陈述。我得到错误"无效列";在我的案例陈述中。如有任何帮助，不胜感激。

SELECT 0 as Counter(Not part of any table),
CASE
WHEN dateadd(HOUR, -1,GETDATE()) >= max (a.UPDATED_DATE)
THEN
Counter + 1 
WHEN dateadd(HOUR, -1,GETDATE()) >= max (b.UPDATED_DATE)
THEN
Counter + 1          
ELSE
Counter + 0
END  as Counter
FROM dbo.My_Dates a, Client_Dates b

Answer 1

在黑暗中拍摄。不清楚需要按什么顺序计算行，也不清楚两个表之间的关系。

with m as (
SELECT *,
case when max(a.UPDATED_DATE) over () >    max(b.UPDATED_DATE) over ()
then max(a.UPDATED_DATE) over () else max(b.UPDATED_DATE) over () end as last_update
FROM dbo.My_Dates a, Client_Dates b /* this cross join surely isn't right */
)
select
count(case when dateadd(hour, -1, getdate()) >= last_update then 1 end)
over (order by ??)
from m;

根据下面的注释，你只需要计算一堆表的最大值:

with d(last_updated) as (
select max(UPDATED_DATE) from T1  union all
select max(UPDATED_DATE) from T2  union all ...
select max(UPDATED_DATE) from T16
)
select count(case when dateadd(hour, -1, getdate()) >= last_update then 1 end) from d;