If you are using SQL 2016 or above, and assuming the structure of MyPriceTable and price_history is the same, I would be inclined to ditch the trigger and set up...

CROSS APPLY can be used to make the multiple use of the same expression more readable:

SELECT DISTINCT N.[Organization Name], 
X.UniqueCPT,
C.[DESCRIPTION]
FROM dbo.CCN_837 N
CROSS APPLY ( VALUES(SUBSTRING(N.[Composite Med Procedure...

• This reply was modified 5 years, 6 months ago by Ken McKelvey.

Another approach would be to use recursion to find the sequence breaks:

WITH DocOrder
-- needed as gaps in documentID would mess up the /2 calculation
AS
(
SELECT documentID, ArrivedDate
,CAST(ROW_NUMBER() OVER...

• This reply was modified 5 years, 6 months ago by Ken McKelvey.
• This reply was modified 5 years, 6 months ago by Ken McKelvey.

This query seems to work with the OP’s test data but, like Brain’s and my initial set-based attempts, will fail with more realistic data.

On looking at this again, the options...

Thanks for the comments Brian. I am aware of the flaws with real data but do not have time to look at in more detail at the moment. There might...

• This reply was modified 5 years, 6 months ago by Ken McKelvey.

Consumable Test Data:

CREATE TABLE #t
(
documentID int NOT NULL PRIMARY KEY
,ArrivedDate date NOT NULL
);
INSERT INTO #t
VALUES (1, '20190102')
,(2, '20190102')
,(3, '20190102')
,(4, '20190103')
,(5, '20190103')
,(6, '20190104')
,(7, '20190104')
,(8, '20190104')
,(9, '20190104')
,(10, '20190107');

This works...

You can generate the MOVEs from the FILELIST.

Personally I would not EXEC the results of anything like this. I would just print the result and then eyeball/alter it before copying...

Sorry for my blanket statement about NULLs - EndDate and Payment are good examples of when not to use them.

I am afraid I disagree with you about codes. Sooner or...

Ola Hallengren's maintenance solution sets this up as the default.

https://ola.hallengren.com/sql-server-backup.html

You should also look at automating the restore of you Full backups, to another database name, and running DBCC CHECKDB against...

It is best to store integers as integers, dates as dates etc. There is nothing wrong with having nulls.

I the case of Employee Numbers these are really codes, not integers....

These commands are part of the sqlserver powershell module obtainable from:

https://www.powershellgallery.com/packages/Sqlserver/21.1.18221

You could use an after trigger and insert the values in the deleted pseudo table into your history table.

With SQL2016 and above I would be inclines to make the table...

I have never used Write_DataTable as I am normally reading multiple csv files and do the BulkCopy bit directly in ADO.net.

You could look at loading the sqlserver module and using...

Out-DataTable is not built into Powershell. You have to download it from:

https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd

Then the first line of your Powershell script will be something like:

. "C:\YourScriptPath\Out-DataTable.ps1"

If this needs to be scheduled just use a powershell job step in SQL Server Agent.

Something like:

$SQLparams = @{
  'ServerInstance' = '.';
  'Database' = 'YourDB';
...