if object_id('Rpt.fn_ParseJSON') is not null
drop function Rpt.fn_ParseJSON
GO

CREATE FUNCTION Rpt.fn_ParseJSON ( @JSON NVARCHAR(MAX))
--drop function Rpt.fn_ParseJSON
--created by R.Opeshansky
--created 10-11-2018
/*
Usage:
Select * from Rpt.fn_ParseJSON ('{    "Person": 
 ...

foreach ($provider in [System.Data.OleDb.OleDbEnumerator]::GetRootEnumerator())
{
    if ($provider.GetValue(0) -like "Microsoft.ACE*")
    {
        $v = New-Object PSObject
 ...

Great tip:

https://www.youtube.com/watch?v=YAURzI8kA9c

C# code

=====================================

public void Main()
        {
            // TODO: Add...

#Region "Imports"
Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports System.Data.OleDb
Imports Microsoft.SqlServer.Dts.Runtime
#End Region

'ScriptMain is the entry point class of the script.  Do not change the name, attributes,
'or parent of this...

SET NOCOUNT ON
-- Specify the table here - this is the only place you need to modify.
DECLARE @SchemaName sysname         SET @SchemaName = 'dbo'
DECLARE @TableName  sysname         SET @TableName  = 'Cardinality'
-- Declare variables.
DECLARE @CrLf       CHAR(2)         SET @CrLf       = CHAR(13) + CHAR(10)
DECLARE @Sql        NVARCHAR(MAX)   SET @Sql        = ''
DECLARE @ColumnName sysname         SET @ColumnName = ''
-- Show total number of rows in table.
SET @Sql = 'SELECT COUNT(*) AS "Row...

• This reply was modified 2 years, 1 month ago by  RVO.

Dear Frederico, I attached Execution Plans here. Graphical plans are pretty complicated. Lots of icons. I just used SET STATISTICS PROFILE ON and saved the results as CSV.  The stats...

Jeffrey look at the very first block of code at the top of this thread. There are no variables. It's a scalar function that returns data for all the records...

• This reply was modified 4 years, 11 months ago by  RVO.
• This reply was modified 4 years, 11 months ago by  RVO.
• This reply was modified 4 years, 11 months ago by  RVO.
• This reply was modified 4 years, 11 months ago by  RVO.

Frederico your code works!

Apparently somebody pointed sources to linked server to PRODUCTION (Damn.. I think that's why i wasn't getting a match for  five days!..) And that's why function results...

• This reply was modified 4 years, 11 months ago by  RVO.

Jeffrey, your code is using parameter [@startDate]. Didn't really get you. I'm not looking for another "function" solution..

Frederico,

I inserted the results from old code using function into [tmp_UsingFunction] table. I inserted results from your three versions into [tmp_FredericoX] tables and did EXCEPT to find differences. Here are...

Frederico,/Jeffrey,

Thank you both guys so much. I will try your versions today. I just wanted to modify my version to make it work because I didn't really followed Frederico's logic...

SCDECADE, Thank you very much for your code.

Old code returns 19554 records. Your code returns 1067 records. But it's an interesting approach. I'll see if maybe i can find how...

• This reply was modified 4 years, 11 months ago by  RVO.

I changed it to 1 DAY
but all tracking data seems to stay there and keeps accumulating . . .
I expected a cleanup....?

Again guys...

THERE IS NO INSERT

Application calls this SELECT and loads the entire output into Memory.

SQL Plan is attached.
Both in .SQLPLAN and .XML format