June 11, 2013 at 9:11 am
I am running a Inner Join to the same table I am creating a Subquery on and I don't get the results I am expecting.
This is my query:
Select *
FROM (
Select S1.*, S2.Cnt , rn = ROW_NUMBER() OVER (PARTITION BY InvAmt_Sum ORDER BY ChkNbr DESC)
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt = S2.InvAmt_Sum
Where S2.Cnt > 2
AND InvAmt_Sum < 0
) NegQry
Where rn = 1
I get strange results. In my source table, I have these 6 records:
vndnbr | InvNbr | InvAmt
687840 | 423099 | -23.000000
687840 | 423099 | -23.000000
687840 | 423099 | 23.000000
603031 | 106541 | -781.410000
603031 | 106541 | -781.410000
603031 | 106541 | 781.410000
When I run the query,
I only get this record return in the table:
603031 | 106541 | -781.410000
But when i run the query to test it with this filter, it returns the 1 missing record:
Select *
-- Into AS_Workspace.dbo.StdPmt_InvAmt_SUM_TEST
FROM (
Select S1.*, S2.Cnt , rn = ROW_NUMBER() OVER (PARTITION BY InvAmt_Sum ORDER BY ChkNbr DESC)
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt = S2.InvAmt_Sum
Where S2.Cnt > 2
AND InvAmt_Sum < 0
and S2.VndNbr = '687840' and S2.InvNbr = '423099'
) NegQry
WHERE rn = 1
Results:
vndnbr | InvNbr | InvAmt
687840 | 423099 | -23.000000
Why does it do that?
June 11, 2013 at 9:27 am
here's a test scenario in a ready to use format, returning the expected(?) result.
So, I don't see anything wrong with the code.
declare @tbl table(vndnbr int, InvNbr int, InvAmt numeric(18,6),ChkNbr int)
insert into @tbl
SELECT 687840 , 423099 , -23.000000,1 union all
SELECT 687840 , 423099 , -23.000000,2 union all
SELECT 687840 , 423099 , 23.000000,3 union all
SELECT 603031 , 106541 , -781.410000,1 union all
SELECT 603031 , 106541 , -781.410000,1 union all
SELECT 603031 , 106541 , 781.410000,1
Select *
FROM (
Select S1.*, S2.Cnt , rn = ROW_NUMBER() OVER (PARTITION BY InvAmt_Sum ORDER BY ChkNbr DESC)
From @tbl as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From @tbl
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt = S2.InvAmt_Sum
Where S2.Cnt > 2
AND InvAmt_Sum < 0
) NegQry
Where rn = 1
June 11, 2013 at 9:42 am
Here's the full code. I don't think the rest of it would affect the results, but you tell me?
Select S1.*, S2.Cnt, Cast(Null as Integer) as rn
Into AS_Workspace.dbo.StdPmt_InvAmt_SUM7
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt <> S2.InvAmt_Sum
AND S2.InvAmt_Sum > 0
Where S2.Cnt = 2
UNION
Select S1.*, S2.Cnt, Cast(Null as Integer) as rn
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt <> S2.InvAmt_Sum
AND S2.InvAmt_Sum > 0
Where S2.Cnt > 2
AND NOT EXISTS(Select InvAmt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S3
WhereS2.InvNbr = S3.InvNbr
AND S2.VndNbr = S3.VndNbr
AND S2.InvAmt_Sum = S3.InvAmt )
UNION
Select S1.*, S2.Cnt, Cast(Null as Integer) as rn
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt = S2.InvAmt_Sum
AND S2.InvAmt_Sum > 0
Where S2.Cnt > 0
UNION
-- Insert records to get Invoices with more than 2 records that don't equal 0 and have a negative amount of the sum of invamt.
--Pull in 1 of the 2 Negative records.
--Insert Into AS_Workspace.dbo.StdPmt_InvAmt_SUM2(VndNbr, VndName, InvNbr, InvDt, InvAmt, NetAmt, FInvAmt, DiscAmt, FDiscAmt, TaxBaseAmt, GSTAmt, FGstAmt, InvCurCd, InvStat, PmtTrms, BatNbr, PONbr, CstCntr, Cmpy, VchNbr, TrTyp, Descr, TaxCd, AdjDoc, Void, chknbr, ChkAmt, ChkCurr, ChkDt, ChkClearDt, ChkTyp, VndBnkAcct, AmtPaid, Chk_payId, ChkBatNbr, void_gl_date, VatPct, DiscPct, Cnt, rn)
Select *
FROM (
Select S1.*, S2.Cnt , rn = ROW_NUMBER() OVER (PARTITION BY InvAmt_Sum ORDER BY ChkNbr DESC)
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt = S2.InvAmt_Sum
Where S2.Cnt > 2
AND InvAmt_Sum < 0
) NegQry
WHERE rn = 1
June 11, 2013 at 9:51 am
I tried your code (with replaced table name) and it still return the same data as before (2 rows).
June 11, 2013 at 5:06 pm
Ok, I think I found the issue, but don't know how to change this to fix it.
The problem seems to be with this code:
Select S1.*, S2.Cnt , rn = ROW_NUMBER() OVER (PARTITION BY InvAmt_Sum ORDER BY batNbr DESC)
specifically this:
rn = ROW_NUMBER() OVER (PARTITION BY InvAmt_Sum ORDER BY batNbr DESC)
I think this is creating an issue. The rn should count incrementally by VndNbr, InvNbr.
June 11, 2013 at 11:14 pm
If you want to restart with 1 for each new combination of VndNbr, InvNbr then you should change it to
PARTITION BY VndNbr, InvNbr ORDER BY batNbr DESC
June 12, 2013 at 6:15 am
Thanks!
that fixed my problem.
Viewing 7 posts - 1 through 7 (of 7 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