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 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy