SQLServerCentral » Programming » General » Inner Join with SubQueryInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralFri, 28 Jul 2017 07:33:23 GMT20Inner Join with SubQueryhttps://www.sqlservercentral.com/Forums/FindPost1462214.aspxI 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
[b]and S2.VndNbr = '687840' and S2.InvNbr = '423099' [/b]
) NegQry
WHERE rn = 1
Results:
vndnbr | InvNbr | InvAmt
687840 | 423099 | -23.000000
Why does it do that?Wed, 12 Jun 2013 06:15:22 GMTGrassHopperRE: Inner Join with SubQueryhttps://www.sqlservercentral.com/Forums/FindPost1462581.aspxThanks!
that fixed my problem.Wed, 12 Jun 2013 06:15:22 GMTGrassHopperRE: Inner Join with SubQueryhttps://www.sqlservercentral.com/Forums/FindPost1462452.aspxIf 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 DESCTue, 11 Jun 2013 23:14:34 GMTLutzMRE: Inner Join with SubQueryhttps://www.sqlservercentral.com/Forums/FindPost1462424.aspxOk, 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.Tue, 11 Jun 2013 17:06:07 GMTGrassHopperRE: Inner Join with SubQueryhttps://www.sqlservercentral.com/Forums/FindPost1462246.aspxI tried your code (with replaced table name) and it still return the same data as before (2 rows).
Tue, 11 Jun 2013 09:51:50 GMTLutzMRE: Inner Join with SubQueryhttps://www.sqlservercentral.com/Forums/FindPost1462242.aspxHere'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
Where S2.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 = 1Tue, 11 Jun 2013 09:42:35 GMTGrassHopperRE: Inner Join with SubQueryhttps://www.sqlservercentral.com/Forums/FindPost1462229.aspxhere's a test scenario in a ready to use format, returning the expected(?) result.
So, I don't see anything wrong with the code.
[code="sql"]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[/code]Tue, 11 Jun 2013 09:27:55 GMTLutzM