SQL statement to increment multiple case functions from multiple tables-CodePudding

I'm trying to increment the SQL generated column "Counter" by making 1 case statement. I get the error "Invalid Column" for Counter in my case statement. Any help is appreciated.

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

CodePudding user response：

Shot in the dark. It's not clear what order you need the rows to be counted or what the relationship between the two tables is.

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;

Per comment below it appears you just need to count the maxima across a bunch of tables:

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;