

SSCEnthusiastic
Group: General Forum Members
Last Login: Monday, November 7, 2016 11:31 AM
Points: 189,
Visits: 534


I have a table with millions of records and I want to extract the data into 2 different tables.
Criteria1: When Sum of InvAmt by VndNbr + InvNbr Is Greater or Less than Zero. There will be 1 record that will equal the sum of InvAmt by VndNbr + Invnbr. That record will go into Table1(Sum). The rest of the records will go to Table2(Sum_Removed). e.i. VndNbr InvNbr InvAmt BatNbr 3746 1127 5034.96 9855950 3746 1127 5034.96 8957210 3746 1127 5034.96 9258910 3746 1127 5034.96 9259236 3746 1127 5034.96 9259236
Table1 : 3746 1127 5034.96 9855950
Table2: 3746 1127 5034.96 8957210 3746 1127 5034.96 9258910 3746 1127 5034.96 9259236 3746 1127 5034.96 9259236
Criteria2: When Sum of InvAmt by VndNbr + InvNbr Does NOT Equal Zero and The sum of InvAmt does not equal 1 of those recrods. All those records will go into Table1 (Sum) i.e.
VndNbr InvNbr InvAmt BatNbr 4255 1524 0.77 4595988 4255 1524 59.05 4595988 4255 1524 715.45 4595988 4255 1524 15034.84 4595988
Table1: 4255 1524 0.77 4595988 4255 1524 59.05 4595988 4255 1524 715.45 4595988 4255 1524 15034.84 4595988
Criteria3: When Sum of InvAmt by VndNbr + InvNbr Equal Zero, then all the records will go into Table2
In the end all the records in Table2 will have the sum of Invamt by vndnbr + invnbr equal to zero.  SAMPLE Data: VndNbr InvNbr InvAmt BatNbr 3746 1127 5034.96 9855950 3746 1127 5034.96 8957210 3746 1127 5034.96 9258910 3746 1127 5034.96 9259236 3746 1127 5034.96 9259236
4255 1524 0.77 4595988 4255 1524 59.05 4595988 4255 1524 715.45 4595988 4255 1524 15034.84 4595988
603031 106541 781.41 6879249 603031 106541 781.41 6879249 603031 106541 781.41 6873385
603031 108887 421.92 8236774 603031 108887 421.92 8236737
603031 BS 041207 1000000 3731291 603031 BS 041207 9000000 3724588
685998 47730 45022.05 8466639 685998 47730 492.06 7741083 685998 47730 4686.54 7741083 685998 47730 39843.45 7741083 685998 47730 39843.63 8466640
687840 423099 23 10116179 687840 423099 23 10200163 687840 423099 23 10200177  Total Desired Results: Table1 Name: StdPmt_InvAmtSum Records: 3746 1127 5034.96 9855950 4255 1524 0.77 4595988 4255 1524 59.05 4595988 4255 1524 715.45 4595988 4255 1524 15034.84 4595988 603031 106541 781.41 6879249 603031 BS 041207 1000000 3731291 603031 BS 041207 9000000 3724588 685998 47730 39843.63 8466640 687840 423099 23 10116179
Table2 Name: StdPmt_InvAmtSum_Removed 3746 1127 5034.96 8957210 3746 1127 5034.96 9258910 3746 1127 5034.96 9259236 3746 1127 5034.96 9259236 603031 106541 781.41 6879249 603031 106541 781.41 6873385 603031 108887 421.92 8236774 603031 108887 421.92 8236737 685998 47730 45022.05 8466639 685998 47730 492.06 7741083 685998 47730 4686.54 7741083 685998 47730 39843.45 7741083 687840 423099 23 10200163 687840 423099 23 10200177
This is the code I have been using. I am getting most of my desired results. There is 1 example which is not working for me
666555 107775 300000 7996100 666555 107775 300000 7996100 666555 107775 300000 7904930
The above is not going into the right tables.
This is the result I am getting: Table1: 666555 107775 300000 7996100 666555 107775 300000 7904930
Table2: 666555 107775 300000 7996100
Should be: Table1: 666555 107775 300000 7996100
Table2: 666555 107775 300000 7996100 666555 107775 300000 7904930
Code:
 Get records by Invoices with only 2 records that don't equal 0. Select S1.*, S2.Cnt, Cast(Null as Integer) as rn Into AS_Workspace.dbo.StdPmt_InvAmt_SUM 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 WHERE S2.InvAmt_Sum > 0 AND S2.Cnt > 2 AND S2.VndNbr = '666555' AND S2.InvNbr = '107775'
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 AND S2.VndNbr = '666555' AND S2.InvNbr = '107775'
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 S2.VndNbr, S2.InvNbr ORDER BY BatNbr 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 S2.InvAmt_Sum < 0
) NegQry1 Where rn = 1
/*################# REMOVED TABLE #######################*/  Insert records to get Invoices with 2 or more records that don't equal 0 and have a negative amount of the sum of invamt. Pull in the Positive record. Insert Into AS_Workspace.dbo.StdPmt_InvAmt_SUM2_Removed Select S1.*, S2.Cnt, Cast(Null as Integer) as rn Into AS_Workspace.dbo.StdPmt_InvAmt_SUM_Removed 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 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
 Get the records that don't equal 0 and Sum of records is > 0.  Put these into the SUM2_Removed Table 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 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
 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 into the Removed table. Insert Into AS_Workspace.dbo.StdPmt_InvAmt_SUM2_Removed(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 S2.VndNbr, S2.InvNbr ORDER BY BatNbr 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 AND S2.InvAmt_Sum < 0 Where S2.Cnt > 2 AND 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 )
) NegQry2 WHERE rn >= 2 UNION
/*################## NETZero ######################*/  Get records by Invoices that don't equal 0. 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 Where S2.InvAmt_Sum = 0



