January 4, 2020 at 7:48 pm
I'd like to re-write SELECT below without using function. It returns 19544 records.
SELECT dbo.Func_getinvoicenumberbydocumentnumber(a.documentnumber) AS creditinvoicenumber,
dbo.Func_getinvoicenumberbydocumentnumber(a.originalinvoicenumber) AS originalinvoicenumber,
a.originalinvoicenumber AS referencedocumentnumber
FROM dwdb.dbo.pc_b_customerorder_header a(nolock)
WHERE Year(a.entrydate) >= Year(Getdate()) - 3
AND Substring(a.documentnumber, 3, 1) = 'R'
AND Len(a.originalinvoicenumber) > 9
AND EXISTS (
SELECT documentnumber
FROM dwdb.dbo.pc_b_customerorder_header b(nolock)
WHERE a.originalinvoicenumber = b.documentnumber
)
CREATE FUNCTION [dbo].[func_GetInvoiceNumberByDocumentNumber]
(
@DocumentNumber VARCHAR(11)
)
RETURNS VARCHAR(11)
AS
BEGIN
DECLARE @DocumentDate DATETIME
DECLARE @InvoiceNumber VARCHAR(11)
DECLARE @InvList TABLE
(
InvoiceNumber VARCHAR(11),
InvoiceDate DATETIME
)
DECLARE @InvListFinal TABLE
(
ID INT NOT NULL IDENTITY(1, 1),
InvoiceNumber VARCHAR(11),
InvoiceDate DATETIME
)
SELECT @DocumentDate = EntryDate
FROM dbo.PC_B_CustomerOrder_Header
WHERE DocumentNumber = @DocumentNumber
AND EntryDate >= DATEADD(YEAR, -3, GETDATE())
INSERT INTO @InvList
(InvoiceNumber, InvoiceDate)
SELECT InvoiceNumber, InvoiceDate
FROM dbo.MS_F_InvoiceHeader_History
WHERE DocumentNumber = @DocumentNumber
AND InvoiceDate >= @DocumentDate
AND HistoryInd <> 'H'
INSERT INTO @InvList
(InvoiceNumber, InvoiceDate)
SELECT InvoiceNumber, InvoiceDate
FROM dbo.WO_F_InvoiceHeader_Current
WHERE DocumentNumber = @DocumentNumber
AND InvoiceDate >= @DocumentDate
AND HistoryInd <> 'H'
INSERT INTO @InvListFinal
(InvoiceNumber, InvoiceDate)
SELECT InvoiceNumber, InvoiceDate
FROM @InvList
ORDER BY InvoiceDate DESC, InvoiceNumber DESC
SELECT @InvoiceNumber = InvoiceNumber
FROM @InvListFinal
WHERE ID = 1
RETURN @InvoiceNumber
END
GO
So far I tried two solutions below but it doesn't give me same record count count. I expect 19544. OPTION 1 seems to have a higher matching ratio.
--OPTION 1
--Using temp table and ROW_NUMBER()
--971 record matches out of 975. Matching ratio seemes to be much higher
--though total naumber of records is far from original (19544).
drop table
IF exists #cte3
go
CREATE TABLE #cte3
(
DocumentNumber varchar(10),
CreditInvoiceNumber varchar(11),
OriginalInvoiceNumber varchar(11),
ReferenceDocumentNumber varchar(10),
InvoiceDate datetime
)
go
INSERT into #cte3
SELECT pcb.DocumentNumber,
un1.InvoiceNumber,
un2.InvoiceNumber,
pcb.OriginalInvoiceNumber,
un1.InvoiceDate
FROM DWDB.dbo.PC_B_CustomerOrder_Header pcb
inner JOIN (
SELECT DocumentNumber,
InvoiceNumber,
InvoiceDate
FROM dbo.MS_F_InvoiceHeader_History
WHERE HistoryInd <> 'H'
union all
SELECT DocumentNumber,
InvoiceNumber,
InvoiceDate
FROM dbo.WO_F_InvoiceHeader_Current
WHERE HistoryInd <> 'H'
)
un1
ON un1.DocumentNumber = pcb.DocumentNumber
AND un1.InvoiceDate >= pcb.EntryDate
inner JOIN (
SELECT DocumentNumber,
InvoiceNumber,
InvoiceDate
FROM dbo.MS_F_InvoiceHeader_History
WHERE HistoryInd <> 'H'
union all
SELECT DocumentNumber,
InvoiceNumber,
InvoiceDate
FROM dbo.WO_F_InvoiceHeader_Current
WHERE HistoryInd <> 'H'
)
un2
ON un2.DocumentNumber = pcb.OriginalInvoiceNumber
AND un2.InvoiceDate >= pcb.EntryDate
WHERE YEAR(pcb.EntryDate) >= YEAR(GETDATE()) - 3
AND SUBSTRING(pcb.DocumentNumber, 3, 1) = 'R'
AND LEN(pcb.OriginalInvoiceNumber) > 9
--FINAL OUTPUT
SELECT
--A.DocumentNumber
A.CreditInvoiceNumber
,A.OriginalInvoiceNumber
,A.ReferenceDocumentNumber
FROM
(
SELECT
DocumentNumber,
CreditInvoiceNumber,
OriginalInvoiceNumber,
ReferenceDocumentNumber,
InvoiceDate
FROM (
SELECT ROW_NUMBER()
OVER(PARTITION BY DocumentNumber ORDER BY InvoiceDate DESC) AS StRank,
*
FROM #cte3
)
n
WHERE StRank = 1
)
A
--OPTION 2
--Using OUTER APPLY
drop table if exists tmp_CreditInvXrf_Augusto
---Returns 20053 records
--1246 records match with original (SELECT with function - 19544 records)
go
WITH
WO AS
(
SELECT InvoiceNumber,InvoiceDate,DocumentNumber
FROM SQLPROD1_RO.DWDB.dbo.MS_F_InvoiceHeader_History
WHERE HistoryInd <> 'H'
UNION ALL
SELECT InvoiceNumber,
InvoiceDate,
DocumentNumber
FROM SQLPROD1_RO.DWDB.dbo.WO_F_InvoiceHeader_Current
WHERE HistoryInd <> 'H'
)
,
PCB AS
(
SELECT DocumentNumber,OriginalInvoiceNumber,EntryDate
FROM DWDB.dbo.PC_B_CustomerOrder_Header
WHERE
YEAR(EntryDate) >= YEAR(GETDATE()) - 3
AND SUBSTRING(DocumentNumber, 3, 1) = 'R'
AND LEN(OriginalInvoiceNumber) > 9
AND EXISTS
(
SELECT DocumentNumber
FROM DWDB.dbo.PC_B_CustomerOrder_Header B(NOLOCK)
WHERE OriginalInvoiceNumber = B.DocumentNumber
)
)
--FINAL OUTPUT
SELECT
C.InvoiceNumber CreditInvoiceNumber,
D.InvoiceNumber OriginalInvoiceNumber,
PCB.OriginalInvoiceNumber ReferenceDocumentNumber
INTO
tmp_CreditInvXrf_Augusto --drop table tmp_CreditInvXrf_Augusto
FROM PCB
OUTER APPLY
(
SELECT TOP 1 InvoiceNumber
FROM WO
WHERE DocumentNumber = PCB.DocumentNumber
AND InvoiceDate >= PCB.EntryDate
ORDER BY InvoiceDate DESC
) C
OUTER APPLY
(
SELECT TOP 1 InvoiceNumber
FROM WO
WHERE DocumentNumber = PCB.OriginalInvoiceNumber
AND InvoiceDate >= PCB.EntryDate
ORDER BY InvoiceDate DESC
) D
January 4, 2020 at 11:23 pm
and what have you tried so far? this is a pretty simple function that any SQL Developer should be able to replace with a CTE and outer applies
January 5, 2020 at 11:59 pm
Here's my attempt. Because you're expecting a lot of rows from this query I wouldn't bother with outer applying top(1)'s. Rather it might make sense to break the cte into separate indexed temp tables. Also, if expected rows are missing use LEFT JOIN as necessary 🙂
with
entry_dt_cte(documentnumber, originalinvoicenumber, EntryDate) as (
select
documentnumber,
originalinvoicenumber
EntryDate
from
dwdb.dbo.pc_b_customerorder_header a (nolock)
where
year(a.entrydate) >= year(getdate()) - 3
and substring(a.documentnumber, 3, 1) = 'R'
and len(a.originalinvoicenumber) > 9
and exists (select documentnumber
from dwdb.dbo.pc_b_customerorder_header b (nolock)
where a.originalinvoicenumber = b.documentnumber )),
doc_nbr_cte(documentnumber, InvoiceNumber, InvoiceDate) as (
select
edc.documentnumber, msf.InvoiceNumber, msf.InvoiceDate
from
entry_dt_cte edc
join
dbo.MS_F_InvoiceHeader_History msf on edc.documentnumber=msf.documentnumber
and edc.EntryDate<=msf.InvoiceDate
and msf.HistoryInd <> 'H'
union all
select
edc.documentnumber, wof.InvoiceNumber, wof.InvoiceDate
from
entry_dt_cte edc
join
dbo.WO_F_InvoiceHeader_Current wof on edc.documentnumber=wof.documentnumber
and edc.EntryDate<=wof.InvoiceDate
and wof.HistoryInd <> 'H'),
orig_nbr_cte(originalinvoicenumber, InvoiceNumber, InvoiceDate) as (
select
edc.originalinvoicenumber, msf.InvoiceNumber, msf.InvoiceDate
from
entry_dt_cte edc
join
dbo.MS_F_InvoiceHeader_History msf on edc.originalinvoicenumber=msf.documentnumber
and edc.EntryDate<=msf.InvoiceDate
and msf.HistoryInd <> 'H'
union all
select
edc.originalinvoicenumber, wof.InvoiceNumber, wof.InvoiceDate
from
entry_dt_cte edc
join
dbo.WO_F_InvoiceHeader_Current wof on edc.originalinvoicenumber=wof.documentnumber
and edc.EntryDate<=wof.InvoiceDate
and wof.HistoryInd <> 'H'),
top_doc_nbr_cte(documentnumber, InvoiceNumber, row_num) as (
select
documentnumber,
InvoiceNumber,
row_number() over(partition by documentnumber order by InvoiceDate desc, InvoiceNumber desc) row_num
from
doc_nbr_cte),
top_orig_nbr_cte(originalinvoicenumber, InvoiceNumber, row_num) as (
select
originalinvoicenumber,
InvoiceNumber,
row_number() over(partition by originalinvoicenumber order by InvoiceDate desc, InvoiceNumber desc) row_num
from
orig_nbr_cte)
select
edc.*,
dnc.InvoiceNumber AS creditinvoicenumber,
onc.InvoiceNumber AS originalinvoicenumber
from
entry_dt_cte edc
join
top_doc_nbr_cte dnc on edc.documentnumber=dnc.documentnumber
join
top_orig_nbr_cte onc on edc.originalinvoicenumber=onc.originalinvoicenumber
where
dnc.row_num=1
and onc.row_num=1;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 6, 2020 at 3:40 pm
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 to change your code to achieve good results.
January 6, 2020 at 3:57 pm
Consider the following article while you're at it. 😉
https://www.sqlservercentral.com/editorials/the-art-of-commenting
--Jeff Moden
Change is inevitable... Change for the better is not.
January 6, 2020 at 8:01 pm
thanks for posting your attempt - didn't try to see if they had an error so apologies if what I'm posting looks similar
3 options - untested obviously as we don't have neither DDL to create the tables neither sample data that we could test with.
This is something you should consider supplying going forward as a way to enable people to help you.
-- option 1 - with cte and outer apply top 1
with base_function
as
(
-- following 2 selects emulate what the function was doing
-- get document from PC_B_CustomerOrder_Header
-- get list of associated documents from current and history tables
-- final block of the function - retrieve the most recent record - will be deal with on the main query
select coh.documentnumber
, msf.InvoiceNumber
, msf.InvoiceDate
from dbo.PC_B_CustomerOrder_Header coh
inner join dbo.MS_F_InvoiceHeader_History msf
on msf.documentnumber = coh.documentnumber
and msf.InvoiceDate >= coh.EntryDate
and msf.HistoryInd <> 'H'
and coh.EntryDate >= dateadd(year, -3, getdate())
union all
select coh.documentnumber
, wof.InvoiceNumber
, wof.InvoiceDate
from dbo.PC_B_CustomerOrder_Header coh
inner join dbo.WO_F_InvoiceHeader_Current wof
on wof.documentnumber = coh.documentnumber
and wof.InvoiceDate >= coh.entrydate
and wof.HistoryInd <> 'H'
and coh.EntryDate >= dateadd(year, -3, getdate())
)
--select dbo.Func_getinvoicenumberbydocumentnumber(a.documentnumber) as creditinvoicenumber
-- , dbo.Func_getinvoicenumberbydocumentnumber(a.originalinvoicenumber) as originalinvoicenumber
select cred.creditinvoicenumber as creditinvoicenumber
, orig.InvoiceNumber as originalinvoicenumber
, a.originalinvoicenumber as referencedocumentnumber
from dbo.PC_B_CustomerOrder_Header a
-- original function was returning most recent invoice date and most recent invoice number within that date
-- for supplied document number
-- emulate the same functionality
outer apply (select top 1 bf.documentnumber
, bf.InvoiceDate
, bf.InvoiceNumber
from base_function bf
where bf.documentnumber = a.documentnumber
order by bf.InvoiceDate desc
, bf.InvoiceNumber desc
) cred
outer apply (select top 1 bf.documentnumber
, bf.InvoiceDate
, bf.InvoiceNumber
from base_function bf
where bf.documentnumber = a.originalinvoicenumber
order by bf.InvoiceDate desc
, bf.InvoiceNumber desc
) orig
where year(a.entrydate) >= year(getdate()) - 3
and substring(a.documentnumber, 3, 1) = 'R'
and len(a.originalinvoicenumber) > 9
and exists
(select documentnumber
from dbo.PC_B_CustomerOrder_Header b
where a.originalinvoicenumber = b.documentnumber
)
-- option 2 - with cte and left outer join to selects with rownumber
with base_function
as
(
-- following 2 selects emulate what the function was doing
-- get document from PC_B_CustomerOrder_Header
-- get list of associated documents from current and history tables
-- final block of the function - retrieve the most recent record - will be deal with on the main query
select coh.documentnumber
, msf.InvoiceNumber
, msf.InvoiceDate
from dbo.PC_B_CustomerOrder_Header coh
inner join dbo.MS_F_InvoiceHeader_History msf
on msf.documentnumber = coh.documentnumber
and msf.InvoiceDate >= coh.EntryDate
and msf.HistoryInd <> 'H'
and coh.EntryDate >= dateadd(year, -3, getdate())
union all
select coh.documentnumber
, wof.InvoiceNumber
, wof.InvoiceDate
from dbo.PC_B_CustomerOrder_Header coh
inner join dbo.WO_F_InvoiceHeader_Current wof
on wof.documentnumber = coh.documentnumber
and wof.InvoiceDate >= coh.entrydate
and wof.HistoryInd <> 'H'
and coh.EntryDate >= dateadd(year, -3, getdate())
)
, alldocs
as
(select bf.documentnumber
, bf.InvoiceDate
, bf.InvoiceNumber
, row_number() over (partition by bf.documentnumber
order by bf.InvoiceDate desc
, bf.InvoiceNumber desc
) rownum
from base_function bf
)
--select dbo.Func_getinvoicenumberbydocumentnumber(a.documentnumber) as creditinvoicenumber
-- , dbo.Func_getinvoicenumberbydocumentnumber(a.originalinvoicenumber) as originalinvoicenumber
select ad_cred.creditinvoicenumber as creditinvoicenumber
, ad_orig.InvoiceNumber as originalinvoicenumber
, a.originalinvoicenumber as referencedocumentnumber
from dbo.PC_B_CustomerOrder_Header a
left outer join alldocs ad_cred
on ad_cred.documentnumber = a.documentnumber
and ad_cred.rownum = 1
left outer join alldocs ad_orig
on ad_orig.documentnumber = a.originalinvoicenumber
and ad_orig.rownum = 1
where year(a.entrydate) >= year(getdate()) - 3
and substring(a.documentnumber, 3, 1) = 'R'
and len(a.originalinvoicenumber) > 9
and exists
(select documentnumber
from dbo.PC_B_CustomerOrder_Header b
where a.originalinvoicenumber = b.documentnumber
)
-- option 3 - with temp table and left outer join to selects with rownumber
if object_id('tempdb..#tempdocs') is not null
drop table #tempdocs;
with base_function
as
(
-- following 2 selects emulate what the function was doing
-- get document from PC_B_CustomerOrder_Header
-- get list of associated documents from current and history tables
-- final block of the function - retrieve the most recent record - will be deal with on the main query
select coh.documentnumber
, msf.InvoiceNumber
, msf.InvoiceDate
from dbo.PC_B_CustomerOrder_Header coh
inner join dbo.MS_F_InvoiceHeader_History msf
on msf.documentnumber = coh.documentnumber
and msf.InvoiceDate >= coh.EntryDate
and msf.HistoryInd <> 'H'
and coh.EntryDate >= dateadd(year, -3, getdate())
union all
select coh.documentnumber
, wof.InvoiceNumber
, wof.InvoiceDate
from dbo.PC_B_CustomerOrder_Header coh
inner join dbo.WO_F_InvoiceHeader_Current wof
on wof.documentnumber = coh.documentnumber
and wof.InvoiceDate >= coh.entrydate
and wof.HistoryInd <> 'H'
and coh.EntryDate >= dateadd(year, -3, getdate())
)
, alldocs
as
(select bf.documentnumber
, bf.InvoiceDate
, bf.InvoiceNumber
, row_number() over (partition by bf.documentnumber
order by bf.InvoiceDate desc
, bf.InvoiceNumber desc
) rownum
from base_function bf
)
select *
into #tempdocs
from alldocs ad
where ad.rownum = 1
create clustered index #tempdocs_ix1 on #tempdocs
(documentnumber
)
--select dbo.Func_getinvoicenumberbydocumentnumber(a.documentnumber) as creditinvoicenumber
-- , dbo.Func_getinvoicenumberbydocumentnumber(a.originalinvoicenumber) as originalinvoicenumber
select ad_cred.creditinvoicenumber as creditinvoicenumber
, ad_orig.InvoiceNumber as originalinvoicenumber
, a.originalinvoicenumber as referencedocumentnumber
from dbo.PC_B_CustomerOrder_Header a
left outer join alldocs ad_cred
on ad_cred.documentnumber = a.documentnumber
left outer join alldocs ad_orig
on ad_orig.documentnumber = a.originalinvoicenumber
where year(a.entrydate) >= year(getdate()) - 3
and substring(a.documentnumber, 3, 1) = 'R'
and len(a.originalinvoicenumber) > 9
and exists
(select documentnumber
from dbo.PC_B_CustomerOrder_Header b
where a.originalinvoicenumber = b.documentnumber
)
January 6, 2020 at 9:31 pm
This is essentially the same as Frederico's first option:
Declare @startDate date = (Select max(co.EntryDate)
From dbo.PC_B_CustomerOrder_Header co
Where co.DocumentNumber = @DocumentNumber
And EntryDate >= dateadd(year, -3, getdate()));
With invoiceData
As (
Select h.DocumentNumber
, h.InvoiceNumber
, h.InvoiceDate
From dbo.MS_F_InvoiceHeader_History h
Where h.HistoryInd <> 'H'
And h.InvoiceDate = @startDate
Union All
Select c.DocumentNumber
, c.InvoiceNumber
, c.InvoiceDate
From dbo.WO_F_InvoiceHeader_History c
Where c.HistoryInd <> 'H'
And c.InvoiceDate = @startDate
)
Select CreditInvoiceNumber = ci.InvoiceNumber
, OriginalInvoiceNumber = oi.InvoiceNumber
, ReferenceDocumentNumber = a.OriginalInvoiceNumber
From dwdb.dbo.pc_b_customerorder_header a(nolock)
Outer Apply (Select Top 1
d.InvoiceNumber
, d.InvoiceDate
From invoiceData d
Where d.DocumentNumber = a.DocumentNumber
Order By
InvoiceDate desc
, InvoiceNumber desc) ci
Outer Apply (Select Top 1
d.InvoiceNumber
, d.InvoiceDate
From invoiceData d
Where d.DocumentNumber = a.OriginalInvoiceNumber
Order By
InvoiceDate desc
, InvoiceNumber desc) oi
Where len(a.OriginalInvoiceNumber) > 9
And substring(a.DocumentNumber, 3, 1) = 'R'
And a.EntryDate >= dateadd(year, datediff(year, 0, getdate()) - 3, 0)
And Exists (Select *
From dwdb.dbo.pc_b_customerorder_header b (nolock)
Where b.DocumentNumber = a.OriginalInvoiceNumber);
I modified the lookup for the EntryDate to include MAX - as it isn't guaranteed to return the correct value the way it is currently written. I also changed the check from looking at the YEAR to looking for an EntryDate that is greater than or equal to the 1st of the year 3 years ago - which will allow an index to be utilized if one exists.
I also am assuming that both EntryDate and InvoiceDate are using DATE data type - if either is actually using DATETIME then you would want to change @startDate to a datetime data type.
With that said - you could change your function to an inline-table valued function instead of a scalar function, then outer/cross apply to that function and achieve the same results. That might be a better option if the function is used in other queries...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 7, 2020 at 3:45 pm
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 from the first view. Sorry Frederico, it's my fault. I am a stubborn guy. I don't like to "steal" somebody's code unless I fully understand what they are doing. But I guess I don't have a choice if my version still doesn't work today.
January 9, 2020 at 6:20 pm
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 the results:
select * from tmp_UsingFunction
except
--select * from tmp_Frederico1 --mismatch 4253
--select * from tmp_Frederico2 --mismatch 4253
select * from tmp_Frederico3 --mismatch 4253
January 9, 2020 at 6:27 pm
Jeffrey, your code is using parameter [@startDate]. Didn't really get you. I'm not looking for another "function" solution..
January 9, 2020 at 8:13 pm
Jeffrey, your code is using parameter [@startDate]. Didn't really get you. I'm not looking for another "function" solution..
I did not use a parameter - I defined a variable instead of repeating that code multiple times. I assume that you are putting this code in a stored procedure which can use variables and even parameters with no issues.
If the goal of getting rid of the function is to improve performance - you do have the option of converting your multi-statement scalar function to an inline-table valued function which might solve the performance problem. It would also allow you to reuse that function for other queries.
However - the solution I provided is not a function and just uses a variable to define the start date. In your original code you have this:
SELECT @DocumentDate = EntryDate
FROM dbo.PC_B_CustomerOrder_Header
WHERE DocumentNumber = @DocumentNumber
AND EntryDate >= DATEADD(YEAR, -3, GETDATE())
And then you use that later here:
INSERT INTO @InvList
(InvoiceNumber, InvoiceDate)
SELECT InvoiceNumber, InvoiceDate
FROM dbo.MS_F_InvoiceHeader_History
WHERE DocumentNumber = @DocumentNumber
AND InvoiceDate >= @DocumentDate
AND HistoryInd <> 'H'
In my code - I did the same thing...but I made a mistake and use '=' instead of '>='...it should be this:
With invoiceData
As (
Select h.DocumentNumber
, h.InvoiceNumber
, h.InvoiceDate
From dbo.MS_F_InvoiceHeader_History h
Where h.HistoryInd <> 'H'
And h.InvoiceDate >= @startDate
Union All
Select c.DocumentNumber
, c.InvoiceNumber
, c.InvoiceDate
From dbo.WO_F_InvoiceHeader_History c
Where c.HistoryInd <> 'H'
And c.InvoiceDate >= @startDate
The only difference between my solution and Frederico's option 1 is that I moved the check for the document start date to the variable. This eliminates the need to join to that table to get the document dates.
Doing this:
SELECT @DocumentDate = EntryDate
FROM dbo.PC_B_CustomerOrder_Header
WHERE DocumentNumber = @DocumentNumber
AND EntryDate >= DATEADD(YEAR, -3, GETDATE())
is not guaranteed to return the 'latest' EntryDate - it has 2 problems...the first is that DATEADD(YEAR, -3, GETDATE()) will return a different date every day this is run...so running this tomorrow may return a different document date. The second issue is that setting a variable this way will return the last row SQL Server decides to return - which could be the earliest, the latest - or somewhere in the middle...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 10, 2020 at 3:06 am
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 were different when i was doing comparison.
THANK YOU VERY MUCH Frederico!
January 10, 2020 at 3:10 am
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 in the SELECT.
Anyway, I removed @StartDate part from your code and ran it. 1406 records don't match. I like the simplicity of your code but I have no choice. i have to go with Frederico version. His code gives me 100% matching results. Thank you very much anyway. I really appreciate your help.
January 10, 2020 at 7:51 am
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 were different when i was doing comparison.
THANK YOU VERY MUCH Frederico!
Good to know.
Would it be possible to give us the explain plans (for the 3 methods I gave) and time results for each as well as the time that it takes using your functions. Just curious.
January 10, 2020 at 3:53 pm
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 highlights are the following:
FredericoPlan1 - 6 seconds
FredericoPlan2 - 46 seconds
Third option returned two plan grids so I saved them as separate CSV's:
FredericoPlam3-1
FredericoPlan3-2
----------------------------
32 seconds
As you can see, first option is the fastest. I'm gonna use it.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply