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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply