Another title for this post could be "Why is SSDT so braindead?" or "Does Microsoft have a clue about the needs of SSDT developers?"
It's ***software*** Microsoft - you could make this work if you invested a bit of thought and development effort into SSDT.
I really *want* to like SSDT, but run into crap like this almost daily requiring a convoluted workaround...
The below query works fine in SSMS. It also works as a view, but would require a linked server. But it fails miserably in SSDT as an OLEDB data source. Any ideas how to get this to work?
I could split the extract and deduping into two separate steps using an intermediate table, but am trying to improve performance by having it all execute on the source server before sending the results to the target server.
(My actual query doesn't use SELECT *, I'm just trying to save hundreds of lines in this post.)
-- Delta source table extract, new or changed rows between start and end dates
WITH cteDelta AS (
(? <= [valid_to_date] AND [valid_to_date] < ?)
(? <= [valid_from_date] AND [valid_from_date] < ?)
-- Dedup, only want the most recent row within the keys
cteDedup AS (
SELECT *, ROW=ROW_NUMBER() OVER (
PARTITION BY facility_identifier, stay_number, episode_sequence_number
ORDER BY valid_from_date DESC