finra --EH 09-03-01 WITH recent_users AS ( SELECT *, CAST(CONCAT(CAST(year AS VARCHAR), '-', CAST(month AS VARCHAR), '-', CAST(day AS VARCHAR)) AS DATE) AS partition_date FROM dsa_datalake.dsa_ad_human_users ) SELECT '#' + ISNULL(CONVERT(VARCHAR, t.workspacetypename), '.') + '#' + ISNULL(FORMAT(t.createdon, 'MM-dd'), '.') AS Date, '#' + ISNULL(CONVERT(VARCHAR, t.workspacetypename), '.') + '#' + ISNULL(CONVERT(VARCHAR, t.locationname), '.') AS Location, '#' + ISNULL(CONVERT(VARCHAR, t.workspacetypename), '.') + '#' + ISNULL(CONVERT(VARCHAR, t.floornumber), '.') AS Floor, '#' + ISNULL(CONVERT(VARCHAR, t.workspacetypename), '.') + '#' + ISNULL(CONVERT(VARCHAR, t.deskname), '.') AS Desk_Name, '#' + ISNULL(CONVERT(VARCHAR, t.workspacetypename), '.') + '#' + ISNULL(CONVERT(VARCHAR, t.deskbookedtousername), '.') AS Name, '#' + ISNULL(CONVERT(VARCHAR, t.workspacetypename), '.') + '#' + ISNULL(CONVERT(VARCHAR, u.jobtitle), '.') AS Title, '#' + ISNULL(CONVERT(VARCHAR, t.workspacetypename), '.') + '#' + ISNULL(CONVERT(VARCHAR, u.department), '.') AS Department FROM dsa_condeco_booking_details_future t LEFT JOIN recent_users u ON t.deskbookedtouseremail = u.mail AND u.partition_date = (SELECT MAX(partition_date) FROM recent_users) WHERE t.createdon > CAST(GETDATE() AS DATE) -- Filter for rows with createdon greater than today's date GROUP BY t.createdon, t.workspacetypename, t.locationname, t.floornumber, t.deskname, t.deskbookedtousername, u.department, u.jobtitle ORDER BY t.createdon, t.locationname, t.floornumber;