The issue isn't so much that SQL server can't parse and format json it's that the data is represented in fundamentally different ways.  And what your translation layer is...

Fighting the tools is futile in most cases...

It seems the CASE conditions could be simplified because it's not necessary to test the CHARINDEX prior to REPLACE.  If the search characters are not present in the string then...

With the 2 SQL functions and 1 procedure above in place, the following steps should results in a functional Forum API with 32 .NET Core 3.1 API endpoints wired up...

.

.

.

It appears you're looking to join rows where p1.usr_kataloghierarchie is not null and p1.pm_extended_id equals the left 8 characters of p2.pm_extended_id.

select p1.usr_kataloghierarchie, p2.pm_extended_id
from pm_process p1
  ...

Are you using the .NET Framework or .NET Core?  The code above references System.Web.Services which implies the full framework.  Since you're migrating off of SOAP endpoints is this reference still...

Rob, sorry, yes.

Are you just saying that the project you started on that thread is in the same sort of position as this one or that you have some sort...

Thanks for the reply.  That works or why not just copy what Scott did

        SUM(CASE WHEN PAYCODE <> 'SICK' THEN...

It appears the math in example 3 is off a little.  You could try something like this

;with SampleData (person, costcenter, amount, paycode, [date]) as (
  ...

Where do you draw the lines between languages, applications, and tools?  In the SQL realm imo Redgate, Quest, etc. are "no code software" tools.  The output (if used properly) is...

This project and my Forum project are basically in the same situation.

https://www.sqlservercentral.com/forums/topic/forum-api-project

Hi there marksquall.  It shouldn't be necessary to combine the queries.  There are 2 queries because your question seemed to contain 2 parts.  Part 1, calculate the date difference in...