You can write a case statement in your order by clause like below - however this will not be an ideal solution because of hardcoded values . I guess better...

Instead of using row_number try using lead or lag

Thanks

I had similar issue going from 2008R2 to 2016 - after much trial and error found that Solution's Configuration Properties - DeployementTargertVersion was set to SQL Server 2017 - changing...

Came up with this but I am curious to know if there is a more elegant way to achieve it

SELECT  Id ,
    ...

Don't have an answer to this - but learned something new today. I would have expected it to fail as well but tested it out and it works !

Hi

The package will not be in SSISDB - it will be in Integration Services Catalog - which will be below all the database as a separate twistie

At first glance and without actually looking at the data looks like this is the problem - CAST(AR_USERENTRY AS NUMERIC(18, 2)) >= 0  You  can give TRY_CAST a try - as...

You can try self join - the one I have written brings two overlapping ids

SELECT * 
FROM #ChargeDataTemp AS cdt 
INNER JOIN #ChargeDataTemp AS cdt2 
ON cdt2.AssetReference...

I would add another condition to it

declare @a varchar(100) = 'GUDR200305R02'
--IF PATINDEX('[^0-9][^0-9][^0-9][^0-9][120000-999999][R][0-9][0-9]',@a)>0
IF PATINDEX('[^0-9][^0-9][^0-9][^0-9][0-9][0-9][0-9][0-9][0-9][0-9][R][0-9][0-9]',@a)>0  AND CAST(SUBSTRING(@a,5,2) AS INT) >= 12
Print 'T'
Else
Print 'F'

Thanks

I would have written the code differently - basically find the first day of next month and then subtract the number of days from there

However I have a feeling there...

In order to get the most recent record of  each patient from Patdata you can use something like below

;WITH cte_p AS 
(
SELECT p.id , surname , bed...

If the  question was just to convert union into join - I would have gone with full join with coalesce function -though this will not work in the view either

Well you can do this - makes it short not sure if actually better

SELECT c.* , CCL.column_names
FROM [RawDownloads].[dbo].tbl_K8_Customer C
LEFT JOIN [RawDownloads].[dbo].tbl_k8_Customer CCL
     ON...

I guess you can load the data in some sort of staging table first - clean it up there and then load it in the destination table

Thanks

Does this work for you

 ;WITH cte_test 
 
 AS ( SELECT *  
  , ROW_NUMBER() OVER (PARTITION BY clientid ORDER  BY ClientImportsId desc...