DECLARE @sql NVARCHAR(MAX) = ''
DECLARE @dbname NVARCHAR(255)

-- Cursor to iterate over each database
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE' AND name NOT IN ('master', 'tempdb', 'model', 'msdb') AND name LIKE 'ERASOFT_%'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = @sql + 'SELECT ''' + @dbname + ''' AS DatabaseName, COUNT(*) AS TOTAL, SUM(CASE WHEN a.NAMA=''15'' THEN 1 ELSE 0 END) AS TOKOPEDIA, SUM(CASE WHEN a.NAMA=''17'' THEN 1 ELSE 0 END) AS SHOPEE, SUM(CASE WHEN a.NAMA=''2021'' THEN 1 ELSE 0 END) AS TIKTOK, SUM(CASE WHEN a.NAMA=''7'' THEN 1 ELSE 0 END) AS LAZADA, SUM(CASE WHEN a.NAMA=''16'' THEN 1 ELSE 0 END) AS BLIBLI,  SUM(CASE WHEN a.NAMA<>''15'' AND a.NAMA<>''17'' AND a.NAMA<>''16'' AND a.NAMA<>''7'' AND a.NAMA<>''2021'' THEN 1 ELSE 0 END) AS OTHER  FROM [' + @dbname + ']..SOT01A s LEFT JOIN  [' + @dbname + ']..ARF01 a ON s.CUST=a.CUST WHERE s.TGL >= ''2024-07-10 00:00:00'' AND s.TGL < ''2024-07-11 00:00:00''  UNION ALL '

    FETCH NEXT FROM db_cursor INTO @dbname
END

-- Remove the last 'UNION ALL'
SET @sql = LEFT(@sql, LEN(@sql) - 10)

-- Execute the dynamic SQL
EXEC sp_executesql @sql

-- Cleanup
CLOSE db_cursor
DEALLOCATE db_cursor