Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Complex Query Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, June 14, 2013 7:16 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, December 03, 2013 11:33 AM Points: 111, Visits: 287
 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 BatNbr3746 1-127 -5034.96 98559503746 1-127 -5034.96 89572103746 1-127 -5034.96 92589103746 1-127 5034.96 92592363746 1-127 5034.96 9259236Table1 :3746 1-127 -5034.96 9855950Table2:3746 1-127 -5034.96 89572103746 1-127 -5034.96 92589103746 1-127 5034.96 92592363746 1-127 5034.96 9259236Criteria2: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 BatNbr4255 1524 0.77 45959884255 1524 59.05 45959884255 1524 715.45 45959884255 1524 15034.84 4595988Table1:4255 1524 0.77 45959884255 1524 59.05 45959884255 1524 715.45 45959884255 1524 15034.84 4595988Criteria3:When Sum of InvAmt by VndNbr + InvNbr Equal Zero, then all the records will go into Table2In the end all the records in Table2 will have the sum of Invamt by vndnbr + invnbr equal to zero.----------------------------------------------------------------------SAMPLE Data:VndNbr InvNbr InvAmt BatNbr3746 1-127 -5034.96 98559503746 1-127 -5034.96 89572103746 1-127 -5034.96 92589103746 1-127 5034.96 92592363746 1-127 5034.96 92592364255 1524 0.77 45959884255 1524 59.05 45959884255 1524 715.45 45959884255 1524 15034.84 4595988603031 106541 -781.41 6879249603031 106541 -781.41 6879249603031 106541 781.41 6873385603031 108887 -421.92 8236774603031 108887 421.92 8236737603031 BS 041207 1000000 3731291603031 BS 041207 9000000 3724588685998 47730 -45022.05 8466639685998 47730 492.06 7741083685998 47730 4686.54 7741083685998 47730 39843.45 7741083685998 47730 39843.63 8466640687840 423099 -23 10116179687840 423099 -23 10200163687840 423099 23 10200177------------------------------------------------------------------------------------Total Desired Results:Table1 Name: StdPmt_InvAmtSumRecords:3746 1-127 -5034.96 98559504255 1524 0.77 45959884255 1524 59.05 45959884255 1524 715.45 45959884255 1524 15034.84 4595988603031 106541 -781.41 6879249603031 BS 041207 1000000 3731291603031 BS 041207 9000000 3724588685998 47730 39843.63 8466640687840 423099 -23 10116179Table2 Name: StdPmt_InvAmtSum_Removed3746 1-127 -5034.96 89572103746 1-127 -5034.96 92589103746 1-127 5034.96 92592363746 1-127 5034.96 9259236603031 106541 -781.41 6879249603031 106541 781.41 6873385603031 108887 -421.92 8236774603031 108887 421.92 8236737685998 47730 -45022.05 8466639685998 47730 492.06 7741083685998 47730 4686.54 7741083685998 47730 39843.45 7741083687840 423099 -23 10200163687840 423099 23 10200177This is the code I have been using. I am getting most of my desired results. There is 1 example which is not working for me666555 107775 -300000 7996100666555 107775 300000 7996100666555 107775 300000 7904930The above is not going into the right tables.This is the result I am getting:Table1:666555 107775 300000 7996100666555 107775 300000 7904930Table2:666555 107775 -300000 7996100Should be:Table1:666555 107775 300000 7996100Table2:666555 107775 -300000 7996100666555 107775 300000 7904930Code:-- 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 TableSelect 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
Post #1463551

 Permissions