June 14, 2013 at 7:16 am
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.
VndNbrInvNbrInvAmtBatNbr
37461-127-5034.969855950
37461-127-5034.968957210
37461-127-5034.969258910
37461-1275034.969259236
37461-1275034.969259236
Table1 :
37461-127-5034.969855950
Table2:
37461-127-5034.968957210
37461-127-5034.969258910
37461-1275034.969259236
37461-1275034.969259236
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.
VndNbrInvNbrInvAmtBatNbr
425515240.774595988
4255152459.054595988
42551524715.454595988
4255152415034.844595988
Table1:
425515240.774595988
4255152459.054595988
42551524715.454595988
4255152415034.844595988
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:
VndNbrInvNbrInvAmtBatNbr
37461-127-5034.969855950
37461-127-5034.968957210
37461-127-5034.969258910
37461-1275034.969259236
37461-1275034.969259236
425515240.774595988
4255152459.054595988
42551524715.454595988
4255152415034.844595988
603031106541-781.416879249
603031106541-781.416879249
603031106541781.416873385
603031108887-421.928236774
603031108887421.928236737
603031BS 04120710000003731291
603031BS 04120790000003724588
68599847730-45022.058466639
68599847730492.067741083
685998477304686.547741083
6859984773039843.457741083
6859984773039843.638466640
687840423099-2310116179
687840423099-2310200163
6878404230992310200177
------------------------------------------------------------------------------------
Total Desired Results:
Table1 Name: StdPmt_InvAmtSum
Records:
37461-127-5034.969855950
425515240.774595988
4255152459.054595988
42551524715.454595988
4255152415034.844595988
603031106541-781.416879249
603031BS 04120710000003731291
603031BS 04120790000003724588
6859984773039843.638466640
687840423099-2310116179
Table2 Name: StdPmt_InvAmtSum_Removed
37461-127-5034.968957210
37461-127-5034.969258910
37461-1275034.969259236
37461-1275034.969259236
603031106541-781.416879249
603031106541781.416873385
603031108887-421.928236774
603031108887421.928236737
68599847730-45022.058466639
68599847730492.067741083
685998477304686.547741083
6859984773039843.457741083
687840423099-2310200163
6878404230992310200177
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
666555107775-3000007996100
6665551077753000007996100
6665551077753000007904930
The above is not going into the right tables.
This is the result I am getting:
Table1:
6665551077753000007996100
6665551077753000007904930
Table2:
666555107775-3000007996100
Should be:
Table1:
6665551077753000007996100
Table2:
666555107775-3000007996100
6665551077753000007904930
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
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
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
WhereS2.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
WhereS2.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
WhereS2.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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply