Complex Query

• GrassHopper

  SSCarpal Tunnel

  Points: 4465

  More actions

  June 14, 2013 at 7:16 am

  #144418

  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