with unmatchedTranCounts as ( select 'rms.ordhead' as tableName, count_big(etl_update_ts) as todayRows 
from rms.ordhead t1 where t1.etl_update_ts >= convert(date, getutcdate()) union all select 'rms.shipment' as tableName, count_big(etl_update_ts) as todayRows from rms.shipment t2 where t2.etl_update_ts >=convert(date, getutcdate()) union all select 'rms.shipsku' as tableName, count_big(etl_update_ts) as todayRows from rms.shipsku t3 where t3.etl_update_ts >= convert(date, getutcdate()) union all select 'rms.sups' as tableName, count_big(etl_update_ts) as todayRows from rms.sups t4 where t4.etl_update_ts >= convert(date, getutcdate()) union all select 'rms.im_doc_head' as tableName, count_big(etl_update_ts) as todayRows from rms.im_doc_head t5 where t5.etl_update_ts >= convert(date, getutcdate()) union all select 'rms.tran_data_history' as tableName, count_big(etl_update_ts) as todayRows from rms.tran_data_history t6 
where t6.etl_update_ts >= convert(date, getutcdate()) union all select 'rms.im_invoice_detail' as tableName,count_big(etl_update_ts) as todayRows from rms.im_invoice_detail t7 where t7.etl_update_ts >=
convert(date, getutcdate()) union all select 'rms.tran_data_codes' as tableName, count_big(etl_update_ts) as todayRows from rms.tran_data_codes t8 where t8.etl_update_ts >= convert(date, getutcdate()))
select count(tableName) as tablesLoadedTodayfrom unmatchedTranCounts uwhere u.todayRows > 0


i am getting an error stating that"incorrect syntax near unmatchedTranCounts"

>Solution :

I used SSMS to reformat it after I fixed 2 typos. You had 2 in this line:

select count(tableName) as tablesLoadedTodayfrom unmatchedTranCounts uwhere u.todayRows > 0

You did not put a space before FROM and you need to remove the u just before where.

WITH unmatchedTranCounts
AS (SELECT 'rms.ordhead' AS tableName,
           COUNT_BIG(etl_update_ts) AS todayRows
    FROM rms.ordhead t1
    WHERE t1.etl_update_ts >= CONVERT(DATE, GETUTCDATE())
    UNION ALL
    SELECT 'rms.shipment' AS tableName,
           COUNT_BIG(etl_update_ts) AS todayRows
    FROM rms.shipment t2
    WHERE t2.etl_update_ts >= CONVERT(DATE, GETUTCDATE())
    UNION ALL
    SELECT 'rms.shipsku' AS tableName,
           COUNT_BIG(etl_update_ts) AS todayRows
    FROM rms.shipsku t3
    WHERE t3.etl_update_ts >= CONVERT(DATE, GETUTCDATE())
    UNION ALL
    SELECT 'rms.sups' AS tableName,
           COUNT_BIG(etl_update_ts) AS todayRows
    FROM rms.sups t4
    WHERE t4.etl_update_ts >= CONVERT(DATE, GETUTCDATE())
    UNION ALL
    SELECT 'rms.im_doc_head' AS tableName,
           COUNT_BIG(etl_update_ts) AS todayRows
    FROM rms.im_doc_head t5
    WHERE t5.etl_update_ts >= CONVERT(DATE, GETUTCDATE())
    UNION ALL
    SELECT 'rms.tran_data_history' AS tableName,
           COUNT_BIG(etl_update_ts) AS todayRows
    FROM rms.tran_data_history t6
    WHERE t6.etl_update_ts >= CONVERT(DATE, GETUTCDATE())
    UNION ALL
    SELECT 'rms.im_invoice_detail' AS tableName,
           COUNT_BIG(etl_update_ts) AS todayRows
    FROM rms.im_invoice_detail t7
    WHERE t7.etl_update_ts >= CONVERT(DATE, GETUTCDATE())
    UNION ALL
    SELECT 'rms.tran_data_codes' AS tableName,
           COUNT_BIG(etl_update_ts) AS todayRows
    FROM rms.tran_data_codes t8
    WHERE t8.etl_update_ts >= CONVERT(DATE, GETUTCDATE()))
SELECT COUNT(tableName) AS tablesLoadedToday
FROM unmatchedTranCounts
WHERE u.todayRows > 0;